Spreadsheet Practice

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)
Pro11 minutes, 4 exercisesMEDIANQUARTILE.INCPERCENTILE.INCSTDEV

Watch it done

3 steps

Watch 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 fences

Watch 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

  1. 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).
  2. 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.
  3. 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.
  4. 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.

  1. Median and quartiles
  2. IQR and fences
  3. Flag the outliers
  4. Percentile and spread