Learning Objectives
- To calculate mean, variance, and standard deviation in excel [with video tutorial]
How to calculate mean, variance, and standard deviation in Excel?
Range | frequency |
0-10 | 1 |
10-20 | 7 |
20-30 | 14 |
30-40 | 7 |
40-50 | 1 |
Total | 30 |
Source: A hypothetical data
Step 1: Calculating the mid value
Range | f | Mid value (m) |
0-10 | [D12] 1 | [E12] 5 |
10-20 | 7 | 15 |
20-30 | 14 | 25 |
30-40 | 7 | 35 |
40-50 | [D16] 1 | [E16] 45 |
Total | 30 |
Calculating the mid value is an easy task. Justing taking an average of the range.
[D12] [D16] [E12] [E16] are the cell numbers of excel.
Step 2: Calculating mean, variance, and standard deviation
Mean | 25 | =(SUMPRODUCT(E12:E16,D12:D16))/D17 |
Variance | 73.333 | =(SUMPRODUCT(D12:D16,E12:E16,E12:E16)/D17)-Mean^2 |
Standard Deviation | 8.5635 | =SQRT(Variance) |
Source: Excel 2013
Within these two steps, we can get mean, variance, and standard deviation.
For more clarification, watch our video tutorial
0 Comments