A typo puts 9,999 into a latency column clustered near 50 ms — what happens to the range, SD and IQR?
answer
- which summaries even look at the extreme value
- squares magnify distance, ranks do not
- the range is two numbers only
- quartiles are positions, not magnitudes
basics
~20 sThe range explodes, since it is fixed by the two extremes. The standard deviation jumps hard, because one enormous squared deviation dominates the sum. The interquartile range barely moves: it reads two percentile positions, never the magnitude beyond them.
solid answer
~50 sTake 1,000 latencies clustered near 50 ms with a standard deviation around 5 ms. A single 9,999 entry moves the range from roughly 30 ms to nearly 10,000 ms, because the range reads only the minimum and the maximum. It drags the mean up by about 10 ms and pushes the standard deviation from about 5 ms to a few hundred milliseconds, because that one point contributes a squared deviation of nearly 10,000 squared — bigger than the entire rest of the sum. The interquartile range stays near 7 ms: the 25th and 75th percentiles move by a fraction of one observation's position, and the magnitude of the extreme value never enters the calculation. The diagnostic value is in the disagreement itself — a standard deviation dozens of times the IQR is a data-quality alarm, not a description of the traffic.
go deeper
Know that the range and standard deviation react strongly to a single extreme value while the interquartile range barely moves, and be able to say why squaring the deviations is what causes the standard deviation to jump.
Explain the mechanism for each measure: the range reads only the minimum and maximum, the standard deviation squares a huge deviation and also shifts the mean, and quartiles depend on rank rather than magnitude.
Demonstrate the diagnostic habit — compute both a mean-based and a percentile-based spread, notice when they disagree by more than the roughly 1.35 factor, and trace the offending rows before deciding anything about them.
Own the policy: which sentinel values are screened at ingestion, who is allowed to exclude records from a published metric, and how a summary that has already been circulated gets corrected once contamination is found.
## The setup A latency column holds 1,000 measurements clustered near 50 ms with a standard deviation of about 5 ms, so realistic values run roughly 35 ms to 65 ms. One row is mistyped as 9,999. Nothing else changes. Each spread measure responds differently, and the differences are the whole lesson. ## The range The range is `maximum - minimum`. It reads exactly two numbers from the dataset and ignores the other 998. Before the typo it is about 30 ms. After, it is roughly 9,964 ms. The range is therefore fully determined by whichever value happens to be most extreme, which makes it the single most fragile summary of spread in common use. It also grows with sample size even on clean data, because a larger draw is more likely to reach further into the tails — so ranges from samples of different sizes are not comparable. ## The standard deviation The standard deviation is built from squared deviations around the mean, so the bad row hurts twice. First it moves the centre. The mean climbs from 50 ms to about 59.9 ms, since one value 9,949 ms above the mean spread across 1,000 rows shifts the average by roughly 10 ms — about two of the original standard deviations. Every deviation in the column is now measured from a centre that no real observation sits near. Second, and far more seriously, the squared deviation of that one point is about 9,939 squared, close to 98.8 million, while the other 999 rows together contribute on the order of 25,000. One row out of a thousand supplies well over 99.9% of the sum of squares. The standard deviation lands somewhere around 300 ms, roughly sixty times the true figure. Squaring is what does this: it is precisely the property that makes far-away points dominate. ## The interquartile range The IQR is `Q3 - Q1`, the distance between the 75th and the 25th percentiles. Both are positional: they depend on the *rank* of values, not their magnitude. Adding one enormous value shifts the position of each quartile by half an observation's worth in a thousand-row column, so the IQR moves by a negligible amount and stays near 7 ms. Notably, the value 9,999 could have been 99,999 or 9,999,999 and the IQR would be unchanged, because past a certain rank the measure stops caring how far out a point lies. For roughly bell-shaped data there is a useful conversion: the IQR is about 1.35 standard deviations. On the clean column, 1.35 times 5 ms gives about 6.7 ms, which matches the observed IQR. That relationship is what makes the disagreement diagnosable. ## Turning the disagreement into a diagnostic After the typo, the standard deviation reads about 300 ms while the IQR reads about 7 ms. On approximately bell-shaped data those two should sit in a ratio near 1.35; here they are apart by a factor in the dozens. That gap is the signal. Any of three things could produce it: contamination such as this typo, a genuinely heavy-tailed distribution, or a mixture of two populations sharing one column. All three are worth knowing about, and none of them is visible from a standard deviation alone. The operational habit that follows is to compute both a mean-based and a percentile-based spread on any column before trusting either. Latency, revenue, session duration and file size are all routinely heavy-tailed, so this disagreement is common even without a typo — the point is not that the standard deviation is wrong, but that reporting it alone hides the shape of the data. ## What to do about it The fix is to treat 9,999 as what it is: a suspect record. Trace it to its source, decide whether it is a data-entry error, a sentinel value, a timeout marker, or a real event, and handle it deliberately with the decision written down. Silently deleting anything large is how genuine incidents disappear from the record. Recompute the summaries after the decision, and if the number has already been published, republish it rather than letting the two versions circulate. A sentinel-value check is worth building in early. Values like 9,999, -1, 0, and 999999 are conventional 'no data' markers in upstream systems, and a single one of them landing in a numeric column will wreck every mean-based summary computed downstream. ## Common mistakes The main error is trusting the standard deviation as a description of the traffic and then explaining the 300 ms figure to stakeholders as real volatility. The second is claiming the IQR is unaffected because it 'removes' extremes: it never sees them in the first place — it is computed from two positions and nothing beyond them enters. The third is dropping the row on sight without diagnosing it, which is how a real outage gets erased along with a typo.
- What does it mean when a column's standard deviation is dozens of times its IQR?It means the tails carry far more weight than a bell-shaped distribution would produce, since for roughly normal data the IQR is about 1.35 standard deviations. Three causes are worth checking in order: contaminated rows such as sentinel values or typos, a genuinely heavy-tailed process like latency or revenue, and two populations mixed into one column. The ratio does not tell you which, but it tells you not to publish the standard deviation unexamined.
- Why is the range a weak measure of spread even on perfectly clean data?Because it reads only two observations and discards everything between them, so it says nothing about how the bulk of the data is distributed. It also grows systematically with sample size — a larger draw reaches further into the tails — which makes ranges from samples of different sizes incomparable. It survives in practice mainly for its cheapness and for quick sanity bounds, not as a serious summary.
- Should you delete the 9,999 row before recomputing the summaries?Not on sight. First trace it to its source and classify it: a data-entry error, an upstream sentinel for missing data, a timeout marker, or a genuine 10-second response. Each calls for a different action, and only the first two justify removal. Record the decision and the rule that produced it, so the same row is handled the same way next month rather than by whoever happens to run the query.
saying these in an interview costs you the question
- Reports the inflated standard deviation as real volatility
- Says the IQR removes extreme values before computing
- Deletes any large value without diagnosing its source
- Believes one bad row in a thousand cannot matter
- Never compares a mean-based spread against a percentile-based one