"Averages: mean, median and mode"

01The problem

Ashgrove Chambers' managing partner asks two questions about the firm's invoices:

"What's a typical invoice worth? And how long do clients usually take to pay?"

"Typical" sounds simple, but there are three different averages, and on real data they often disagree. Pick the wrong one and the partner sets fee targets or cash-flow plans on a number that describes almost nobody. This lesson is about choosing.

02The concept

The three averages

With an even number of values, the median is the average of the middle two.

Why they disagree: skew

The mean uses every value's size, so a few very large values pull it up. The median only cares about order, so it barely moves.

• Right-skewed data (a long tail of high values) has mean > median. Salaries, invoice amounts and order sizes are almost always like this.
• Left-skewed data (a tail of low values) has mean < median: for example exam scores where most people did well.
• Roughly symmetric data has mean ≈ median.

So the gap between mean and median is itself useful information: it tells you the data is skewed and which way.

Choosing

• Asking "what does a typical one look like?" Use the median for skewed data.
• Asking "what's the total worth, or the per-unit cost?" Use the mean: mean × count = total, which is exactly what budgets need. The median can't be multiplied back up.
• Asking "what's the most common choice?" Use the mode.

When in doubt, report both, with a sentence explaining the gap.

Averages for groups

AVERAGEIFS(average_range, criteria_range, criteria, …) averages only the rows that meet conditions. There's no MEDIANIFS, but =MEDIAN(IF(criteria_range = "x", values)) does the same job (press Ctrl + Shift + Enter in older Excel), and in Google Sheets you can wrap a FILTER: =MEDIAN(FILTER(values, criteria_range = "x")).

A middle way: the trimmed mean

=TRIMMEAN(range, 0.1) drops the top and bottom 5% (10% in total) and averages the rest. It keeps most of the data's information while ignoring extremes, and it's used for things like judges' scores and some inflation measures.

03Example

The partner's two questions, on the legal dataset's invoices.csv (amount in column D, rows 2 to 411):

=AVERAGE(D2:D411)     2,931,098
=MEDIAN(D2:D411)      2,655,000

The mean invoice is about ₦2.93m, the median ₦2.66m: right-skewed, with some large invoices pulling the mean up. "A typical invoice is about ₦2.7m" is the honest answer; "₦2.9m" would overstate it. But for budgeting (expected billing next year from roughly 400 invoices), the mean is the right number: 410 × ₦2,931,098 is the actual total billed.

For days to pay, add a column days_to_pay = paid_date - issued_date (format it as a number). Unpaid invoices have no paid date, so the cell is empty or an error. AVERAGE and MEDIAN skip blanks, which is exactly right: you can't include a payment that hasn't happened. Then:

=AVERAGE(days_to_pay)    45.4
=MEDIAN(days_to_pay)     46

Here mean and median are almost equal, so payment times are roughly symmetric: "clients usually pay in about 46 days" is fair.

04Walkthrough

1. Download the legal dataset and open invoices.csv. Make it a Table (Ctrl + T).
2. Calculate the mean and median amount_ngn. Note which is larger and what that says about skew.
3. Add days_to_pay: =IF([@[paid_date]]="", "", [@[paid_date]]-[@[issued_date]]), formatted as a whole number. The IF leaves unpaid invoices blank.
4. Calculate the mean and median of days_to_pay.
5. Open the HR dataset's employees.csv and calculate the mean, median and mode of monthly_salary. Then =TRIMMEAN(G2:G81, 0.1). Where does the trimmed mean fall between the other two?
6. Use AVERAGEIFS and MEDIAN(IF(…)) to compare mean and median salary in Operations. The gap is unusually large: what does it tell you about pay in that department?

05Practice

06Challenge

07More practice

Optional drills on the HR data.

08Check your understanding