Averages that mislead: mean, median and mode

Bisi's average sale is ₦11,755.

Now count how many of her 938 sales are actually above ₦11,755.

199. That's 21.2%.

Nearly four sales in five are below average. The word "average" carries a promise of typicality that this number does not keep, and a shop owner who stocks her shelves for an ₦11,755 customer is planning for a person who walks in about one time in five.

The median sale is ₦4,950. The single most common sale is ₦3,900. Those describe her real customers. The mean describes her total divided by her count, which is a different thing entirely, and the confusion between them costs businesses real money every day.

Words in this lesson

Three different questions

People say "average" and mean one of three things:

Three correct answers. They differ by a factor of three, and each is right for a different question.

Why Bisi's mean is so much higher

Look at what the numbers actually do:

Sales can't go below zero, but they can go a very long way up. A 50kg bag of rice at ₦86,500 is more than twenty times the median. Those big sales pull the mean up; they can't pull it down, because there's nothing below zero to balance them.

This is right skew, and it is the normal shape of money data everywhere: sales, salaries, rents, transaction sizes. When you are looking at money, assume it is skewed until you have checked.

The 50kg rice bag shows up twice

Something revealing: the three most common sale amounts in Bisi's file are

The second most common sale is also the largest possible one: the 50kg rice bag, sold at list price, 42 times in a quarter. It's not a freak event or a data error. It's a regular, predictable part of the business that happens to sit at the far end of the range.

That matters, because "outlier" often gets treated as a synonym for "mistake to remove". Here, removing the big sales would delete Bisi's most important product.

The median resists outliers; the mean doesn't

Try it on five sales:

One large sale moved the mean by more than six times. The median didn't move at all, because it only cares which value sits in the middle, not how far away the extremes are.

That's the property you're relying on when you report a median: it describes the middle of the data regardless of what the tails are doing.

So which do you report?

Report both, and say which is which. The mean carries information the median doesn't: it's the only one that relates to the total. The median carries information the mean doesn't: what a normal day looks like.

"Bisi's 938 sales in the quarter averaged ₦11,755, but that average is pulled up by a small number of large purchases. The typical sale was ₦4,950, and only 21% of sales were above the average."

Three numbers, one sentence, and no reader is misled.

When the mean alone is right

The mean is exactly what you want when the question is genuinely about the total:

• Revenue per branch per month for a forecast, because you'll multiply it back up.
• Average order value for deciding whether a delivery fee covers its cost.

The rule of thumb: if you are going to multiply the average back out to get a total, use the mean. If you are describing what a person or a day looks like, use the median.

The mode works on text too

MODE needs numbers, but "what happens most often" applies to any column. For text, count them:

=INDEX(Sales[item], MATCH(MAX(COUNTIF(Sales[item], Sales[item])), COUNTIF(Sales[item], Sales[item]), 0))

Or far more simply, sort a COUNTIF table and read the top. For Bisi:

Note that none of these is rice. Bar soap is what Bisi sells most often; rice is what earns her the most money. Two completely different lists, and stocking decisions need both.

The mode for quantity is 1 (327 lines), then 2 (301) and 3 (258). Most people buy one of a thing.

Averaging an average: the mistake to never make

Bisi's three branches have these average line values. What's the shop's overall average?

Averaging those three means gives ₦11,972. The real answer is ₦11,755.

The three-mean average is wrong because it treats Surulere's 140 lines as equal in weight to Yaba's 460. To combine averages you need the totals and the counts:

=SUM(all amounts) / SUM(all lines)

Never average an average. Go back to the underlying rows, every time. This mistake is extremely common and almost never noticed, because the wrong answer always looks reasonable.

Common mistakes

The short version

• Mean = total ÷ count. Median = the middle. Mode = the most common.
• Bisi's lines: mean ₦11,755, median ₦4,950, mode ₦3,900. Only 21.2% of sales are above the mean.
• Money data is right skewed almost always: it can't go below zero but can go far above.
• Outliers aren't automatically errors. Bisi's second most common sale amount is her largest product.
• Report both, and say which is which. Use the mean when you'll multiply it back into a total.
• Never average an average. Weighted properly, Bisi's mean is ₦11,755, not the ₦11,972 you get from averaging her three branch means.

Try it yourself

1. Calculate the mean, median and mode of your amount column. You should get ₦11,755, ₦4,950 and ₦3,900.
2. Use =COUNTIF(Sales[amount_ngn], ">"&AVERAGE(Sales[amount_ngn])) to count sales above the mean. You should get 199, or 21.2%.
3. Find the three most common amounts and identify which product the ₦86,500 one is.
4. Build a COUNTIF table of items and find the three most frequent. Note how different that list is from the revenue ranking in lesson 3.5.
5. Calculate each branch's mean line value, then average those three. Compare with the true overall mean, and write one sentence explaining the gap.
6. Remove the top 10 sales from a copy of your data and recalculate the mean and median. Record how much each one moved.
7. Write the one-sentence summary you'd give Bisi about her average sale.

You'll know you've got it when someone says "the average customer" and you immediately want to know how many customers are actually near that number.