How to Calculate Standard Deviation
In this article, I will show you how to do the standard deviation, listing the 6 simple steps required, and showing the process manually and also describing how to do it using Excel (includes links to a downloadable spreadsheet of the examples given).
Six Straightforward Steps on How to Calculate Standard Deviation
- Get the mean
- Get the deviations
- Square these
- Add the squares
- Divide by total numbers less one
- Square root of the result is the standard deviation
Here is a step-by-step example of how to do the standard deviation with the manual method.
- Get the mean: To begin with, you need to find the mean or the average. For example, add 23, 92, 46, 55, 63, 94, 77, 38, 84, 26 = 598, then divide by 10 (the actual number of numbers) which is 598 divided by 10 = 59.8. So the mean or average of 23, 92, 46, 55, 63, 94, 77, 38, 84, 26 is 59.8
- Get the deviations: Subtract the mean from each of the numbers. The answers are: -36.8, 32.2, -13.8, -4.8, 3.2, 34.2, 17.2, -21.8, 24.2, -33.8
- Square These: To square means multiply them by themselves. The answers are: 1354.24, 1036.84, 190.44, 23.04, 10.24, 1169.64, 295.84, 475.24, 585.64, 1142.44
- Add the squares: The total of these numbers is 6,283.60
- Divide by total number of numbers less one: You had 10 numbers less 1 is 9 numbers, so 6283.60 divided by 9 = 698.18
- The Square root of the result is the standard deviation: A square root is the number multiplied by itself to get 698.18 which is 26.4, so 26.4 is the standard deviation.
Step-By-Step Example Using Excel
Now I will show you how to calculate the standard deviation using Excel. You will need to download the spreadsheet file below or make your own in order to do this.
- view or download spreadsheet
Standard deviation example spreadsheet in Google docs, click on file click on download and save the excel spreadsheet
Enter your range of numbers as shown in cells 1 to 10.
- Place the cursor in Cell 11.
- Go to the menu bar, select insert, select function: the insert function dialog box opens.
- Click on the category and select Statistical.
- In the window below select Average.
- Hit enter.
- When you hit enter another dialog box will appear asking you to confirm the range, i.e. the numbers in cells 1 to 10 that you wish to perform the calculation on.
- Just hit enter.
- The Mean or Average will now appear in Cell 11.
- Place the cursor in Cell 12.
- Go to the menu bar, select insert, select function.
- The function dialog box will open, select statistical, in the window below scroll down and select STDEV.
- When you hit enter another dialog box will appear asking you to confirm the range, i.e., the numbers in cells 1 to 10 that you wish to perform the calculation on. Since it automatically seeks to perform the calc on all the cells above you will have to change the range from D4:D14 to D4:D13.
- The standard deviation will now appear in Cell 12.
Pat Yourself on the Back If
Your final calculation matches the image below.
What Does the Standard Deviation Tell You
The standard deviation is all about dispersion, how the set of numbers or data you have deviates from the mean; this is essentially a measure of uncertainty.
- Low Deviation shows the numbers are all reasonably similar
- High Deviation shows there is a lot of fluctuation in the numbers.
How can you use it:
- Conducting investment research since this is an aid to measuring or calculating volatility.
- Making weather comparison between locations or year to year.
- Analysing agricultural yields and/or prices.
- Almost everything to do with population analysis.
- Lots of things in sports, with athletes, team performances, motorsports, horse racing, etc.
All these analyses help in prediction by looking closely at past performance.
Example: Using Deviation to Analyse Hub Scores
Just say we were using deviation to analyse hub scores, in the example below all the hub scores, are above 90, when the SD is calculated for this range the SD is 2.92. This is low; as a contrast the original calculation has hub scores from 23 to 94; in other words, there is a lot of volatility.
So if one wished to rank HubPages, it might be that those with a lower standard deviation, i.e., less volatility are more consistent, and so we enter the esoteric world of statistics. Imagine this was horses.
An Example of Using SD
Questions & Answers
© 2006 des donnelly