AVERAGE Function in Google Sheets: Complete Guide
The AVERAGE function calculates the arithmetic mean of a set of numbers—add them up, divide by the count. Simple in concept, but surprisingly nuanced in practice. This function forms the backbone of...
Key Insights
- AVERAGE ignores empty cells and text but includes zeros—understanding this distinction prevents calculation errors that silently corrupt your data analysis.
- AVERAGEIF and AVERAGEIFS unlock conditional averaging that eliminates the need for helper columns and complex nested formulas in most business scenarios.
- Combining AVERAGE with FILTER creates dynamic calculations that automatically adapt to changing data, making your spreadsheets more maintainable than static range references.
Introduction to AVERAGE
The AVERAGE function calculates the arithmetic mean of a set of numbers—add them up, divide by the count. Simple in concept, but surprisingly nuanced in practice. This function forms the backbone of statistical analysis in Google Sheets, from grading systems to financial dashboards.
You’ll reach for AVERAGE when you need a central tendency measure that accounts for the magnitude of values. Unlike MEDIAN (which finds the middle value) or MODE (which finds the most frequent), AVERAGE gives weight to every number in your dataset. A single outlier can dramatically shift your result—sometimes that’s exactly what you want, sometimes it’s a problem you need to handle.
This guide covers everything from basic syntax to production-ready formulas you can drop into real projects.
Basic Syntax and Parameters
The function signature is straightforward:
=AVERAGE(value1, [value2, ...])
value1 is required. Everything else is optional. You can pass up to 255 arguments, though you’ll rarely need more than a few.
Here’s the basic range average:
=AVERAGE(B2:B100)
This calculates the mean of all numeric values in cells B2 through B100. The range can span rows, columns, or both.
For individual values:
=AVERAGE(85, 92, 78, 88, 95)
Returns 87.6—the arithmetic mean of five test scores.
Mixed inputs work too:
=AVERAGE(B2:B10, D2:D10, 100)
This averages all values from two separate ranges plus a hardcoded value. Useful when you need to include a baseline or target in your calculation.
You can also reference other sheets:
=AVERAGE('Q1 Sales'!C2:C50, 'Q2 Sales'!C2:C50)
The function flattens all inputs into a single list of numbers before calculating.
Handling Special Cases
This is where AVERAGE trips up most users. The function’s behavior with non-numeric data isn’t always intuitive.
Empty cells are ignored entirely:
| A |
|-------|
| 10 |
| |
| 20 |
| |
| 30 |
=AVERAGE(A1:A5) // Returns 20 (sum of 60 ÷ 3 values)
The two empty cells don’t count toward the divisor. This is usually what you want.
Zeros are included:
| A |
|-------|
| 10 |
| 0 |
| 20 |
| 0 |
| 30 |
=AVERAGE(A1:A5) // Returns 12 (sum of 60 ÷ 5 values)
Big difference. A zero is a value; an empty cell is the absence of a value. If your data uses zero to mean “no data,” you’ll get skewed results.
To exclude zeros:
=AVERAGEIF(A1:A5, "<>0")
Text values are ignored:
| A |
|---------|
| 10 |
| pending |
| 20 |
| N/A |
| 30 |
=AVERAGE(A1:A5) // Returns 20 (ignores text)
No error, no warning—text just disappears from the calculation. This can mask data quality issues.
Boolean values depend on context:
=AVERAGE(TRUE, FALSE, 10) // Returns 10 (booleans ignored)
=AVERAGE(1, 0, 10) // Returns 3.67 (all included)
When you type TRUE/FALSE directly or reference cells containing boolean values, AVERAGE ignores them. If you need booleans counted (TRUE=1, FALSE=0), use AVERAGEA instead.
AVERAGE Variants and Related Functions
Google Sheets provides several AVERAGE variants for different scenarios.
AVERAGEA includes text (as 0) and booleans (TRUE=1, FALSE=0):
| A |
|---------|
| 10 |
| TRUE |
| hello |
| 20 |
=AVERAGE(A1:A4) // Returns 15 (only 10 and 20)
=AVERAGEA(A1:A4) // Returns 7.75 (10 + 1 + 0 + 20) ÷ 4
Use AVERAGEA when you explicitly want text treated as zero and booleans converted to numbers.
AVERAGEIF applies a single condition:
=AVERAGEIF(B2:B50, ">1000")
Averages only values greater than 1000. The condition can reference another range:
=AVERAGEIF(A2:A50, "Electronics", B2:B50)
This averages values in column B where column A equals “Electronics.”
AVERAGEIFS handles multiple conditions:
=AVERAGEIFS(
C2:C100, // Values to average
A2:A100, "Sales", // Condition 1: Department = Sales
B2:B100, ">=2024-01-01" // Condition 2: Date >= Jan 1, 2024
)
All conditions must be true for a value to be included. There’s no built-in OR logic—you’d need helper columns or AVERAGE with FILTER for that.
Quick decision guide:
- Simple numeric average → AVERAGE
- Include text/booleans → AVERAGEA
- One filter condition → AVERAGEIF
- Multiple filter conditions → AVERAGEIFS
- Complex logic → AVERAGE + FILTER
Combining AVERAGE with Other Functions
The real power emerges when you combine AVERAGE with other functions.
AVERAGE + FILTER for dynamic subsets:
=AVERAGE(FILTER(B2:B100, A2:A100="Active"))
Averages column B values only where column A is “Active.” More flexible than AVERAGEIF because FILTER supports complex boolean expressions:
=AVERAGE(FILTER(C2:C100, (A2:A100="Sales") + (A2:A100="Marketing")))
The + acts as OR—includes rows where department is Sales OR Marketing.
Excluding outliers:
=AVERAGE(FILTER(B2:B100,
(B2:B100 >= PERCENTILE(B2:B100, 0.1)) *
(B2:B100 <= PERCENTILE(B2:B100, 0.9))
))
This calculates a trimmed mean, excluding the top and bottom 10% of values. Essential for datasets with extreme outliers.
Rounding results:
=ROUND(AVERAGE(B2:B50), 2)
Rounds to 2 decimal places. For currency, consider:
=DOLLAR(AVERAGE(B2:B50))
Conditional average with IF:
=IF(COUNT(B2:B50) >= 5, AVERAGE(B2:B50), "Insufficient data")
Only calculates the average if there are at least 5 data points.
ARRAYFORMULA for row-by-row averages:
=ARRAYFORMULA(AVERAGE(B2:D2)) // Single row
For multiple rows, you need a different approach since AVERAGE doesn’t naturally expand:
=ARRAYFORMULA((B2:B100 + C2:C100 + D2:D100) / 3)
Or use MMULT for variable column counts.
Common Errors and Troubleshooting
#DIV/0! error occurs when the range contains no numeric values:
=AVERAGE(A1:A10) // #DIV/0! if all cells are empty or text
Fix with IFERROR:
=IFERROR(AVERAGE(A1:A10), 0)
Or provide a meaningful message:
=IFERROR(AVERAGE(A1:A10), "No data available")
Better yet, check proactively:
=IF(COUNT(A1:A10) > 0, AVERAGE(A1:A10), "No numeric values")
#VALUE! error typically means you’ve passed an invalid argument:
=AVERAGE("hello") // #VALUE!
=AVERAGE(A1:A10, "not a number") // #VALUE!
Unlike ranges where text is ignored, direct text arguments cause errors.
Silent wrong results are the most dangerous. Your formula returns a number, but it’s wrong:
- Zeros included when they represent missing data
- Date range accidentally including header row (which might be text)
- Referenced range doesn’t expand as data grows
Use named ranges with automatic expansion:
=AVERAGE(SalesData) // Where SalesData is a named range
Or table references if you’ve formatted data as a table.
Real-World Use Cases
Student grade calculator:
| Student | Test1 | Test2 | Test3 | Final | Average |
|---------|-------|-------|-------|-------|---------|
| Alice | 85 | 92 | 88 | 90 | |
| Bob | 78 | 85 | | 82 | |
In the Average column:
=IFERROR(AVERAGE(B2:E2), "Incomplete")
For weighted averages (tests worth 20% each, final worth 40%):
=B2*0.2 + C2*0.2 + D2*0.2 + E2*0.4
Monthly sales dashboard:
=AVERAGEIFS(
Sales!D:D,
Sales!A:A, ">="&DATE(2024,1,1),
Sales!A:A, "<"&DATE(2024,2,1),
Sales!B:B, "Completed"
)
Calculates average sale amount for completed orders in January 2024.
Rolling average for trend analysis:
=AVERAGE(FILTER(B:B,
(A:A >= TODAY()-30) * (A:A <= TODAY())
))
30-day rolling average that updates automatically.
Performance metrics with thresholds:
=COUNTIF(B2:B100, ">="&AVERAGE(B2:B100)) / COUNT(B2:B100)
Returns the percentage of values at or above average—useful for identifying top performers.
The AVERAGE function is deceptively simple. Master its edge cases and combinations, and you’ll handle 90% of statistical calculations in Google Sheets without reaching for more complex tools.