JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,525
- Office Version
- 365
- Platform
- Windows
The stdev.p function returns the std dev for a population, whereas the stdev.s does so for a sample, presumably less than the entire population.
These 2 pages from Microsoft appear identical except for the denominator. It's n for stdev.p and n-1 for stdev.s, so stdev.p will always be slightly larger than stdev.s.
But it doesn't say anything about when to use one or the other.
Based on this example, where I am using the std dev to calculate z scores, it doesn't really matter which I use. The z scores based on stdev.s are slightly more spread out, but the order doesn't change. The raw data is prices and Amazon-style 5-star ratings. If these were selected products, they would clearly be a sample, not the entire population.
These 2 pages from Microsoft appear identical except for the denominator. It's n for stdev.p and n-1 for stdev.s, so stdev.p will always be slightly larger than stdev.s.
But it doesn't say anything about when to use one or the other.
Based on this example, where I am using the std dev to calculate z scores, it doesn't really matter which I use. The z scores based on stdev.s are slightly more spread out, but the order doesn't change. The raw data is prices and Amazon-style 5-star ratings. If these were selected products, they would clearly be a sample, not the entire population.
| Std Dev & Variance.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | |||
| 2 | Mean | $26.053 | $26.053 | 4.547 | 4.547 | ||||||||
| 3 | StdDev.P | 10.726 | 0.311 | ||||||||||
| 4 | StdDev.S | 10.362 | 0.301 | ||||||||||
| 5 | |||||||||||||
| 6 | Price | Price Z(P) | Price Z(S) | Rtg | Rtg Z(P) | Rtg Z(S) | Sum Z(P) | Sum Z(S) | Rank X(P) | Rank X(S) | Δ | ||
| 7 | $13.95 | +1.128 | +1.168 | 4.6 | +0.171 | +0.177 | +1.300 | +1.345 | 1 | 1 | =0 | ||
| 8 | $17.99 | +0.752 | +0.778 | 4.7 | +0.492 | +0.510 | +1.244 | +1.288 | 2 | 2 | =0 | ||
| 9 | $18.00 | +0.751 | +0.777 | 4.7 | +0.492 | +0.510 | +1.243 | +1.287 | 3 | 3 | =0 | ||
| 10 | $18.01 | +0.750 | +0.776 | 4.7 | +0.492 | +0.510 | +1.242 | +1.286 | 4 | 4 | =0 | ||
| 11 | $23.99 | +0.192 | +0.199 | 4.8 | +0.814 | +0.842 | +1.006 | +1.041 | 5 | 5 | =0 | ||
| 12 | $21.95 | +0.383 | +0.396 | 4.7 | +0.492 | +0.510 | +0.875 | +0.906 | 6 | 6 | =0 | ||
| 13 | $25.00 | +0.098 | +0.102 | 4.6 | +0.171 | +0.177 | +0.269 | +0.279 | 7 | 7 | =0 | ||
| 14 | $39.95 | -1.296 | -1.341 | 5.0 | +1.456 | +1.507 | +0.160 | +0.166 | 8 | 8 | =0 | ||
| 15 | $15.00 | +1.031 | +1.067 | 4.2 | -1.113 | -1.152 | -0.083 | -0.086 | 9 | 9 | =0 | ||
| 16 | $38.55 | -1.165 | -1.206 | 4.7 | +0.492 | +0.510 | -0.673 | -0.696 | 10 | 10 | =0 | ||
| 17 | $35.12 | -0.845 | -0.875 | 4.6 | +0.171 | +0.177 | -0.674 | -0.698 | 11 | 11 | =0 | ||
| 18 | $25.00 | +0.098 | +0.102 | 4.2 | -1.113 | -1.152 | -1.015 | -1.051 | 12 | 12 | =0 | ||
| 19 | $38.29 | -1.141 | -1.181 | 4.5 | -0.150 | -0.155 | -1.291 | -1.336 | 13 | 13 | =0 | ||
| 20 | $14.00 | +1.124 | +1.163 | 3.7 | -2.719 | -2.815 | -1.595 | -1.651 | 14 | 14 | =0 | ||
| 21 | $45.99 | -1.859 | -1.924 | 4.5 | -0.150 | -0.155 | -2.009 | -2.079 | 15 | 15 | =0 | ||
P v S | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:D2 | D2 | =AVERAGE(MrExcel[Price]) |
| F2:G2 | G2 | =AVERAGE(MrExcel[Rtg]) |
| C3 | C3 | =STDEV.S(MrExcel[Price]) |
| F3 | F3 | =STDEV.S(MrExcel[Rtg]) |
| D4 | D4 | =STDEV.P(MrExcel[Price]) |
| G4 | G4 | =STDEV.P(MrExcel[Rtg]) |
| C7:C21 | C7 | =ZScore([@Price],@Mean,@StdDevP,FALSE) |
| D7:D21 | D7 | =ZScore([@Price],@Mean,@StdDevS,FALSE) |
| F7:F21 | F7 | =ZScore([@Rtg],@Mean,@StdDevP) |
| G7:G21 | G7 | =ZScore([@Rtg],@Mean,@StdDevS) |
| H7:H21 | H7 | =[@[Price Z(P)]]+[@[Rtg Z(P)]] |
| I7:I21 | I7 | =[@[Price Z(S)]]+[@[Rtg Z(S)]] |
| J7:J21 | J7 | =RANK.EQ([@[Sum Z(P)]],[Sum Z(P)]) |
| K7:K21 | K7 | =RANK.EQ([@[Sum Z(S)]],[Sum Z(S)]) |
| L7:L21 | L7 | =[@[Rank X(S)]]-[@[Rank X(P)]] |
