Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
AVERAGE
AggregationThe arithmetic mean of a range — total ÷ count, with blanks left out.
Difficulty
1What is it?
AVERAGE returns the mean of the numbers in a range: it adds them up and divides by how many there are. The key subtlety is what it ignores — blank cells and text are left out of both the sum and the count, so an average across ten cells where three are empty divides by seven, not ten. That's usually what you want, but it's the thing that surprises people.
2What it looks like
AVERAGE(number1, [number2], …)- number1
- The first number or range to average.
- [number2], …
- Optional more numbers or ranges.
3When you use it
- Average order value, average monthly revenue, or average score.
- Blend several ranges into one mean.
- Pair with a filter or conditions (AVERAGEIFS) for a segment-level average.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
The mean of B2:B5 — the total divided by how many numbers there are.
=AVERAGE(B2:B5)$87,500| A | B | C | |
|---|---|---|---|
| 1 | Month | Revenue | Result |
| 2 | Jan | $87,500 | |
| 3 | Feb | ||
| 4 | Mar | ||
| 5 | Apr |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#DIV/0!The range has no numbers to average (all blank or text).Fix: Wrap in IFERROR for a clean 0, or confirm the range actually holds numbers.
Blanks aren't zeroYou expected empty cells to count as 0 and pull the average down.Fix: They're skipped, not zeroed — fill them with 0, or use AVERAGEA to count text/logicals as values.
Answer looks highNumbers stored as text are excluded from the count.Fix: Convert text-numbers to real numbers so they're included.
6Better functions & alternatives
- AVERAGEIFS — Average only the rows that meet conditions.
- MEDIAN — The middle value — resists a few big outliers better than the mean.
- AVERAGEA — Like AVERAGE but counts text as 0 and TRUE/FALSE as 1/0.
Want AVERAGE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →