How to Calculate Standard Deviation of a Frequency Distribution in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

standard deviation of a frequency distribution in excel


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

standard deviation of a frequency distribution in excel

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.

standard deviation of a frequency distribution in excel

  • 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

standard deviation of a frequency distribution in excel

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)

standard deviation of a frequency distribution in excel

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


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.

standard deviation of a frequency distribution in excel

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

standard deviation of a frequency distribution in excel


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

Meraz Al Nahian
Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo