Advertisements  How to Calculate Standard Deviation in Excel

-

|

Introduction

Standard Deviation is a measure of how dispersed values are from their mean (average). You can calculate standard deviation in Excel with STDEV, STDEVP, STDEV.S or STDEV.P Functions.

In this guide you will learn how to calculate standard deviation in Excel based on a sample of a population. The guide also covers how to calculate standard deviation in Excel based on an entire population.

How to Calculate Sample Standard Deviation in Excel

STDEV and STDEV.S (newer version of STDEV) functions are used to calculate standard deviation based on sample of a population.

When you use any of these functions, Excel assumes that its inputs are a sample of the population, not the entire population.

To calculate standard deviation in Excel based on the entire population it is better to use the STDEVP function.

This section covers how to use STDEV, STDEV.S STDEVP and STDEV.P functions.

How to Calculate Sample Standard Deviation in Excel with STDEV and STDEV.S Functions

The syntax of STDEV and STDEV.S functions are:

STDEV(number1,[number2],...)
STDEV.S(number1,[number2],...)

Number1 (required) – represents the first number in a sample of a population
Number2 (optional) – represents a number between 2 to 255 in a sample of a population

Important Notes

1. The formulas assume that the data supplied are samples of the population.
2. If your data represents an entire population use STDEVP and STDEV.P functions instead. These are covered in the next section
3. STDEV and STDEV.S use the “n-1” method to calculate the standard deviation
4. Number1, number2, etc can either be numbers or names, arrays, or references that contain numbers.
5. If any of the values included in STDEV or STDEV.S formula arguments reference a cell that contains text, those cells are ignored.
6. If your sample data include logical values and text representations of numbers, use the STDEVA function instead.
7. STDEV and STDEV.S use the formula below to calculate standard deviation in Excel:

The formula above is the square root (SQRT) of the variance (VAR) of the sample.

In the formula, x is the sample Mean AVERAGE(number1,number2,…) while n is the sample size.

To calculate Sample standard deviation for the data shown in cells A2 to A11 use the formula

=STDEV(A2:A11)

The result is 27.46391572 (cell B14).

To use the newer function, STDEV.S use this formula

STDEV.S(A2:A11)

How to Calculate Standard Deviation in Excel with STDEVP and STDEV.S Functions

As I mentioned in the last section STDEV and STDEV.S functions assume that the input data are samples. To calculate standard deviation for an entire population use STDEVP and STDEV.S.

Using the data shown in the last section, to calculate standard deviation for the whole population, use the formulas below:

=STDEVP(A2:A11)
=STDEV.P(A2:A11)

The result of both formula is 26.05455814.

How to Calculate Mean and Standard Deviation in Excel

I said earlier that the standard deviation functions use the formula below:

In this section I will demonstrate how to compute standard deviation from the mean (Average) of a sample data using the formula shown above.

This section will reference the worksheet shown below. Column A contains the sample data.

To calculate standard deviation using the formula above, follow the steps below:

• Calculate the Mean of the sample with the formula below:
=AVERAGE(A2:A11)
• Next, calculate the dispersion of each sample data in column A from the above Mean. This is shown in cells B2 to B11 in the image below:
• Then square each of the dispersion values. This was done in column C (See the the image above).
• The squared values are then summed in cell C14.
• Next, count the sample data and subtract 1 from the total. The formula used is:
=COUNT(A2:A11)-1

and the result is 9, shown in Cell C15.

• Finally, take the square root of C14/C15 (C14 divided C15) as shown below:
=SQRT(C14/C15)

The result is 27.46392. The exact value returned by the STDEV and STDEV.S functions.

This last section will be useful for college students. If you are required to calculate standard deviation but show the steps, use the steps in this section.

Conclusion

Standard deviation calculation using Excel is very straight forward. In this guide I showed various ways you can calculate standard deviation in Excel.

In the last section you saw how to perform the same calculation from the Mean of a sample.

I hope this guide has been helpful. Have any question or comment? Use the “Leave a Reply” form at the end of this page.

Additional Resources and References

LATEST DEALS    How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...

How to Setup WhatsApp Auto Reply in an Android Phone

Introduction This guide demos how to setup Whatsapp auto reply in an Android phone. Steps...

How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

Introduction This guide demos how to set out of office auto reply in Outlook, Outlook.com and Gmail.

How to Take Ownership of Folder in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to take ownership of folder in Windows 10. Options to...

How to Install Windows Server 2019 from USB

Introduction This guide demos how to install Windows Server 2019 from USB. Steps to Install...

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

How to Fix Windows Server 2016 if Stuck at Automatic Repair Loop

Introduction This guide demos how to fix Windows Server 2016 automatic repair loop. Automatic repair loop means that your...

2 Easy Ways to Start Windows 10 Safe Mode

Introduction In previous versions of Windows you could start safe mode by pressing F8. But in Windows 10, F8...

How to Open Local Security Policy in Windows 10 (5 Methods)

Introduction This guide demos how to open local security policy in Windows 10. Options to...

How to Find, Download or Share Spotify Playlists

Introduction This quick guide will show you step by step how to find, download or share Spotify Playlists. You...

4 Easy Ways to Insert Bullet points in Excel

Introduction Inserting bullet points in excel is not as straightforward as adding bullet points in Word. This guide demonstrates...

How to Add More Rows to a Table in Word and Google Docs

Introduction This guide demos how to add more rows to a table in Word. It also has a...

How to Create a Bar Chart in Excel and Google Sheets

Introduction This guide demos how to create a bar chart in Excel and Google Sheets. A bar chart is...

How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...

How to Create a Pie Chart in Excel and Google Sheets

Introduction This guide demos how to create a pie chart in Excel and Google Sheets. A pie chart is...

iTechGuides’ Top 10 Online Games

The gaming world is getting bigger day by day. Many people are getting engaged to play online games worldwide.

How to Open Local Security Policy in Windows 10 (5 Methods)

Introduction This guide demos how to open local security policy in Windows 10. Options to...

How to Change Network from Public to Private in Windows 10

Introduction This guide demos 2 methods to change network from public to private in Windows 10.

How to Disable Touchpad in Windows 10 for Dell or HP Laptop

Introduction This guide demos how to Disable Touchpad in Windows 10 for a Dell or HP laptop.

How to Change Account Picture in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to change account picture in Windows 10. Windows 10...

How to Create Xbox Live Account (2 Methods)

Introduction This guide demos 2 methods to create Xbox Live account. Options to Create Xbox...

Windows 10 1909 (19H2 Preview) Features

Introduction The latest version of Windows 10 1909 (19H2 Preview) was released on September 5, 2019. This guide details...

Top 5 Websites Like Craigslist for Gigs

Craigslist is a local classified Ads website with 7 sections including gigs. It is a brilliant site but using other websites like...

Cannot Start Microsoft Outlook [Fixed]

Introduction While I was working on How to Change Outlook Password, I opened Outlook and received the error message...

iTechGuides’ Top 10 Online Games

The gaming world is getting bigger day by day. Many people are getting engaged to play online games worldwide.

Dynamic Disk vs Basic Disk: How to Convert to Dynamic Disk

Introduction Dynamic disk type was introduced by Microsoft with Windows 2000. Earlier versions of Windows Operating Systems used Basic...

LATEST DEALS    By using this website you agree to accept our Privacy Policy and Terms & Conditions