STDEV / PERCENTILE / CORREL
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.
STDEV measures how far values typically sit from the average: a small STDEV means a tightly clustered dataset, a large one means wide variation even alongside the exact same average, which is why an average alone can hide a genuinely unreliable process. PERCENTILE(array,0.5) gives the same answer as MEDIAN, since the median is exactly the 50th percentile; it's the function to reach for whenever a cutoff other than the middle is actually needed, like "the bonus threshold is the top 10%." CORREL returns a number from -1 to 1: close to 1 means the two sets move up and down together, close to -1 means they move in opposite directions, and close to 0 means little relationship at all. A strong correlation is not proof that one thing causes the other.
Syntax
| Argument | Description |
|---|---|
| number1, ... | STDEV: Required. The values, cells, or ranges to measure the spread of. |
| array, k | PERCENTILE: Required. The dataset, and which percentile to return (0 to 1, e.g. 0.9 for the 90th percentile). |
| array1, array2 | CORREL: Required. Two equal-sized ranges to measure the relationship between. |
A real example: setting a bonus threshold at the top 10%
A sales dataset where the bonus cutoff is defined as the top 10% of results, not simply the single highest number:
The k argument is a fraction from 0 to 1, not a percentage out of 100: 0.9 for the 90th percentile, not 90, which is the detail that trips up this formula most often on a first attempt.
Worked examples
| A | |
|---|---|
| 1 | Reading |
| 2 | 10.1 |
| 3 | 9.9 |
| 4 | 10.3 |
| 5 | 9.8 |
| A | |
|---|---|
| 1 | Sales |
| 2 | 100 |
| 3 | 150 |
| 4 | 200 |
| 5 | 250 |
| 6 | 300 |
| A | B | |
|---|---|---|
| 1 | Ad spend | Sales |
| 2 | 1000 | 12000 |
| 3 | 1500 | 15000 |
| 4 | 2000 | 19000 |
Common mistakes
- Reading a small average alongside a large STDEV and assuming the data is reliable. The average alone hides how spread out the real values are.
- Confusing PERCENTILE's k argument with a percentage out of 100. It's a fraction from 0 to 1: 0.9, not 90, for the 90th percentile.
- Treating a strong CORREL result as proof of cause and effect. It only shows the two sets tend to move together, not that one is causing the other.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice STDEV / PERCENTILE / CORREL →