Excel Functions Overview

1. Average Function

• Usage: =AVERAGE(range)
  • Example: =AVERAGE(15, 27, 42, 93, 56, 84, 75, 33, 49)
  • Result: 53.125
• Quick Calculation:
  • For two numbers: =AVERAGE(100, 200) → Result: 150
  • For three numbers: =AVERAGE(100, 200, 300) → Result: 200

2. Sum Function

• Usage: =SUM(range)
  • Example: =SUM(range of numbers) → Result: 429
• Quick Addition:
  • For two numbers: =SUM(50, 125) or =50 + 125 → Result: 175
  • For three numbers: =SUM(100, 200, 300) → Result: 600

3. Sum If Function

• Usage: =SUMIF(range, criteria, sum_range)
  • Example:
    • To sum only Mazda vehicles:
    • =SUMIF(car_type_range, "Mazda", sum_range) → Result: 692
• Dynamic Calculation:
  • Change criteria to Honda or Toyota → Automatically adjusts the sum.

4. Count Function

• Usage: =COUNT(range)
  • Counts number of cells containing numbers.
  • Example: 10 cells with numbers.

5. Count A Function

• Usage: =COUNTA(range)
  • Counts non-empty cells (both numbers and text).
• Example: Count in a mixed list → Returns total of non-empty cells.

6. Count Blank Function

• Usage: =COUNTBLANK(range)
  • Counts empty cells in a specified range.

7. Count If Function

• Usage: =COUNTIF(range, criteria)
  • Example: Count how many times a specific car type appears.
• Dynamic Counting:
  • Adjust criteria (e.g., Mazda or Toyota) and get updated counts.

8. Concatenate Function

• Usage: =CONCATENATE(text1, text2)
  • Example: Combine first and last names with a space.
• Detailed Usage:
  • =CONCATENATE(B2, " ", C2) → Adds a space between the names.

9. IF Function

• Basic Usage: =IF(logical_test, value_if_true, value_if_false)
  • Example: Check if a cell contains "orange".
  • Dynamic calculations based on true/false criteria.
  • Example: If cell value > 30 → Calculate or return zero.

10. VLOOKUP Function

• Usage: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • Example for looking up name, email, phone number, revenue.
  • Adjust the column index number to get different data (e.g., 2 for email, 3 for phone).

11. Drop Down List

• Usage: Create a dropdown for easy selection from a list.
  • Go to Data → Data Validation → Allow list → Enter names or select range.
• Dynamic Updating: Selecting a name updates related information automatically.

Additional Resources

• For more detailed videos, check the description for the Excel video tutorial playlist.
• Other topics available: Algebra, Geometry, Trig, Precalculus, Chemistry, Physics.