What They Do
AVERAGE calculates the arithmetic mean of a set of numbers — add them all up and divide by the count. AVERAGEIF does the same thing but only includes cells that meet a condition you specify. Together they cover everything from simple class grade calculations to targeted performance metrics like "average order value for customers in California."
Syntax
=AVERAGE(value1, [value2, ...])
=AVERAGEIF(criteria_range, criterion, [average_range])
| Parameter | Description |
|---|---|
| value1, value2, ... | Numbers, ranges, or cell references to average. |
| criteria_range | The range to evaluate against the criterion. |
| criterion | The condition to match (number, text, or expression like ">100"). |
| average_range | Optional. The range to actually average. If omitted, criteria_range is averaged. |
Basic Examples
Calculate the average of a set of scores
| A | |
|---|---|
| 1 | Score |
| 2 | 85 |
| 3 | 92 |
| 4 | 78 |
| 5 | 88 |
=AVERAGE(A2:A5)
Result: 85.75
The sum (343) divided by the count (4).
Average only passing scores (70 or above)
Using the same data:
=AVERAGEIF(A2:A5, ">=70")
Result: 85.75 (all scores are above 70 in this case)
If A5 were 55, the result would change to 85 — averaging only 85, 92, and 78.
Average sales for a specific region
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | East | 12000 |
| 3 | West | 9500 |
| 4 | East | 14200 |
| 5 | West | 11000 |
=AVERAGEIF(A2:A5, "East", B2:B5)
Result: 13100
Only rows where column A is "East" contribute to the average: (12000 + 14200) / 2.
Advanced Examples
Compare individual performance to team average
Suppose column B has monthly sales for each rep. You want to flag anyone below the team average:
=IF(B2 < AVERAGE(B$2:B$20), "Below average", "On track")
The dollar signs lock the AVERAGE range so it stays fixed as you copy the formula down. Each row is then compared to the same team-wide mean.
Average the top scores excluding outliers
To average only values within a reasonable range (say, between 50 and 100), nest AVERAGEIFS:
=AVERAGEIFS(A2:A100, A2:A100, ">=50", A2:A100, "<=100")
AVERAGEIFS (with an S) accepts multiple criteria. This averages only the scores in A2:A100 that fall between 50 and 100, filtering out data entry errors or outliers without manually cleaning the data.
Common Mistakes
- Including header rows in the range.
=AVERAGE(A1:A10)when A1 is the text "Score" will skip the text cell, but it is confusing and error-prone. Start your range at the first data row:=AVERAGE(A2:A10). - Forgetting that AVERAGE ignores empty cells but not zeros. If some cells contain
0instead of being blank, those zeros pull the average down. An empty cell is excluded from the count; a cell with0is not. If zeros represent missing data, replace them with blanks or use AVERAGEIF to exclude them:=AVERAGEIF(A2:A10, "<>0"). - Using AVERAGEIF when you need multiple conditions. AVERAGEIF supports only one criterion. For two or more conditions (e.g., region = "East" AND month = "March"), switch to AVERAGEIFS. The syntax is similar but allows multiple criteria_range/criterion pairs.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.