Often you may want to calculate the standard deviation of a frequency distribution in Excel.
For example, suppose you have the following frequency distribution:
The following step-by-step example shows how to calculate the standard deviation of this frequency distribution in Excel.
Step 1: Enter Values for Frequency Distribution
First, we’ll enter the class limits and frequency values for our frequency distribution:
Step 2: Calculate Mean of Frequency Distribution
We can use the following formula to estimate the mean of our frequency distribution:
Mean: Σmini / N
where:
- mi: The midpoint of the ith group
- ni: The frequency of the ith group
- N: The total sample size
To apply this formula in Excel, we will type the following formulas into cells D2, E2, and F2:
- D2: =AVERAGE(A2:B2)
- E2: =D2*C2
- F2: =SUM($E$2:$E$6)/SUM($C$2:$C$6)
We will then click and drag these formulas down to each remaining cell in each column:
Step 3: Calculate Standard Deviation of Frequency Distribution
We can use the following formula to estimate the standard deviation of our frequency distribution:
Standard Deviation: √Σni(mi-μ)2 / (N-1)
where:
- ni: The frequency of the ith group
- mi: The midpoint of the ith group
- μ: The mean
- N: The total sample size
To apply this formula in Excel, we will type the following formulas into cells G2, H2, and I2:
- G2: =D2-F2
- H2: =G2^2
- I2: =C2*H2
We will then click and drag these formulas down to each remaining cell in each column:
Lastly, we can type the following formula into cell B8 to calculate the standard deviation of this frequency distribution:
=SQRT(SUM(I2:I6)/(SUM(C2:C6)-1))
The following screenshot shows how to use this formula in practice:
The standard deviation of this frequency distribution turns out to be 9.6377.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create Grouped Frequency Distribution in Excel
How to Create a Percent Frequency Distribution in Excel
How to Calculate Cumulative Frequency in Excel
