AVERAGE
Average the numbers.
Find the typical figure in a column
The total of the numbers divided by how many there were
Syntax
=AVERAGE(range1, [range2], ...)
| Argument | Required | What goes here |
|---|---|---|
range1 | Required | The cells to average, like C2:C13. |
range2 | Optional | The cells to average, like C2:C13. |
How to use AVERAGE
AVERAGE adds the numbers and divides by how many it found. Blanks and text are ignored rather than counted as nought, which changes the answer.
That is usually right and occasionally very wrong. If a missing amount really means nought, the average is being taken over too few rows and comes out too high.
An average over a handful of rows moves a long way on one large item. On a ledger it is a starting point rather than a conclusion, and MAX is often the more useful question.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=AVERAGE(E2:E9)The typical invoice on this ledger. | 981.25 |
=ROUND(AVERAGE(E2:E9),2)Rounded to the penny, because an average is where the trailing decimals come from. | 981.25 |
=SUM(E2:E9)/COUNT(E2:E9)The same answer written out, which is worth seeing once so the blanks question is obvious. | 981.25 |
=AVERAGE(D2:D9)Average days overdue, counting the rows that are not overdue at all. SUMPRODUCT weighting is usually the figure that gets reported. | 34.375 |
Notes
- AVERAGE skips blanks. If a blank means nought, the answer is too high and nothing warns you.
- AVERAGEIF and AVERAGEIFS narrow it to the rows that meet a condition.
Related
A reference tells you what it does. A unit makes you use it.