Standard Deviation In Excel – Shiksha Online
The article discusses standard deviation in Excel.
Standard deviation is a popular Excel tool to calculate how much the data set deviates from the mean of the data series. It is ubiquitous in statistics since it is the measure of dispersion that tells us how much the values of a data set can move away from the mean.
Content
- What is Standard Deviation in Excel?
- How to Use the Standard Deviation Function for Samples in Excel?
- How to Use the Standard Deviation Function for Population in Excel?
- STDEV.S vs STDEV.P
What is Standard Deviation in Excel?
Standards Deviation in Excel measures the variance in a given set of numbers compared to the average (mean) of the numbers. This function is denoted by STDEV and it returns the standard deviation for a dataset.
We have six standard deviation formulas in Excel, divided into two groups –
Standard deviation for samples – STDEV, STDEV.S, and STDEVA
Standard deviation for an entire population – STDEV.P, STDEVPA, and STDEVP
Read MS Excel Tutorials
Standard Deviation (STDEV), (STDEV.S) & (STDEVA) Syntax
STDEV
=STDEV(number1,[number2],…)
STDEV.S is used when we have numeric data. It suggests that it will ignore the text and logical values.
=STDEV.S(number1,[number2],…)
STDEVA is used when we have both text and logical values, along with numbers.
Text and “FALSE” = 0
TRUE = 1
=STDEVA(number1,[number2],…)
Where,
number1 = Necessary argument. This argument corresponds to a sample of a population.
number2 = Optional argument. Number 2 can have a range of 2 – 255 corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.
In Excel 2010 and more updated versions, the STDEV.S function is used. This function replaces the STDEV function that is a part of the earlier versions.
You can still use STDEV as because it is a “compatibility function,” which suggests that you can still use it in later versions of Excel. However, Microsoft recommends that you use the newer feature.
STDEV is compatible with older versions of Excel (2007 or prior). It performs the same function as STDEV.S (which is used in any Excel software after 2007).
Must Explore – MS Excel Courses
Standard Deviation (STDEV.P), (STDEVPA) & (STDEVP) Syntax
STDEVP is used to calculate standard deviation based on the entire population as arguments.
=STDEVP(number1,[number2],…)
STDEV.P also calculates the standard deviation for a sample set of data and assumes the entire population as its arguments.
=STDEV.P(number1,[number2],…)
STDEVPA
=STDEVPA(number1,[number2],…)
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
How to Use the Standard Deviation Function for Samples in Excel?
To use the Standard Deviation function, you need to
- Place the data in a table
- Indicate the data range that you need to work on.
An ideal scenario would be to have all the data grouped in a single row or column. Once we have the data perfectly grouped, we go to the Excel cell where we want the standard deviation calculation to appear, and we enter the formula =STDEV.S(B2:B11) and the result appears after you press Enter.
In this case, B2 corresponds to the cell where the data begins and B11 to the last cell with data present.
How to Use the Standard Deviation Function for Population in Excel?
To find out the standard deviation for population, again, you can use a data set with multiple rows and columns.
We apply the formula –
=STDEV.P(B2:D7)
Press enter.
STDEV.S vs STDEV.P
So what is the difference between the functions STDEV.S and STDEV.P? Let’s check out –
| STDEV.S | STDEV.P |
| Use when your data is a sample of the entire population | Use when your data represents the entire population |
| The squared deviation is divided by the total number of samples -1. | The squared deviation is divided by the total number of arguments, mostly represented as n. |
| STDEV.S or STDEV function is used on a small sample of the entire population and is called an unbiased standard deviation analysis. | STDEV.P doesn’t give correct results if you have not captured the entire population for analysis |
FAQs
What is a population?
The population is a complete dataset on a spreadsheet.
What is the meaning of a sample in Excel?
Sample means only some elements of the large population. It is the subset of the population.
When should you use STDEV.P?
You use the STDEV.P function when your data represents the entire population, and you use the STDEV.S function when your data is a sample of the entire population.
What precautions we should take while using standard deviation in Excel?
While using standard deviation in Excel, we need to make sure that we don't confuse functions. The Excel STDEV.S formula is very close to STDEV.P. The first is usually the necessary formula for our calculations, as it is used when our data is a sample of a population. In case our data constitute the entire studied population, then we would like to use STDEV.P. Another thing we need to be careful about is the number of data values. Excel is limited by the number of values u200bu200bthat can be entered in the standard deviation function. All cells we use for our calculation must be numeric. We need to make sure that the error cells and the cells with text in them are not included in the standard deviation formula.
Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio
Top Picks & New Arrivals
