Excel practice · Data Tools

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

  1. Measure how consistent a production run is
  2. Measure how volatile monthly sales have been
  3. Find the score a sales rep needs to hit for the top 10% bonus
  4. Find the median response time using PERCENTILE
  5. Check how strongly marketing spend relates to sales

The exercises

1. Measure how consistent a production run is Medium

Six parts from a production run were measured in millimeters. Find the standard deviation to see how consistent the batch is.

ABCD
1PartMeasurement (mm)
2150.2?
3249.8
4350.5
5449.6
6550.1
7650.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.

2. Measure how volatile monthly sales have been Medium

Find the standard deviation of monthly sales over the past 7 months to see how volatile the business has been.

ABCD
1MonthSales
2Jan42000?
3Feb38500
4Mar51000
5Apr29000
6May47500
7Jun44000
8Jul41000

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.

3. Find the score a sales rep needs to hit for the top 10% bonus Medium

A bonus goes to the top 10% of sales reps by score. Find the cutoff score, the 90th percentile, from this quarter's scores.

ABCD
1Score 90th percentile cutoff
261?
374
488
592
670
785
895
968
1079
1190

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.

4. Find the median response time using PERCENTILE Easy

Find the median (the 50th percentile) of these support ticket response times, in hours.

ABCD
1Median response time (hrs)
23?
37
45
52
69

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.

5. Check how strongly marketing spend relates to sales Medium

Find the correlation between monthly marketing spend and monthly sales to see how strongly the two move together.

ABCD
1MonthMarketing spendSales
2Jan200041000?
3Feb250046000
4Mar180038000
5Apr300052000
6May220043000

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 →

More data tools practice

All Excel practice exercises · Excel test prep