Percentiles, the IQR and the 1.5 Outlier Rule: How to Flag a Bad Row

1. What a percentile is, in counting terms

Before the definition: look at the sorted row above and find the value with a quarter of the data below it. Say which one you picked before reading on.

A percentile is a value with a stated share of the data at or below it. The 25th percentile has a quarter of the values below it, the 90th has nine tenths below it, and the 50th is the median. That is the whole idea, and notice what it is built from: positions in a sorted list, not sizes. The 90th percentile does not care whether the top value is 1,100 or 1,100,000. It only cares that it is at the top.

The word quantile covers the same thing in other slices. Quartiles cut the data into four parts, deciles into ten, percentiles into a hundred. They are the same operation with different cut counts, and every tool implements them with the same function.

This is the reason percentiles are the natural language for anything with a long tail. "Our average page loads in 400 milliseconds" hides the customers waiting four seconds. "Our 95th percentile page loads in 3.9 seconds" is a statement about those specific customers, and it does not move at all when one absurd 60-second reading arrives, because that reading is still just one position at the top.

2. Quartiles by hand on sixteen orders

Before the arithmetic: sixteen values and you want the 25th percentile. A quarter of sixteen is four. Decide whether the answer is the 4th value, and what you would do if you are not sure.

It is not quite the 4th value, and the reason is worth having straight. The commonest method, the one Excel's PERCENTILE.INC, pandas and numpy all use by default, puts the 0th percentile on the first value and the 100th on the last, then spreads the rest evenly between them. The position it wants is:

position = 1 + p × (n − 1)

With p as a fraction and n = 16, that gives 1 + 0.25 × 15 = 4.75. There is no 4.75th value, so it takes three quarters of the way from the 4th to the 5th. Both of those are 440, so the answer is 440 with no interpolation needed.

Do the 75th the same way: 1 + 0.75 × 15 = 12.25. The 12th value is 765 and the 13th is 850, and a quarter of the way between them is 765 + 0.25 × 85 = 786.25. That decimal is the interpolation showing its working. It is a real answer, and it is not any order's revenue.

Here is the whole set, computed the same way.

-- SQL
SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Revenue) AS q1,
       PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY Revenue) AS median,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Revenue) AS q3
FROM Orders;

# pandas
df['Revenue'].quantile([0.25, 0.5, 0.75])
# 0.25    440.00
# 0.50    630.00
# 0.75    786.25

' Excel
=QUARTILE.INC(Orders[Revenue], 1)    ' 440
=QUARTILE.INC(Orders[Revenue], 3)    ' 786.25
=PERCENTILE.INC(Orders[Revenue], 0.9) ' 880

3. The IQR, and what the middle half tells you

Before the definition: you have Q1 at 440 and Q3 at 786.25. Say in one sentence what the gap between them describes, before reading on.

It describes the width of the middle half of your orders. The interquartile range, or IQR, is simply Q3 minus Q1: 786.25 − 440 = 346.25. Half of all orders fall inside a 346-wide band, and that band runs from 440 to 786.

Compare that with the standard deviation's answer to the same question. Both describe spread and both are in dollars, but they are built from different things. The standard deviation squares every distance from the mean, so every value votes and the far ones vote loudest. The IQR asks two positions in the sorted list where they are, and ignores everything else entirely.

Section five puts a number on that last column. For now, notice that the IQR is larger than the standard deviation here, which surprises people who expect them to be interchangeable. They are not on the same scale and there is no reason they should match. On perfectly bell-shaped data the IQR runs about 1.35 times the standard deviation, and 346.25 ÷ 236.81 = 1.46, which is close enough to say this data is not far from that shape.

4. The 1.5 fence, and why clean data flags nothing

Before the rule: you have a middle half that is 346.25 wide. Somebody has to draw a line past which a value counts as unusual. Decide roughly how far beyond the box you would draw it, before you see the standard answer.

The standard answer is one and a half box-widths, and it goes on both sides.

lower fence = Q1 − 1.5 × IQR = 440.00   − 1.5 × 346.25 = −79.375
upper fence = Q3 + 1.5 × IQR = 786.25   + 1.5 × 346.25 = 1305.625

Now run it. Our largest order is 1,100 and the upper fence is 1,305.63, so nothing is flagged. Our smallest is 240 and the lower fence is below zero, so nothing is flagged there either. This clean table has no outliers, and that is the correct and useful answer.

Say out loud why a lower fence of −79.375 is not a mistake. Revenue cannot be negative, so the rule is telling you that no order is unusually small, because there is not enough room below the box for one to be. A fence that falls outside the possible range of the data simply never fires, and that is fine.

There is a second, wider fence at 3 × IQR, which on this data sits at 1,825. Values past the 1.5 fence are conventionally called mild outliers and values past the 3 fence extreme ones. In practice I use the 1.5 fence to build a list to read and the 3 fence to decide what to escalate.

-- The whole rule as one query
WITH q AS (
  SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Revenue) AS q1,
         PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Revenue) AS q3
  FROM Orders
)
SELECT o.*
FROM Orders o CROSS JOIN q
WHERE o.Revenue < q.q1 - 1.5 * (q.q3 - q.q1)
   OR o.Revenue > q.q3 + 1.5 * (q.q3 - q.q1);
