Coefficient of variation: spread relative to size
STDEV divided by AVERAGE lets you compare things measured on different scales.
Task:
Is weekly demand steadier for screws (thousands a week) or for drills (dozens a week)? In B9 and C9 give each product's standard deviation divided by its average.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of STDEV, VAR
0/3The same formula in the other shapes it takes at work.
- STDEV Function
The same spread as VAR, back in units you can read.
CoreAdvanced - Coefficient of variation: spread relative to size
STDEV divided by AVERAGE lets you compare things measured on different scales.
Advanced - Flag values more than two standard deviations out
A simple, defensible outlier rule built from AVERAGE and STDEV.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Week | Screws | Drills |
| 2 | W1 | 4200 | 18 |
| 3 | W2 | 3900 | 31 |
| 4 | W3 | 4500 | 12 |
| 5 | W4 | 4100 | 25 |
| 6 | W5 | 4300 | 14 |
| 7 | |||
| 8 | |||
| 9 | Std dev ÷ average |
What this exercise teachesMay contain the answer
Screws swing by about 220 units a week and drills by about 8, which makes screws look far less stable. Relative to their averages, though, screws vary by about 5% and drills by about 40%. The coefficient of variation is the fair comparison whenever the scales differ.