AVERAGE Function in Excel: Complete Guide (2025)
Quick Answer: AVERAGE calculates mean: =AVERAGE(A1:A10) or =AVERAGE(10,20,30). Ignores text/blanks automatically. With conditions: =AVERAGEIF(range, criteria) or =AVERAGEIFS(avg_range, range1, criteria1, ...).
What is AVERAGE?
AVERAGE calculates the arithmetic mean of numbers by adding them up and dividing by the count. It's one of Excel's most fundamental statistical functions.
Simple Example:
=AVERAGE(A1:A10) // Adds: A1 + A2 + A3 + ... + A10 // Divides by: count of numbers // Result: arithmetic mean
Example: =AVERAGE(10, 20, 30) returns 20 → (10+20+30)/3 = 60/3 = 20
Test Scores
=AVERAGE(B2:B20)
Calculate class average
Sales Performance
=AVERAGE(Sales!C:C)
Average daily sales
Response Time
=AVERAGE(D2:D100)
Mean response time
💡 Common Use Cases
- • Academic: Calculate grade point averages, test scores
- • Business: Average sales, revenue, profit margins
- • Performance: Mean response times, load times
- • Finance: Average stock prices, returns, expenses
- • Analytics: Mean user engagement, conversion rates
AVERAGE Function Family
1. AVERAGE
=AVERAGE(number1, [number2], [number3], ...)
Basic average of all numbers. Ignores text and blank cells.
=AVERAGE(A1:A10) ← Average range=AVERAGE(10, 20, 30) ← Average values=AVERAGE(A1:A5, C1:C5) ← Multiple ranges2. AVERAGEIF
=AVERAGEIF(range, criteria, [average_range])
Average cells that meet one condition.
=AVERAGEIF(A1:A10, ">50") ← Values > 50=AVERAGEIF(A:A, "Complete", B:B) ← Average B where A="Complete"3. AVERAGEIFS
=AVERAGEIFS(average_range, criteria_range1, criteria1, [range2, criteria2], ...)
Average cells that meet multiple conditions (AND logic).
=AVERAGEIFS(C:C, A:A, "North", B:B, ">100")Average C where A="North" AND B>100
4. AVERAGEA
=AVERAGEA(value1, [value2], ...)
Like AVERAGE but includes text (as 0) and logical values.
Note: Rarely needed. Use AVERAGE for numbers only.
Key Differences
| Function | Conditions | Best For |
|---|---|---|
| AVERAGE | None | Simple averages |
| AVERAGEIF | 1 condition | Filtered averages |
| AVERAGEIFS | Multiple (AND) | Complex filtering |
| AVERAGEA | None | Include text as 0 |
12+ AVERAGE Examples
1. Basic Average of Range
=AVERAGE(A2:A20)
Average all values in range A2:A20
2. Average Individual Values
=AVERAGE(85, 92, 78, 95)
Returns 87.5 (average of 4 scores)
3. Multiple Ranges
=AVERAGE(A2:A10, C2:C10, E2:E10)
Average across multiple non-adjacent ranges
AVERAGEIF Examples
4. Average Greater Than
=AVERAGEIF(B2:B100, ">50")
Average only values greater than 50
5. Average by Category
=AVERAGEIF(A2:A100, "Electronics", C2:C100)
Average prices (C) where category (A) is "Electronics"
6. Dynamic Criteria
=AVERAGEIF(Region, E2, Sales)
Average sales for region specified in E2
AVERAGEIFS Examples
7. Two Conditions
=AVERAGEIFS(D2:D100, A2:A100, "North", C2:C100, ">=100")
Average D where Region="North" AND Value≥100
8. Date Range Average
=AVERAGEIFS(Sales, Date, ">="&DATE(2025,1,1), Date, "<="&DATE(2025,12,31))
Average sales for year 2025
9. Exclude Values
=AVERAGEIFS(C2:C100, B2:B100, "<>Cancelled", C2:C100, ">0")
Average excluding cancelled orders and zero values
Advanced Techniques
10. Top 3 Average
=AVERAGE(LARGE(A2:A20, {1,2,3}))Average of 3 highest values
11. Exclude Outliers
=AVERAGEIFS(A:A, A:A, "<"&AVERAGE(A:A)*1.5, A:A, ">"&AVERAGE(A:A)*0.5)
Average excluding values 50% above/below mean
12. Moving Average (Last 7 Days)
=AVERAGE(OFFSET(A2,COUNT(A:A)-7,0,7,1))
Average of last 7 entries
🎯 Pro Tips
- • Blank cells: Automatically ignored (not counted as 0)
- • Error handling: Use IFERROR to handle div/0: =IFERROR(AVERAGE(A:A), 0)
- • Text ignored: AVERAGE skips text; use AVERAGEA to include as 0
- • Median alternative: For skewed data, use MEDIAN instead of AVERAGE
- • Weighted average: Use SUMPRODUCT: =SUMPRODUCT(values, weights)/SUM(weights)
Frequently Asked Questions
Why does AVERAGE return #DIV/0! error?
#DIV/0! means no numbers found to average. Common causes: 1) All cells are blank/text, 2) Range contains only errors, 3) All values filtered out by criteria. Use IFERROR to handle: =IFERROR(AVERAGE(A:A), 0).
Does AVERAGE count blank cells as zero?
No! AVERAGE ignores blank cells completely. Example: =AVERAGE(1,2,"",3) returns 2 (not 1.5). Only actual numbers are included. To treat blanks as zero, use: =SUM(A1:A10)/COUNTA(A1:A10) or fill blanks with 0 first.
How do I calculate weighted average?
Use SUMPRODUCT: =SUMPRODUCT(values, weights)/SUM(weights). Example: =SUMPRODUCT(A2:A10, B2:B10)/SUM(B2:B10) where A=values, B=weights. This multiplies each value by its weight before averaging.
What's the difference between AVERAGE and MEDIAN?
AVERAGE is arithmetic mean (sum/count). MEDIAN is middle value when sorted. Use AVERAGE for normal distributions, MEDIAN for skewed data with outliers. Example: Salaries [30k,35k,40k,1M] → AVERAGE=276k (misleading), MEDIAN=37.5k (typical).
Can I average the top 10 values only?
Yes! Use AVERAGE with LARGE: =AVERAGE(LARGE(A1:A100, ROW(1:10))). Enter as array formula (Ctrl+Shift+Enter in older Excel). This averages the 10 highest values in the range.
How do I exclude zeros from average?
Use AVERAGEIF: =AVERAGEIF(A1:A10, "<>0") or =AVERAGEIF(A1:A10, ">0"). The first excludes all zeros, the second only includes positive numbers. Both ignore zeros in the calculation automatically.