STDEV, PERCENTILE & CORREL practice exercises
5 graded problems with realistic data, from easy to medium.
Three more statistics functions: how spread out a dataset is, the value at a given percentile, and how strongly two sets of numbers move together. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the STDEV, PERCENTILE & CORREL guide first: syntax, a worked example and the mistakes people make.
In this set
- Measure how consistent a production run is
- Measure how volatile monthly sales have been
- Find the score a sales rep needs to hit for the top 10% bonus
- Find the median response time using PERCENTILE
- Check how strongly marketing spend relates to sales
The exercises
Six parts from a production run were measured in millimeters. Find the standard deviation to see how consistent the batch is.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Measurement (mm) | ||
| 2 | 1 | 50.2 | ? | |
| 3 | 2 | 49.8 | ||
| 4 | 3 | 50.5 | ||
| 5 | 4 | 49.6 | ||
| 6 | 5 | 50.1 | ||
| 7 | 6 | 50.3 |
Enter a formula in D2.
Show a hint
STDEV(number1, number2, ...) measures how spread out a set of values is around its average: =STDEV(B2:B7).
A low standard deviation means a production run is consistent; a high one means something is drifting: this is the core metric behind quality control.
Find the standard deviation of monthly sales over the past 7 months to see how volatile the business has been.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | ||
| 2 | Jan | 42000 | ? | |
| 3 | Feb | 38500 | ||
| 4 | Mar | 51000 | ||
| 5 | Apr | 29000 | ||
| 6 | May | 47500 | ||
| 7 | Jun | 44000 | ||
| 8 | Jul | 41000 |
Enter a formula in D2.
Show a hint
Same formula as any STDEV problem, just on a different dataset: =STDEV(B2:B8).
Two businesses can have the same average sales and very different risk profiles: standard deviation is what actually captures that difference.
A bonus goes to the top 10% of sales reps by score. Find the cutoff score, the 90th percentile, from this quarter's scores.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Score 90th percentile cutoff | |||
| 2 | 61 | ? | ||
| 3 | 74 | |||
| 4 | 88 | |||
| 5 | 92 | |||
| 6 | 70 | |||
| 7 | 85 | |||
| 8 | 95 | |||
| 9 | 68 | |||
| 10 | 79 | |||
| 11 | 90 |
Enter a formula in D2.
Show a hint
PERCENTILE(array, k) finds the value below which k percent of the data falls, with k written as a decimal (0.9 for the 90th percentile): =PERCENTILE(B2:B11,0.9).
Top 10%, bottom quartile, median: these are all percentile cutoffs, and PERCENTILE is the one formula that answers all of them, just with a different k.
Find the median (the 50th percentile) of these support ticket response times, in hours.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Median response time (hrs) | |||
| 2 | 3 | ? | ||
| 3 | 7 | |||
| 4 | 5 | |||
| 5 | 2 | |||
| 6 | 9 |
Enter a formula in D2.
Show a hint
The 50th percentile (k=0.5) is the median: =PERCENTILE(B2:B6,0.5).
MEDIAN and PERCENTILE(array,0.5) always agree; this is a good way to see that percentiles are a generalization of the median, not a separate idea.
Find the correlation between monthly marketing spend and monthly sales to see how strongly the two move together.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Marketing spend | Sales | |
| 2 | Jan | 2000 | 41000 | ? |
| 3 | Feb | 2500 | 46000 | |
| 4 | Mar | 1800 | 38000 | |
| 5 | Apr | 3000 | 52000 | |
| 6 | May | 2200 | 43000 |
Enter a formula in D2.
Show a hint
CORREL(array1, array2) returns a number from -1 to 1 showing how closely two sets of numbers move together: =CORREL(B2:B6,C2:C6).
A correlation close to 1 means spend and sales move together almost in lockstep; this is the first real check before anyone claims marketing spend is actually driving sales.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the STDEV, PERCENTILE & CORREL exercises →