Measures of Variance
Excel Formulas
Excel Commands
|
A |
B |
C |
D |
E |
1 |
X |
Name |
Command |
Answer |
Answer (with Notes) |
2 |
$20,000 |
Range |
=MAX(A2:A8)-MIN(A2:A8) |
$25,000 |
Finds the range |
3 |
$25,000 |
Population Standard Deviation |
=STDEV.P(A2:A8) |
$8,539.13 |
Finds the population standard deviation |
4 |
$30,000 |
Sample Standard Deviation |
=STDEV.S(A2:A8) |
$9,354.14 |
Finds the sample standard deviation |
5 |
$35,000 |
Population Variance |
=VAR.P(A2:A8) |
72,916,667 |
Finds the population variance |
6 |
$40,000 |
Sample Variance |
=VAR.S(A2:A8) |
87,500,000 |
Finds the sample variance |
7 |
$45,000 |
Coefficient of Variation (Population) |
=VAR.P(A2:A8) /AVERAGE(A2:A8) |
26.2742% |
Finds the population coefficient of variation |
8 |
$50,000 |
Coefficient of Variation (Sample) |
=VAR.S(A2:A8) /AVERAGE(A2:A8) |
28.7820% |
Finds the sample coefficient of variation |
Click here to download the Excel spreadsheet shown in the above table
Computing Coefficient of Variation
- Usually, we find the average and standard deviation separately
- We then divide the standard deviation by the mean (average) to calculate the coefficient of variation
- Example: if we have the mean calculated in cell D9 and the sample standard deviation calculated in D4
- Sample Coefficient of Variation → D4/D9
- Note: It is recommended that you format the cell as a percentage and display 2 decimals