-- 0 rows

5. The typo: what the IQR does that the standard deviation cannot

Before the numbers: somebody keys order 1013 as 150 lamps instead of 15, so its revenue reads 6,000 instead of 600. Predict what happens to the IQR, and separately to the standard deviation.

Here is what actually happens.

The standard deviation nearly sextupled. The IQR moved by a fifth, and only because one value shifting to the top pushed everything below it up a position. The fence moved from 1,305.63 to 1,483.75, and 6,000 is comfortably past both, so the rule flags exactly one row and it is the right one. The 1,100 desk order stays inside, correctly, because it is a real order.

Here is the failure this avoids. The obvious way to find unusual values is a z-score: how many standard deviations from the mean. Run that on the typo data and 6,000 scores 3.69, which is over the usual threshold but not dramatically. Run it against the clean standard deviation and the same value scores 22.7. The gap between 3.69 and 22.7 is the problem in one line: the bad row inflated the standard deviation it is then measured against, so it partly hides itself. The more extreme the error, the more effectively it does this.

The IQR cannot be gamed that way, because a value at the top of the sorted list contributes its position and nothing else. That is the entire reason the standard outlier rule is built from quartiles rather than from the mean and standard deviation.

If you would rather stay in z-score language, there is a robust version. Replace the mean with the median and the standard deviation with the median absolute deviation, the median of every value's distance from the median. Here that is 220, and the modified score for our 6,000 row is 16.4 rather than 3.69. Same idea, same units, but built from positions.

Now picture your own largest table and the one numeric column everything depends on. If a row in it were keyed ten times too large tomorrow, which of these two rules would notice?

6. Why Excel, SQL and pandas give different percentiles

Before the problem: you compute the 90th percentile of these sixteen orders in three tools and get 880, 880 and 946. Decide whether one of them is broken, before reading on.

None of them is. There is no single agreed definition of a sample percentile, and the common statistical packages implement several. The differences show up on small data sets and vanish on large ones, which is exactly the wrong way round for catching them, because you develop on small data.

Three families, and what each does on our sixteen orders.

The nearest-rank family always returns a value that is actually in your data, which is a real advantage when the number will be shown to somebody as an example row. The interpolating family returns a smoother estimate that usually is not in your data. The excluded-endpoints version assumes your sample came from a larger population and pushes the extremes outward accordingly, which is why its 90th percentile of 946 sits above every value except 1,100.

The practical rule is short. Pick one method, write down which one, and use it everywhere in a piece of work. Do not compute Q1 in Excel and Q3 in SQL. And if a percentile ever has to reconcile against somebody else's number, ask which function they used before assuming anybody made a mistake.

7. What to do with an outlier once you have found one

Before the options: the fence has handed you one row. List what you could do about it before reading my list.

There are four honest options and one dishonest one.

Fix it, if it is an error you can correct. The 150-lamp row was meant to be 15. Correct the source, not the copy, and note the correction.

Exclude it and say so, if it is an error you cannot correct. A row you cannot verify is a row you can drop, but the count and the reason go in the write-up. "Fifteen of sixteen orders, one excluded as an unverifiable quantity" is a complete sentence and it is the whole cost. Documenting data limitations covers how to phrase this so it does not read as hedging.

Keep it and report the median instead of the mean, if it is real. A genuinely enormous order is not an error and deleting it is falsifying your data. Switch to statistics that tolerate it, which is the argument in mean vs median.

Split it out, if there are several and they share a story. Six orders ten times the size of the rest are not outliers, they are a second population, probably wholesale against retail. Report the two groups separately and the noise disappears.

The dishonest option is deleting it quietly because the chart looks better. It happens most often in the last hour before a deadline, and the reason it is dishonest rather than merely wrong is that the reader cannot tell it happened. An outlier rule is a tool for finding rows to think about, and it never decides anything on its own.

Edge cases that catch people out

Six that each cost somebody an afternoon.

The fence flags nothing and you conclude the data is clean. It is not the same claim. The rule catches values far from the middle half. A date typed as 2062 instead of 2026, a region spelled two ways, a row duplicated exactly: none of those move a revenue percentile at all.

The fence flags a tenth of your rows. That means the shape is skewed rather than that a tenth of your data is broken. On heavily right-tailed columns like income or session length the 1.5 rule fires constantly by design. Either work on the log of the column or accept that the top tail is the data, not an error.

Quartiles on categories. A percentile needs an order. It works on numbers and on dates, and it does not work on region names. Sorting alphabetically and taking the 25th percentile of "East, North, South, West" produces a value and no meaning.

Percentile of a percentile. The 90th percentile of your ten regional 90th percentiles is not the 90th percentile of the underlying rows, and there is no way to combine them that gets you there. Percentiles must be computed on the raw data every time, which is why they are expensive on large tables and why pre-aggregated dashboards so often quietly get them wrong.

Two rows either side of the fence. A value at 1,483.74 and a value at 1,483.76 are treated as completely different by the rule and are identical in reality. The fence is a triage tool, not a verdict, and rows just inside it deserve the same look as rows just outside.

Percentiles on tiny groups. A 90th percentile of three values is the second-largest of them with extra decimal places. Print the count next to any percentile computed per group, the same discipline GROUP BY and HAVING asks for.