AVERAGE vs MEDIAN: Which Tells the Truth?

AVERAGE vs MEDIAN matters whenever someone asks for a "typical" number. AVERAGE adds everything up and divides, so one very large value can drag it far from what most of the data looks like, while MEDIAN takes the middle value and barely moves. This guide is for anyone writing reports, describing salaries or sales, or answering a statistics question in an Excel test.

AVERAGE vs MEDIAN at a glance#

Both describe the centre of a list of numbers. They answer slightly different questions.

The same data, measured both ways#

Here are seven orders. Six are normal; one is a bulk order.

=AVERAGE(B2:B8)
=MEDIAN(B2:B8)

• AVERAGE adds the seven values (1,500) and divides by 7: 214.29.
• MEDIAN sorts them (38, 45, 49, 52, 55, 61, 1,200) and takes the fourth, the middle one: 52.

Which is the "typical" order? Six of the seven orders are between 38 and 61. The average of 214.29 is bigger than every order except one. The median of 52 describes most customers far better.

The bulk order is an outlier: a value far from the rest. Take it out and the two measures agree:

=AVERAGE(B2:B7)   → 50
=MEDIAN(B2:B7)    → 50.5

With six values there is no single middle one, so MEDIAN averages the two middle values, 49 and 52, to get 50.5.

When to use MEDIAN#

Use MEDIAN when you want the value a typical item has, and the data might contain a few extreme values. It is the better choice for:

• Salaries. One senior salary can lift the average well above what most staff earn. That's why salary reports usually quote a median.
• House prices and rents. A few luxury properties skew the average up.
• Order or deal sizes, as in the example above.
• Waiting and response times. A handful of very slow tickets can make the average look worse than most customers' experience.
• Any data where the average and median are far apart. That gap is itself a sign of skew.

To get a median for one group only, combine it with FILTER (Excel 2021, Excel 2024 or Microsoft 365): =MEDIAN(FILTER(C2:C20,B2:B20="East")). Our MEDIAN and MODE guide covers MODE too, which finds the most common value.

When to use AVERAGE#

Use AVERAGE when the values are fairly even, or when the total matters. It is the better choice for:

• Budgets and forecasts. If the average order is 214.29 and you expect 70 orders, you can expect about 15,000 in revenue. The median can't give you that total.
• Rates and scores that cluster closely, such as test scores or call times within a narrow range.
• Performance comparisons where every unit counts equally, such as average calls handled per agent.
• Conditional averages, which have their own functions: =AVERAGEIF(B2:B8,"<1000") averages only the orders under 1,000 (50).

The AVERAGE function guide shows more examples, and our article on AVERAGE, MIN and MAX covers the summary functions you usually report alongside it.

Common mistakes with AVERAGE and MEDIAN#

These lead to misleading numbers:

1. Reporting only the average for skewed data. It looks precise but describes almost nobody. Check the median, and report both if they are far apart.
2. Typing 0 for missing values. Both functions count zeros. An empty cell is skipped, but a 0 pulls the average down and shifts the median. Leave unknown values blank.
3. Numbers stored as text. Values imported as text, often shown aligned to the left, are skipped inside a range. The result looks fine but is based on fewer values.
4. Averaging averages. The average of three regional averages is not the overall average unless every region has the same number of items. Use the full data, or weight each region by its count.
5. Including the total row. =AVERAGE(B2:B9) with a total in B9 counts the total as one of the values. Stop the range above the total.
6. Rounding too early. Round the final result with =ROUND(AVERAGE(B2:B8),2), not the values you feed in.

What about MODE?#

MODE is the third way to describe a "typical" value: it returns the number that appears most often.

=MODE(D2:D11)

For order sizes of 1, 1, 1, 2, 3, 4, 5, 6, 2 and 1 items, MODE returns 1: single-item orders are the most common. It is useful for counts like items per order or people per booking, where you want the most frequent case rather than the middle one.

MODE has limits. If no value repeats, it returns #N/A. If two values tie for most common, it still returns just one of them, so check the data when the answer matters. And it only works on numbers; to find the most common text value, such as a top product, count each one with COUNTIF and take the largest.

How do I spot an outlier quickly?#

Compare the average with the median. If they are close, the data is fairly even and either one works. If the average is much higher than the median, a few large values are pulling it up. If it is much lower, a few small values are pulling it down.

=AVERAGE(B2:B8)-MEDIAN(B2:B8)

On the orders above this returns 162.29: a strong signal that one value is distorting the average. Then use =MAX(B2:B8) and =MIN(B2:B8) to find it.

These formulas are identical in Google Sheets and in Excel on Windows and Mac, so what you learn here works in any test environment. See our comparisons of Excel vs Google Sheets for job tests and Excel on Mac vs Windows for the parts that do differ. If you need to find which row the outlier is in, MATCH vs XMATCH shows how to return its position.

Our recommendation#

For a "typical" value, start with MEDIAN whenever the data could be skewed: money, prices, sizes and times usually are. Use AVERAGE when the values are even, or when you need to multiply back to a total, as in budgets and forecasts.

When you present numbers to a manager or in an interview, the strongest answer is often both: "The average order is 214, but the median is 52, because one bulk order of 1,200 pulls the average up." That shows you know what the numbers mean, not just how to calculate them.

Practise it#

Measure the outlier's pull in the exercise on this page. Then work through the MEDIAN and MODE lesson, where a founder's salary skews a company's average, and the AVERAGE, MIN and MAX lesson to summarize a call centre team in one quick set of formulas.