Lesson 37 of 45, Analysis
Median, quartiles & outliers
Quick answer
Describe a column of numbers honestly, and flag the values that do not belong.
- Key formula
=MEDIAN(B2:B13)(The middle value)
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
The average is pulled around by extreme values; the median, the middle value when sorted, is not. When the two disagree, you probably have outliers.
QUARTILE.INC(range, 1) is the value a quarter of the way up the sorted list, and QUARTILE.INC(range, 3) is three quarters. The gap between them is the IQR, where the middle half of the data sits. PERCENTILE.INC(range, 0.9) gives any cut-off you want, such as the 90th percentile.
A common rule flags an outlier as anything below Q1 - 1.5 × IQR or above Q3 + 1.5 × IQR. STDEV measures how spread out the values are around the average.
Syntax
=MEDIAN(B2:B13)The middle value=QUARTILE.INC(B2:B13,1)First quartile (Q1)=PERCENTILE.INC(B2:B13,0.9)90th percentile=G4-G3IQR: Q3 minus Q1=IF(OR(B2<$G$6,B2>$G$7),"Outlier","")Flag values outside the fencesWatch out for
- Quartile numbers are 0 to 4. QUARTILE.INC(range,2) is the median; 1 and 3 are the quartiles.
- Lock the fence cells with $ when you flag a whole column against them.
- An outlier is a prompt to look, not a reason to delete. Check whether it is an error or a real event first.
The exercises
- Median and quartiles. These are delivery times in minutes. In G2, find the median. In G3, the first quartile (Q1). In G4, the third quartile (Q3).
- IQR and fences. In G5, calculate the IQR (Q3 minus Q1). In G6, the lower fence: Q1 minus 1.5 times the IQR. In G7, the upper fence: Q3 plus 1.5 times the IQR. Point at the cells above.
- Flag the outliers. In C2, show Outlier when the minutes are below the lower fence or above the upper fence (G6 and G7); otherwise leave it blank (""). Fill down to C13.
- Percentile and spread. In G8, find the 90th percentile of the minutes. In G9, find the standard deviation.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Median and quartiles
- IQR and fences
- Flag the outliers
- Percentile and spread