Average vs median in Excel: which number to trust
One big value pulls the average up; the median does not move. When each one tells the truth about your data, with salaries, delivery times and MODE.
AVERAGE adds up the numbers and divides by how many there are. MEDIAN sorts them and takes the middle one. One very big value pulls the average up, but the median does not move.
So use the median to say what is typical. Use the average when you need the total back. The two sheets below show why.
Delivery times: one parcel at customs
A small shop checks how many days its last 10 orders took to arrive. One parcel was held at customs for 41 days.
| Row | A | B |
|---|---|---|
| 1 | Order | Days |
| 2 | 5101 | 2 |
| 3 | 5102 | 3 |
| 4 | 5103 | 3 |
| 5 | 5104 | 2 |
| 6 | 5105 | 4 |
| 7 | 5106 | 3 |
| 8 | 5107 | 5 |
| 9 | 5108 | 2 |
| 10 | 5109 | 3 |
| 11 | 5110 | 41 |
=AVERAGE(B2:B11) returns 6.8.
=MEDIAN(B2:B11) returns 3.
Put 6.8 days on the website and it is wrong for almost everyone.
=COUNTIF(B2:B11,"<"&AVERAGE(B2:B11)) returns 9. So 9 of the
10 orders arrived faster than the average.
The late parcel took 38 days longer than a typical one. The average shares those days out over
all 10 orders. Take row 11 away and =AVERAGE(B2:B10) returns 3.
How MEDIAN finds the middle
MEDIAN puts the values in order: 2, 2, 2, 3, 3, 3, 3, 4, 5, 41. There are 10 values, so there is no single middle one. MEDIAN takes the 5th and 6th values and averages them. Both are 3, so the answer is 3.
With an odd count, it is simply the middle value. With an even count, it can be a value that is
not in the list. =MEDIAN(2,3,4,5) returns 3.5.
Make the late parcel later
Change B11 from 41 to 90. Now the average returns 11.7. The median still returns 3.
That is the whole difference. The median only cares that 41 or 90 is the biggest value. It does not care how big it is. The average uses every day of it.
Salaries: the owner in the list
A team of 7, with the owner's salary in the same column, in B3:B9 of a second sheet.
| Row | A | B |
|---|---|---|
| 1 | Team salaries | |
| 2 | Role | Salary |
| 3 | Assistant | 28,000 |
| 4 | Driver | 30,000 |
| 5 | Driver | 31,000 |
| 6 | Buyer | 32,000 |
| 7 | Accountant | 34,000 |
| 8 | Manager | 36,000 |
| 9 | Owner | 120,000 |
=AVERAGE(B3:B9) returns 44428.57, to 2 decimal places.
The cell holds more decimals than it shows. The article on rounding in Excel explains why that matters.
=MEDIAN(B3:B9) returns 32000.
6 of the 7 people earn less than the average. If a job advert says "average salary 44,000", it describes nobody on the team. The median, 32,000, is what a new driver or buyer can expect.
A quick test for any column is =AVERAGE(B3:B9)-MEDIAN(B3:B9).
Here it returns about 12429. When the gap is big next to the numbers, look for the one value
that causes it.
When the average is the right number
The owner now asks a different question. What will the team cost next year? For that, the median is the wrong tool.
=AVERAGE(B3:B9)*COUNT(B3:B9) returns 311000. That is the real
total, the same as =SUM(B3:B9).
=MEDIAN(B3:B9)*COUNT(B3:B9) returns 224000. That budget is
87,000 short.
The average is built from the total, so it gives the total back. The median forgets the size of the big values, and money has to include them. Use the average for budgets, costs and stock plans.
MODE: the most common value
MODE returns the value that appears most often. =MODE(B2:B11) on
the delivery sheet returns 3. 4 of the 10 orders took 3 days.
It is useful when the values repeat, like shoe sizes or days to deliver. "Most orders arrive in 3 days" is a line a customer can read.
On the salary sheet, =MODE(B3:B9) returns
#N/A. No salary appears twice, so there is no most common value.
Newer Excel also has MODE.SNGL. It is the same function with a newer name.
TRIMMEAN: an average without the ends
TRIMMEAN cuts off a share of the values at both ends, then averages the rest. You give it the range and the share to cut.
=TRIMMEAN(B2:B11,0.2) returns 3.125.
20% of 10 values is 2 values. TRIMMEAN takes 1 from the bottom and 1 from the top: one 2 and the 41. It averages the 8 values left, which add up to 25. 25 divided by 8 is 3.125.
One thing surprises people. TRIMMEAN rounds the number of values to cut down to an even number. With 9 values and 0.2, that is 1.8, which rounds down to 0. So nothing is cut, and you get the plain average.
Empty cells and zeros
AVERAGE and MEDIAN both skip empty cells. Both count a 0 as a value. So an order that has not arrived yet must stay empty.
Type 0 in its place and the order counts as delivered in 0 days. Both numbers go down, and nothing looks wrong.
Which number to use
- To say what is typical: use MEDIAN. Delivery times, salaries and house prices often have a few very big values.
- To get a total back, or plan money: use AVERAGE. Only the average times the count gives the sum.
- To name the most common value: use MODE, on values that repeat.
When the average and the median are far apart, report both. Then say which value caused the gap. In the delivery sheet, that one sentence is "one parcel was held at customs for 41 days".
The lessons below practise AVERAGE, MEDIAN and MODE on real data. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.