The article will show you basic ways to calculate the **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.

## What Is Standard Deviation?

The term **Standard Deviation **is a measurement of the scattering of a set of values from their **mean**. If the **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**

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

**1. Using Mathematical Formula to Calculate the 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 **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 Population Standard Deviation in Excel

**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 batters. We used the 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: **How to Calculate Standard Deviation with IF Conditions in Excel

## 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.

**Download Practice Workbook**

## 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

- Calculate Percentile from Mean and Standard Deviation in Excel
- How to Calculate Standard Deviation of y Intercept in Excel
- How to Calculate Uncertainty in Excel

**<< Go Back to Standard Deviation Formula in Excel | Excel for Statistics**** | Learn Excel**