Lesson 6 of 16 · Level 2: Intermediate
Descriptive Statistics: Mean, Median, Standard Deviation and Percentiles
- ⏱ 13 min read
- ❓ 4 review questions
- ✍️ Exercise with solution
- 📥 Downloadable template
In this lesson
- How to summarize a column with measures of center: mean, median and mode.
- Why the mean can mislead when data is skewed, with a real example where it's three times the median.
- How to measure spread with range, standard deviation and the interquartile range (IQR).
- How to use quartiles and percentiles, and how to flag outliers with the 1.5 × IQR rule.
- The Excel and Google Sheets formulas for every measure, and which version to choose.
All figures come from the 200 orders in Sales Clean. The Summary sheet already calculates the mean, median, standard deviation, minimum, maximum and 90th percentile so you can check your formulas.
- sales-practice-data.xlsx · sheets Sales Clean and Summary
Descriptive statistics are a handful of numbers that summarize a whole column: where the "middle" is, how spread out the values are and which ones are unusual. They're the first thing an analyst calculates on any numeric column, and they're often the first thing an interviewer asks about. In this lesson you'll calculate them on the revenue of our 200 orders and see why reporting "the average order is $448.68" without context would mislead your boss.
Measures of center: mean, median and mode
These three numbers each answer "what's a typical value?" in a different way:
- Mean (average): add all values and divide by how many there are. $89,735 ÷ 200 = $448.68. Excel:
=AVERAGE(J2:J201). - Median: sort the values and take the middle one (with an even count, the average of the two middle values). Here it's $138. Excel:
=MEDIAN(J2:J201). - Mode: the most frequent value. The most common order is $119 (one Air Fryer), which appears 16 times. Excel:
=MODE.SNGL(J2:J201). The mode is most useful for categories ("the most common channel is Online") and for discrete counts (the most common order size is 1 unit).
The mean is more than three times the median. That's a red flag that the data is skewed: a small number of very large values pulls the mean up, while the median doesn't care how big the largest values are. In our data, the 37 wholesale orders (bulk purchases by businesses) are the long tail. You can see it clearly in a histogram:
- Sensitive to outliers
- ●●●●
- Easy to total back
- ●●●●
- Use it for
- Planning totals (mean × number of orders = revenue) and symmetric data.
- Sensitive to outliers
- ●●●●
- Easy to total back
- ●●●●
- Use it for
- Describing a "typical" case in skewed data: order values, incomes, home prices.
Both are correct; they answer different questions. If the CFO asks "how much revenue do 1,000 more orders bring?", use the mean: 1,000 × $448.68 ≈ $448,680 (assuming the same mix). If marketing asks "what does a typical customer spend per order?", the median ($138) is the honest answer. Even better, split the groups: consumer orders (online and retail, 163 orders) have a mean of $173.35 and a median of $119, while the 37 wholesale orders have a mean of $1,661.59 and a median of $1,264.
1. A news story says "the average home price in the county rose to $650,000," but most homes sold for around $400,000. What's the most likely explanation?
Prices, incomes and order values are usually right-skewed: a few huge values raise the mean while the median stays near the typical value. The median isn't always lower; in a symmetric distribution the two are about equal.
Measures of spread: how different are the values?
Two datasets can share the same mean and look nothing alike. Spread tells you how far values typically sit from the center.
Range
Maximum minus minimum: $5,229 − $35 = $5,194. Simple, but it depends on only two values, so one extreme order changes it completely.
Standard deviation
The standard deviation measures the typical distance between each value and the mean. Conceptually: take each value's distance from the mean, square it, average those squares (that's the variance) and take the square root to return to dollars.
Sample standard deviation: s = √( Σ(x − mean)² / (n − 1) )
Excel / Google Sheets: =STDEV.S(J2:J201) → $794.93
Population version: =STDEV.P(J2:J201) → $792.94
Use STDEV.S (sample) when your data is a sample of a bigger process, which is almost always the case in business: 2025's orders are a sample of all the orders the company could get. Use STDEV.P only when you truly have every member of the population. With 200 rows the difference is small; with 10 rows it matters.
A standard deviation of $794.93, bigger than the mean itself, confirms that order values are all over the place. A handy comparison is the coefficient of variation: standard deviation ÷ mean = 794.93 ÷ 448.68 ≈ 1.77. For consumer orders only, it's 163.21 ÷ 173.35 ≈ 0.94: still variable, but much more predictable.
You may have heard that about 68% of values fall within one standard deviation of the mean. That holds for roughly normal (bell-shaped) data. Here, mean ± 1 SD runs from −$346 to $1,244, a range that includes impossible negative orders. For skewed data, describe spread with percentiles and the IQR instead.
2. You're analyzing last quarter's support tickets as a sample to understand typical resolution times. Which formula should you use?
Last quarter's tickets are a sample of the ongoing process, so use the sample standard deviation, STDEV.S, which divides by n − 1 instead of n to avoid underestimating the spread.
Quartiles, percentiles and the IQR
Percentiles split sorted data into hundredths. The 90th percentile is the value below which 90% of observations fall. Quartiles are the 25th, 50th and 75th percentiles:
| Measure | Excel / Google Sheets | Value | Meaning |
|---|---|---|---|
| 10th percentile | =PERCENTILE.INC(J2:J201,0.1) | $57.60 | 10% of orders are below this |
| Q1 (25th) | =QUARTILE.INC(J2:J201,1) | $79 | A quarter of orders are $79 or less |
| Median (50th) | =MEDIAN(J2:J201) | $138 | The middle order |
| Q3 (75th) | =QUARTILE.INC(J2:J201,3) | $398 | Three-quarters are $398 or less |
| 90th percentile | =PERCENTILE.INC(J2:J201,0.9) | $1,181.60 | Only 10% of orders exceed this |
| 95th percentile | =PERCENTILE.INC(J2:J201,0.95) | $1,785.80 | The top 5% starts here |
The interquartile range (IQR) is Q3 − Q1 = $398 − $79 = $319. It's the width of the middle half of the data, and because it ignores the top and bottom quarters, extreme values barely affect it. That makes it the go-to measure of spread for skewed data.
Analysts use percentiles constantly: "95% of pages load in under 2 seconds" (p95 latency), "your salary offer is at the 60th percentile for the role," "the top 10% of customers by spend." Percentiles describe the shape of the data without assuming a bell curve.
Finding outliers with the 1.5 × IQR rule
A common rule flags as outliers any values beyond 1.5 IQRs from the quartiles:
Upper fence = Q3 + 1.5 × IQR = 398 + 1.5 × 319 = $876.50
Lower fence = Q1 − 1.5 × IQR = 79 − 478.50 = −$399.50 (no order can be below it)
Count of outliers: =COUNTIFS(J2:J201,">876.5") → 30 orders
Thirty orders are above $876.50. Twenty-nine of them are wholesale; the other is a retail order of 4 adjustable dumbbells ($1,196) in Texas. Together these 30 orders account for $58,069, or 64.7% of all revenue.
That's the key lesson about outliers: flagged doesn't mean wrong. These aren't errors to delete; they're the most important orders in the business. The right move is to analyze them separately (as you did with consumer vs. wholesale above), not to throw them away.
3. The 90th percentile of order revenue is $1,181.60. Which statement is correct?
A percentile describes the position of a value among the orders, not a share of revenue or an average. 90% of the 200 orders fall at or below $1,181.60; 20 orders are above it.
Putting it together: a one-paragraph summary
Here's how an analyst would describe this column to a manager, using the numbers above:
The 200 orders averaged $448.68, but a typical order is much smaller: the median is $138 and half of all orders fall between $79 and $398. A small group of large wholesale orders creates a long tail up to $5,229; the 30 largest orders (above $876.50) produce 64.7% of revenue. For planning, report consumer and wholesale orders separately.
Three sentences, five numbers, and the reader understands the distribution far better than from the mean alone.
Common mistakes with descriptive statistics
Reporting only the mean.
Report the mean and the median; if they're far apart, explain why.
Deleting outliers to make the numbers "look normal."
Investigate them; analyze them as their own group if they're real.
Averaging averages (e.g., averaging four regional averages).
Recalculate from the raw rows: total revenue ÷ total orders.
Using STDEV.P on a sample.
Default to STDEV.S unless you truly have the whole population.
4. The regional average orders are $572.28 (West), $461.91 (Northeast), $316.68 (South) and $426.22 (Midwest). Their simple average is $444.27. Why isn't that the company's average order?
Averaging the four averages gives each region equal weight, but the West has 64 orders and the Midwest only 32. Weighting properly ($89,735 ÷ 200) gives $448.68.
Practice exercise
The first seven orders of 2025 had these revenues: $498, $1,080, $158, $199, $119, $299 and $138.
- Calculate the mean and the median.
- Remove the $1,080 order (a wholesale order) and recalculate both.
- Which measure changed more, and what does that tell you?
See solution
1. Sum = 498 + 1,080 + 158 + 199 + 119 + 299 + 138 = $2,491. Mean = 2,491 ÷ 7 = $355.86. Sorted: 119, 138, 158, 199, 299, 498, 1,080 → median = $199. (The sample standard deviation, =STDEV.S(), is $345.24.)
2. Without $1,080: sum = $1,411, mean = 1,411 ÷ 6 = $235.17. Sorted: 119, 138, 158, 199, 299, 498 → median = (158 + 199) ÷ 2 = $178.50.
3. The mean dropped by $120.69 (34%); the median dropped by only $20.50 (10%). One extreme value moves the mean a lot and the median very little. That's why the median is the better "typical" value for skewed data.
Summary
- Mean, median and mode describe the center; when the mean is far above the median, the data is right-skewed.
- In our data the mean order is $448.68 but the median is $138, because of 37 large wholesale orders.
- Standard deviation ($794.93 with STDEV.S) measures spread around the mean; use the sample version by default.
- Quartiles, percentiles and the IQR ($319) describe skewed data honestly.
- The 1.5 × IQR rule flags 30 orders above $876.50, which together bring in 64.7% of revenue: flagged doesn't mean wrong.
Next lesson: Pivot Tables for Data Analysis. You'll summarize the 200 orders by region, channel, category and quarter in seconds, without writing a single formula.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.