Lesson 2: Mean, Median and Mode and When Each Misleads

Why "the average" needs a second look

When a manager asks for "the average salary" or "the typical order", they want one number that stands for the whole column. Statisticians call this a measure of centre. There are three common ones, and each can mislead in its own way:

• Mean: add the values up and divide by how many there are.
• Median: the middle value once the values are sorted.
• Mode: the value that occurs most often.

In Level 1 you sorted columns into nominal, ordinal and numeric data. That sorting now tells you which measure you can use.

Task 1: Set up the dataset

Open a new sheet. In cells A1:A10 type the heading Salary_k and then these annual salaries (in £ thousands) for a small team of nine:

28, 30, 31, 32, 34, 35, 36, 38, 250

The last value belongs to the managing director. In B1:B10 type the heading Dept and then: Sales, Sales, Ops, Sales, Ops, IT, Sales, IT, Board.

Task 2: Calculate the mean

The formula is:

x̄ = Σx / n

• x̄ ("x-bar") is the mean.
• Σx ("sigma x") is the sum of all the values.
• n is how many values there are.

Do this:

1. In D2 type =AVERAGE(A2:A10).
2. Check it by hand: Σx = 514 and n = 9, so x̄ = 514 / 9 ≈ 57.1.

Note: AVERAGE skips empty cells and text but counts zeros. A blank cell and a 0 are not the same thing.

Task 3: Calculate the median

To find the median by hand:

1. Sort the values from smallest to largest.
2. Count them (n).
3. If n is odd, take the middle value. If n is even, take the mean of the two middle values.

Our nine salaries are already sorted, so the 5th value is the median: 34. In D3 type =MEDIAN(A2:A10) to confirm it. You do not need to sort the data first, because MEDIAN does that for you.

Task 4: Calculate the mode

1. In D4 type =MODE.SNGL(A2:A10). It returns #N/A, because no salary appears more than once. #N/A here means there is no mode. Nothing is wrong with your formula.
2. Now find the mode of Dept. MODE.SNGL ignores text, so use a count instead. In E2 type =COUNTIF(B$2:B$10,B2), fill it down, and look for the largest count. Sales appears 4 times, so Sales is the mode.

If several values tie for most frequent, MODE.SNGL returns only one of them. MODE.MULT returns all of them.

Python option

import pandas as pd

df = pd.DataFrame({
    "salary_k": [28, 30, 31, 32, 34, 35, 36, 38, 250],
    "dept": ["Sales", "Sales", "Ops", "Sales", "Ops", "IT", "Sales", "IT", "Board"],
})
print(df["salary_k"].mean())    # 57.11...
print(df["salary_k"].median())  # 34.0
print(df["dept"].mode())        # 0    Sales

.mode() returns a Series that lists every tied mode, so it can hold more than one value. .mean() and .median() skip missing values (NaN) by default.

Task 5: See one value pull the mean

Compare your results:

Do this: delete the 250 and watch D2 and D3 change. The mean moves by about £24k. The median moves by £1k.

The mean uses the size of every value, so one very large salary drags it upward. The median only cares about position, so the 250 counts as "the biggest value" no matter how big it is. A mean of £57k describes nobody in the team: eight of the nine people earn £38k or less.

This is what happens in a skewed column, where there is a long tail of large values on one side. Salaries, house prices and order values are usually skewed to the right. In that case the mean sits above the median, and the median is closer to what a typical person sees.

Task 6: Watch out for mixed groups

Suppose a shift log holds part-time shifts of 4 hours and full-time shifts of 8 hours, half of each. The mean and the median both come out at about 6 hours, yet no one works 6 hours. The column holds two groups, so it has two peaks. A single centre value hides that.

Do this: before you summarise a column, ask whether it mixes groups (part-time and full-time, new and returning customers, UK and US orders). If it does, work out the centre for each group separately.

Task 7: Choose the right measure

Use this checklist:

• Nominal data (department, product colour): use the mode only. A "mean department" makes no sense.
• Ordinal data (ratings from 1 to 5, T-shirt sizes): use the median or the mode. The gaps between ranks are not guaranteed to be equal.
• Numeric data, roughly symmetric (delivery times with no extreme values): use the mean. When the data is symmetric, the mean and median are close anyway.
• Numeric data, skewed or with extreme values (salaries, order values): use the median, or report both.
• Mixed groups: split the data first, then summarise each group.

Justify your choice in one sentence, for example: "I report the median salary because one very high salary pulls the mean far above what most staff earn."

Your question to answer

Add a tenth salary of 33 and recalculate. What is the new median, and roughly how far is the mean from it?

How to check: with 10 values, the median is the mean of the 5th and 6th sorted values. Sorted, those are 33 and 34, so the median = 33.5. The mean = 547 / 10 = 54.7, which is still more than £20k above the median. Your =MEDIAN() and =AVERAGE() cells should show the same numbers.

Key takeaways

• Mean = Σx / n. It uses every value's size, so extreme values pull it.
• The median is the middle sorted value. With an even n, it is the mean of the two middle values. It resists extreme values.
• The mode is the most frequent value. It is the only centre measure for nominal data. MODE.SNGL returns #N/A when nothing repeats.
• In right-skewed data such as salaries, the mean sits above the median.
• Mixed groups can give a centre value that describes nobody, so split them first.
• Choose the measure by data type and shape, and state your reason in one sentence.