6. Percentiles, Quartiles, and Outliers

The average answers "how much on average", but it says nothing about how the values are spread out. For that you use percentiles: the k-th percentile is the value below which k% of observations fall. If the 90th percentile of delivery time is 50 minutes, then 9 out of 10 orders arrive in under 50 minutes. Delivery companies and IT monitoring teams love metrics like p90 and p95.

• PERCENTILE.INC(array, k): k runs from 0 to 1 inclusive, where 0 is the minimum and 1 is the maximum;
• QUARTILE.INC(array, quart): special cases. 1 is the 25th percentile (Q1), 2 is the median, 3 is the 75th (Q3);
• PERCENTILE.EXC and QUARTILE.EXC use a different formula (0 and 1 are not allowed) and give slightly different numbers on small samples. The key is to use the same variant throughout a report.

The interquartile range IQR = Q3 - Q1 is the width of the "middle" of the data, where half of the observations lie. Tukey's rule calls anything below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR an outlier. Quartiles barely react to outliers, so the rule is reliable.

The second method is the z-score: how many standard deviations a value is from the mean. STANDARDIZE(x, mean, standard_dev) = (x - mean) / standard deviation. Values with |z| above 2 to 3 are considered unusual. The weak spot: outliers inflate the mean and the standard deviation and so "hide" themselves. Compare both methods in this lesson.