How to Calculate Standard Deviation of Frequency Distribution in Excel?

---

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: Σm_in_i / N

where:

• m_i: The midpoint of the i^th group
• n_i: The frequency of the i^th 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: √Σn_i(m_i-μ)² / (N-1)

where:

• n_i: The frequency of the i^th group
• m_i: The midpoint of the i^th 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.