Statistical
Coefficient of variation (CV) formula: compare relative volatility across scales
Divide STDEV.S by AVERAGE to measure unit-free relative dispersion across products, portfolios, or sensors with different price scales.
Why raw standard deviation misleads across different magnitudes
A $10 item swinging ±$2 has a sample standard deviation of $1.63, while a $1,000 server swinging ±$10 has a standard deviation of $8.16—five times larger in raw dollars, yet 20 times steadier relative to its mean!
The Coefficient of Variation (CV = Standard Deviation / Mean) removes units and scale: $1.63 / $10 = 16.33% CV on PennySku versus $8.16 / $1,000 = 0.82% CV on ServerSku.
CV becomes unstable when the mean approaches zero. STDEV.S needs at least two numeric observations. A price-level CV is not the same as volatility of investment returns.
| Period | PennySku (A) | ServerSku (B) |
|---|---|---|
| Q1 | 8 | 990 |
| Q2 | 10 | 1000 |
| Q3 | 12 | 1010 |
| Q4 | 10 | 1000 |
Sample CV (STDEV.S) vs Population CV (STDEV.P)
Use STDEV.S(range) / AVERAGE(range) when periods represent a sample of ongoing demand, or STDEV.P when analyzing a complete closed population. Interpret CV on a ratio scale with a meaningful zero and a positive mean; zero observations can occur.
=ROUND(STDEV.S(A2:A5) / AVERAGE(A2:A5), 4)Compare ServerSku CV and guard non-positive means
Guarding AVERAGE(range) <= 0 avoids #DIV/0! and meaningless negative CV ratios.
=ROUND(STDEV.S(B2:B5) / AVERAGE(B2:B5), 4)Try the example
Use the original values or change them to test your understanding.