AVERAGE()
Calculates the arithmetic mean of a set of numbers.
AVERAGE(number1, [number2], ...)AVERAGE adds up the numbers you give it and divides by how many there are. It ignores blank cells and text, but a cell containing 0 still counts toward the total.
For an average with a condition, use AVERAGEIF or AVERAGEIFS.
Arguments
- number1
- A number, cell reference, or range to average.
- number2, ...optional
- Additional numbers, cells, or ranges to include.
Examples
=AVERAGE(B2:B20)→2,539
The mean of every value in the range.
Tips / Tricks / Hacks
AVERAGE tricks, tips, and workbook hacks
AVERAGE looks simple, but most workbook mistakes around it come from the question behind the formula: what exactly should count in the denominator? A blank cell, a zero, a hidden row, an error value, and a text label can all change the story of the number you report.
Use this section as a practical checklist before you trust an average in a dashboard, report, scorecard, budget model, or operations tracker.
Quick wins before you trust the result
- Check whether zero means a real zero or missing data before averaging.
- Use AVERAGEIF or AVERAGEIFS when the average should only include a segment, month, region, product, or status.
- Use SUBTOTAL or AGGREGATE instead of AVERAGE when filters or hidden rows should affect the result.
- Pair AVERAGE with COUNT, COUNTBLANK, MIN, and MAX to explain what the average is hiding.
- Keep the average range numeric; labels, comments, and helper text belong outside the measured range.
| Trick or hack | Formula pattern | Best use | Watch out |
|---|---|---|---|
| Ignore blanks, count zeroes intentionally | =AVERAGE(B2:B100) | A normal average where blanks should be ignored but actual 0 values should reduce the result. | If 0 was typed to mean missing, the answer will be lower than the real average. |
| Average only completed rows | =AVERAGEIF(C2:C100,"Complete",B2:B100) | Project trackers, training scores, order processing, or any sheet with a status column. | The criteria range and average range must be the same size. |
| Average by two or more conditions | =AVERAGEIFS(D2:D500,A2:A500,"North",B2:B500,"2026") | Regional performance, monthly KPIs, category analysis, or filtered business reporting. | AVERAGEIFS returns #DIV/0! when no row meets all conditions. |
| Average visible filtered rows | =SUBTOTAL(101,B2:B100) | Tables where users filter rows and expect the summary number to update. | Function number 101 ignores manually hidden rows; 1 includes them. |
| Average while ignoring errors | =AGGREGATE(1,6,B2:B100) | Imported or calculated data where some rows may contain #N/A, #DIV/0!, or #VALUE!. | Do not hide errors automatically unless you know why they appear. |
| Trim outlier influence | =TRIMMEAN(B2:B100,10%) | Survey results, delivery times, and sales-cycle metrics where extreme values distort the mean. | TRIMMEAN changes the business meaning; document why outliers were trimmed. |
| Calculate a weighted average | =SUMPRODUCT(B2:B100,C2:C100)/SUM(C2:C100) | Average price, average margin, class grades, inventory cost, or any metric where rows carry different weights. | A plain AVERAGE gives every row equal weight, which is often wrong for business data. |
| Average the latest period only | =AVERAGEIF(A2:A100,MAX(A2:A100),B2:B100) | Reports where the date/month column identifies the latest period to summarize. | Works best when the date column contains true Excel dates, not text that looks like dates. |
| Mistake | What it looks like | Fix |
|---|---|---|
| A blank and a zero are treated differently | The average looks too low or too high compared with the visible rows. | Decide whether missing data should be blank, zero, or excluded by criteria. |
| Filtered rows still count | You filter a table but the average does not change. | Use SUBTOTAL(101, range) or AGGREGATE when the visible rows should drive the result. |
| Outliers dominate the story | One huge or tiny value pulls the average away from the typical case. | Show MEDIAN, MIN, MAX, or TRIMMEAN alongside AVERAGE. |
| Mixed units in one range | You average dollars, percentages, hours, or counts together by accident. | Keep one metric per column and label every column clearly. |
| Errors break the result | AVERAGE returns an error even though most cells are valid numbers. | Fix the source error first, or use AGGREGATE only when ignoring errors is intentional. |
| Scenario | Use | Avoid |
|---|---|---|
| You need a quick answer from clean data | AVERAGE with the smallest clear range | Overbuilding the formula with unnecessary wrappers |
| The result will appear in a dashboard | Add checks, labels, and readable fallbacks | Leaving raw errors or unexplained blanks for viewers |
| The workbook will grow over time | Excel Tables, named ranges, or structured references | Hard-coded ranges that miss new rows |
| The data comes from another system | Clean inputs before summarizing or transforming | Assuming pasted data has correct types and spacing |
Practice drills
- Create a small table with sales, region, month, and status. Write one AVERAGE for all sales, then one AVERAGEIF for one region.
- Add a zero and a blank to the same range. Watch how the result changes, then explain why.
- Filter the table to one region. Compare AVERAGE against SUBTOTAL(101, range).
- Add one extreme outlier. Compare AVERAGE, MEDIAN, MIN, MAX, and TRIMMEAN.
- Build a weighted average using quantity and unit price, then compare it with a plain AVERAGE of prices.
