Formula for finding Standard deviation

How to Calculate Standard Deviation Using Microsoft Excel

Spread the love

How to calculate the standard deviation of a set of data is one useful piece of knowledge.

Well, with spreadsheets, it’s the most natural thing.

And, using spreadsheets does not give you the fundamental knowledge of it.

But, mind if we get into the engine of it?

In this tutorial, I’ll cover both the long route to show you the fundamentals. I’ll also use the shortcut to get the same.

Here is the Formula for Calculating Standard deviation

 

For simplicity, I’ll use color codes to help you grasp the formula.

formula for standard deviation

 

 

Where:

σ   = Standard deviation

∑ = sigma means the summation of

x = denotes any number or value in the set

X = Mean of the set of data

N =total number of sets

In other circles, you’ll come across jargon like:

Standard deviation formula variants

The above formula will help you get the standard deviation of a set of data without the repetitive occurrence of many figures.

In real life, a standard set of data would have many values occurring many times. Statisticians consider that and introduce the f for frequencies of occurrence.

It’s not a big deal working with frequencies, especially when you can grasp the concept from a data set without frequencies.

Illustration: Consider the table below, consisting of 10 students and their scores for a continuous assessment test (CAT) for a history test

Serial Number Student Name Marks
1. Richard 4
2. Amina 9
3. Joe 11
4. Rashid 12
5. Joel 17
6. Susan 17
7. Daniel 5
8. Ashley 8
9. Ziann 12
10. Ken 14

 

Required:

Help the teacher to obtain:

  1. The Mean
  2. The standard Deviation

Solution a) Finding the Mean (For tutorial purposes- I’ll use Microsoft Excel):

 Hint:

  • Copy the table and paste it in a spreadsheet
  • Add another column to the right and name it( -x)
Serial Number Student Name Marks
1. Richard 4
2. Amina 9
3. Joe 11
4. Rashid 12
5. Joel 17
6. Susan 17
7. Daniel 5
8. Ashley 8
9. Ziann 12
10. Ken 14

 

  • The mean is deducted against every mark entry (x) is to give us the (X-x). Refer to the table below
  • The array of results under column (X-X) falls on either the positive or negative side. And to get a reasonable figure from those results, squaring them phases out escalation in the column (X-x)2.

 

Serial Number Student Name Marks(X) X (X -x) (X -x)²
1. Richard 4 10.9 -6.9 47.61
2. Amina 9 10.9 -1.9 3.61
3. Joe 11 10.9 0.1 0.01
4. Rashid 12 10.9 1.1 1.21
5. Joel 17 10.9 6.1 37.21
6. Susan 17 10.9 6.1 37.21
7. Daniel 5 10.9 -5.9 34.81
8. Ashley 8 10.9 -2.9 8.41
9. Ziann 12 10.9 1.1 1.21
10. Ken 14 10.9 3.1 9.61
109 109 180.9

 

To get Mean:

 

Get the sum of deviations or the (X-x)2   Column and divide the amount by the number of students in the data set (n).

Total Marks =X 109
Total Students=n 10
Mean = 109 or =10.9
10

 

Solution B: Calculating the Standard Deviation:

Refer

Standard deviation calculated

 

Calculating Standard deviation and Mean using Microsoft Excel functions

By now, you have grasped the fundamentals of how to calculate the following – of a given set of data:

  • Standard deviation
  • Mean

 

Here let’s do the same using the power of spreadsheets. We’ll use the data set in Illustration 1 above.

Microsoft Excel uses the function AVERAGE to calculate the mean.

Therefore, to compute mean:

  • Use one cell aside from the one you have your data set figures.
  • Put in and equals sign and type on the word AVERAGE followed by two brackets[=AVERAGE()]

Compute mean using MS excel

  • Inside the two brackets, list the range of cells with your data set. You can also double click in the brackets and drag your cell selector over the range and press Enter. And that’s it your mean is 10.9

Calculating the Standard Deviation

It’s straightforward to calculate the Standard deviation of a data set using MS Excel functions.  Follow the steps to calculate the mean above BUT:

  • To get Standard Deviation, use the formula: =STDEV(~~~cell reference~~~)
  • In-between the brackets above, insert the range of cells covering your data set.
  • See the image below, derived from the Illustration one above

calculate standard deviation

That’s it, calculating the mean and the standard deviation to a set of data.

Bonus Tip on How to Calculate Standard Deviation

 

Looking at the above image, our total sum of squared deviations is 180.9

So you can get the square-root of it. But remember to divide by the number of sets in your data.

total sum of squared deviations

Note that the   MS Excel function for obtaining square roots is =SQRT (cell value….)

So we still end up with 4.25

Still another tip:

  • Divide the total number of sum deviations with the total number of entries.
  • You’ll get (180.9/10=18.09)
  • Use a separate cell to get the square root of 18.09

Note: To obtain the square root of a number in MS Excel, use an equal sign, followed by the cell reference containing the figure. Then, outside the cell reference and power it by half to get the square root. (In the formula bar use insert the sign^0.5) And you’d be done.

Refer to the image below for better guidance. (Pay special attention to the cell H9 and also at the formula in the formula bar)

formula in the formula bar

You may notice a slight variation in the figures we obtain above, but of most importance, you got the concept right.

Parting Note- How to Calculate Standard Deviation Using Microsoft Excel

Looking at the images above. Mathematics should never make you cringe. Understanding the fundamentals right is the most important thing.

The software simply comes in to complement elements here. And before I sign out, here’s the link to the MS Excel spreadsheet from which I did the formulas and the images for this post:

How to Calculate Standard Deviation Using Microsoft Excel

When you have the fundamentals right, you have at your fingertips the ability to wrench any sort of problem presented to you.

What would you prefer? Being a sharpshooter or being one with skills to dismantle and reassemble your Rifle?

I’d go the latter

Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *