MS Excel - AVERAGE function in Microsoft Excel
1. What Is the AVERAGE Function?
The AVERAGE function calculates the arithmetic mean of a set of numbers. It adds up all the numbers and divides by the count of numbers.
Use Case:
-
Calculate average sales, exam scores, temperatures, etc.
2. Syntax
=AVERAGE(number1, [number2], …)
Parameters
-
number1 → Required. First number, cell reference, or range.
-
number2… → Optional. Additional numbers, cell references, or ranges.
-
Excel allows up to 255 arguments.
3. Basic Examples
Example 1: Average of Numbers
=AVERAGE(10, 20, 30)
Result: 20
Explanation: (10 + 20 + 30) ÷ 3 = 20
Example 2: Average of a Range
=AVERAGE(A1:A5)
-
Calculates the average of all numbers in cells A1 to A5.
Example 3: Average with Multiple Ranges
=AVERAGE(A1:A5, C1:C5)
-
Calculates the average of two separate ranges.
4. AVERAGE Variants
| Function | Purpose |
|---|---|
| AVERAGEIF | Calculates the average for cells that meet a specific condition. Example: =AVERAGEIF(B1:B10, ">50") |
| AVERAGEIFS | Calculates the average for cells that meet multiple conditions. Example: =AVERAGEIFS(C1:C10, B1:B10, ">50", D1:D10, "East") |
| SUBTOTAL | Can calculate average ignoring hidden rows in filtered data. Example: =SUBTOTAL(1, A1:A10) |
5. Practical Examples
A. Average Test Score
=AVERAGE(B2:B10)
-
Calculates the average score of students in cells B2 to B10.
B. Average Sales with Condition
=AVERAGEIF(C2:C20, ">10000")
-
Calculates the average sales where the value is greater than 10,000.
C. Average of Multiple Criteria
=AVERAGEIFS(D2:D50, B2:B50, "East", C2:C50, ">5000")
-
Calculates the average of sales in the East region where sales are greater than 5000.
6. Tips for Using AVERAGE
-
Ignore blanks: AVERAGE ignores empty cells automatically.
-
Ignore text: Text values are ignored, but cells with 0 are included.
-
Combine with IFERROR: Prevent errors if dividing by zero:
=IFERROR(AVERAGE(A1:A10), 0) -
Use with Dynamic Ranges: Use tables or named ranges to make averages update automatically.
