Excel: Calculate Standard Deviation of Frequency Distribution


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

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:

Excel standard deviation of frequency distribution

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:

x
Scroll to Top