How to calculate standard deviation in a Microsoft Excel spreadsheet? step by step guide

When works in spreadsheet programs must be used constantly mathematical formulas and equations to carry out different types of mathematical operations. In this aspect Excel has a lot of advantages since its repertoire of functions is very wide.

This is how here you will also have the opportunity to calculate what is the typical deviationwhich is a dispersion measure which indicates in a data set how far the values ​​can be from the mean or average. What makes it become a more than necessary function when knowing any type of statistics in the data that you are driving

In this way, to be able to know how to apply this tool inside excel spreadsheet is very importantsince in this way you will be able to know the probabilities that a event occurs or not. For this, follow everything in detail what will be explained to you next in the post.

What is the standard deviation in statistics and what does it allow us to know?

The standard or typical deviation as it is also known is a statistical measure that allows you to know the information about the mean dispersion of a variable, so it is very interesting when you want to know how far the values ​​are from the mean value. It is important to note that the standard deviation is always greater than or equal to zero.

In order to understand this concept clearly, it will be necessary to analyze two concepts about deviation, such as the following:

• Deviation: It is the gap between a any value of the series and the mean or average as it is also called.
• Mathematical expectation, mean or expected value: This is the mean of the sample data series.

Knowing these two terms, then it can be said that standard deviation will be calculated in a similar way to the mean, but this time they are taken deviation values. Even though this reasoning is logical and intuitivethere is a fault that can be verified with the following image.

As you can see, in said image presented 6 values ​​are observedthis means that N=6, the mean of the observations is presented by the black line that is located in the entire center of the graphit has a value of 3. Therefore, knowing the mean, it can be understood by deviation the difference between any of the observations and the black line.

This means that there are 6 observations, so the following procedure must be carried out with each of them:

• deviation to (23) = -1
• deviation to (4-3) = 1
• deviation to (23) = 1
• deviation to (4-3) = 1
• deviation to (23) = -1
• deviation to (4-3) = 1

If you can see when add the two deviations, 6 deviations and divide by N = 6, the result ends up being zero. The logic would be that mean deviation outside 1but a mathematical characteristic of the mean with respect to the values ​​that form it isthat the sum of the deviations is zeroin order to solve this it will be necessary square the deviations.

Steps to Calculate Statistical Standard Deviation in a Microsoft Excel Spreadsheet

In order to carry out this mathematical calculation in your Excel worksheet, you will need to carry out each of the steps that we are going to indicate below:

• The first thing will be to enter Microsoft Excel where you have the data you are working with or enter a blank application document.
• next thing will be enter the values you want to use, to do so choose the spine you want and add each of the data and then write the value for each data in the cells. An example of this would be that in column A as the area that the data is entered, you could write a number in cell A1, in cell A2, in Cell A3 and so on.

• Once you have entered all the data in column Athe next thing will be to click on a blank cellhere you must select the cell where you want the standard deviation value is displayed.

• The next thing is to write the formula standard deviation in the empty cell you have selected, this formula is as follows: =STDEV.P () where P stands for “Population”. The standard deviation of the population takes into account all the points so this will be the value of N.
• In order to find the standard deviation of a sample you must write the following formula =STDEV.M(). In this case, the standard deviation of a sample will take into account a value less than the number of points of data what do you have (N-1).

• Now you must add what is the range of values, between the parentheses you must write the letter and number of the cell that contains the first piece of data, write two points (:) and then the letter and number of the last data cell. This will allow you to define the range to evaluate. For example, if you are in column A should then place from A1:A10 these being the cells that contain all the data to be evaluated. In case you want to show the standard deviation of values ​​from different cells then you will need to place it as follows: =STDEV.P(A1,B3,C5).
• Once you have entered these data, you must press the key “Enter” for it to be done the solution of the formula and so you can know the result of the standard deviation of the data series you are evaluating.