The article will show you basic ways to calculate **Standard Deviation **of a **Frequency Distribution** in Excel. Determining the **Standard Deviation **is a very important parameter in statistics as it shows us how data varies from its mean and thus it can be very helpful in practical aspects.

In the dataset, we have batting statistics in a range of **Years**. To illustrate the dataset, let me explain briefly about it. In the year of **2011**, **23 Batters **scored **909 Runs **each; in **2012**, **19 Batters **hit **780 Runs **each and so on.

## Download Practice Workbook

## What Is Standard Deviation?

The term **Standard Deviation **is a measurement of the scattering of a set of values from their **mean**. If **Standard Deviation **of a set of values is high, we can say that the data highly deviates from its mean or average. And thus we can say that those data are not similar in nature or they are independent. If the **Standard Deviation** is low, we can say that the data stays close to its mean and there is a greater possibility of them being related to each other. The mathematical formula for **Standard Deviation **is given below.

Where, **f = Frequency of the Data**

**x = Each Value of the Data**

**x̄ = Mean of the Data**

## 2 Ways to Calculate Standard Deviation of a Frequency Distribution in Excel

**1. Using Mathematical Formula to Calculate Standard Deviation of a Frequency Distribution**

In this section, I’ll show you how to determine the **Standard Deviation **of the **Runs **that these batters scored using the mathematical formula. The **Frequency **of this data is the number of players that scored a certain amount of **runs **in each year. Let’s go through the process below.

**Steps:**

- First, make some necessary columns for the necessary parameters that we need to determine and type the following formula in cell
**E5**.

`=C5*D5`

This formula will store the total runs scored by the batters in **2011**.

- After that, hit the
**ENTER**button and you will see the total**runs**that these players scored together in**2011**.

- Later, use the
**Fill Handle**to**AutoFill**the lower cells.

- Thereafter, use the formula below in cell
**C13**and press**ENTER**.

`=SUM(E5:E11)/SUM(D5:D11)`

The formula will return the **Average runs **per year of a **Batter **with the help of **the SUM function**.

- Now type the following formula in cell
**F5**, press**ENTER**and use**Fill Handle**to**AutoFill**.

`=D5*(C5-$C$13)^2`

This formula will store the** f*(x-x̄)^2 **value for each year.

- After that, type the following formula in cell
**C14**and press**ENTER**.

`=SUM(F5:F11)/SUM(D5:D11)`

This will calculate the **Variance **of this data.

- Finally, type the following formula in cell
**C15**and press**ENTER**.

`=SQRT(C14)`

As **Standard Deviation **is the square root of **Variance**, we use **the SQRT Function** to determine the square root of the value in **C14**.

**Read More: How to Calculate Mean Variance and Standard Deviation in Excel**

**Similar Readings**

**How to Make a Categorical Frequency Table in Excel (3 Easy Methods)****Make a Relative Frequency Histogram in Excel (3 Examples)****How to Create a Grouped Frequency Distribution in Excel (3 Easy Ways)**

**2. Applying Excel SUMPRODUCT Function to Calculate Standard Deviation of a Frequency Distribution**

If you want to determine the **Standard Deviation **of a **Frequency Distribution **in a shortcut way, it will be best if you use **the SUMPRODUCT function** for it. Let’s discuss the solution below.

**Steps:**

- First, make some necessary rows to store the parameters and type the following formula in cell
**C13**.

`=SUMPRODUCT(D5:D11,C5:C11)/SUM(D5:D11)`

Here, the** SUMPRODUCT **function will return the total **runs **over the **7 years**. We want the average runs scored by each batter in a year, so we divided it by the total number of the batters. We used Excel **SUM Function **to input the number of total batters.

- Press
**ENTER**to see the result.

- After that, type the following formula in cell
**C14**.

`=SQRT(SUMPRODUCT((C5:C11-C13)^2,D5:D11)/SUM(D5:D11))`

Here we used **the SQRT Function **to determine the square root of **Variance **and hence calculate the **Standard Deviation**

**Formula Breakdown**

**SUM(D5:D11) —->**returns the total number of batters**Output:****157**

**(C5:C11-C13)^2 —->**returns a range of values which are squares of the difference between the**data**(**runs**) and mean.**SUMPRODUCT((C5:C11-C13)^2,D5:D11) —->**results in the summation of the**products**between the range**(C5:C11-C13)^2**and**D5:D11****Output:****2543093.00636943**

**SUMPRODUCT((C5:C11-C13)^2,D5:D11)/SUM(D5:D11) —->**becomes**2543093.00636943/157****Output:****16198.****0446265569**

**SQRT(SUMPRODUCT((C5:C11-C13)^2,D5:D11)/SUM(D5:D11)) —->**turns into**SQRT(16198.0446265569)****Output:****127.****271538949432**

Finally, we get the **Standard Deviation **of our data.

Thus you can determine the **Standard Deviation **of **Frequency Distribution **using the **SUMPRODUCT Function**.

**Read More: ****Calculate Cumulative Frequency Percentage in Excel (6 Ways)**

## Practice Section

Here, I am giving you the dataset of this article so that you can make it on your own and practice these methods.

## Conclusion

In the end, we can surmise that you will learn the basic knowledge of calculating the **Standard Deviation **of a **Frequency Distribution**. Data analysis for Signal Processing, Communication, Power Transmission, or even Cosmic Radiation requires the basics of **Standard Deviation**. If you have any questions or feedback regarding this article, please share them in the comment box. Your valuable ideas will help me enrich my upcoming articles.

## Related Articles

**How to Make a Relative Frequency Table in Excel (with Easy Steps)****Generate Random Number with Mean and Standard Deviation in Excel****How to Calculate Average and Standard Deviation in Excel****How to Calculate Relative Frequency Distribution in Excel (2 Methods)****Calculate Cumulative Relative Frequency in Excel (4 Examples)****How to Find Outliers with Standard Deviation in Excel (with Quick Steps)**