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.

 

 

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:

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)².

 

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)²   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

 

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()]

• 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

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.

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)

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!