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
Get FREE Advanced Excel Exercises with Solutions!
