Level 7 · Outliers in a quantity column

An outlier is a value far from the rest. Sometimes it’s an error (a quantity of 100000 from a finger on the zero key), sometimes it’s real and the most interesting row in the sheet (a business customer ordering 12 knife blocks). Your job is to tell them apart, and to keep one bad value from distorting every summary.

This sheet holds the real kitchen order lines from June 9 to 15, 2025, with one change we made to mirror a classic export slip: line 22524 (row 24) really has a quantity of 1; here it reads 100000. Line 22567 in row 35, with a quantity of −1, is a real data-entry error from the store’s own records.

How one value moves the average

Formula	Result
=AVERAGE(E2:E39)	2,633.74
=MEDIAN(E2:E39)	1.5
=AVERAGEIF(G2:G39,"<>error",E2:E39)	2.31

The average adds every value and divides by 38, so the single 100000 contributes about 2,632 units to it. The median only cares about the middle of the sorted list, and the middle doesn’t move when the top value grows. That is why the report Debra saw was absurd, and why a median is the honest “typical” figure for a column you haven’t cleaned yet.

Rule 1: what is possible

Some values are impossible, whatever the rest of the data says. A line can’t ship fewer than 1 unit. The biggest line in the store’s history is 16 units, so 50 is a generous ceiling. These are business rules, so they live in labelled cells (J1 and J2) where a manager can read and change them:

=OR(E2<$J$1,E2>$J$2)

It is TRUE for row 24 (100000) and row 35 (−1).

Rule 2: what is unusual for this data

A statistical rule needs no business knowledge. The interquartile range (IQR) method takes the middle half of the data and flags anything far outside it:

J4   =QUARTILE.INC(E2:E39,1)      Q1, a quarter of the values are at or below it: 1
J5   =QUARTILE.INC(E2:E39,3)      Q3, three quarters are at or below it: 2
J6   =J5+1.5*(J5-J4)              the upper fence: 2 + 1.5 × 1 = 3.5

The lower fence, Q1 − 1.5 × IQR, is −0.5 here; rule 1 already covers that side. The quartiles come from the middle of the data, so the 100000 barely touches them: with the real quantity of 1 in row 24, Q1 and Q3 are the same.

The z-score is the other common test: =STANDARDIZE(E2,AVERAGE(E2:E39),STDEV.S(E2:E39)), with |z| above 3 called an outlier. It flags the 100000 (z ≈ 6.0) but gives the −1 a z of −0.16, because the typo inflates the standard deviation it is measured against. On a column with one huge error, the quartile rule is the steadier one.

One label per line

G2   =IF(OR(E2<$J$1,E2>$J$2),"error",IF(E2>$J$6,"unusual","ok"))

The order matters: test for errors first, so the 100000 is called an error rather than merely unusual. The column ends with 2 errors, 5 unusual lines (quantities of 4, 8 and 12) and 31 ok. The unusual ones are real orders: look them up before anyone “corrects” them. In Excel, a box plot (Insert > Insert Statistic Chart > Box and Whisker, Excel 2016 and later) draws the same fences as a picture.

Common mistakes

• Deleting outliers. Flag them, check the source, then fix or exclude with a reason. A deleted row can’t be checked.
• Replacing the error with the average. The average includes the error. If you must fill, use the median, and label the row as in level 6.
• Only one rule. The fence alone would call the −1 “ok”. The business rule alone catches both errors but can’t tell you which real orders are unusual.
• Reporting the average of a dirty column. One typo turned 2.31 into 2,633.74.