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 large number of advantages since its repertoire of functions is very wide.
This is how here you will also have the opportunity to calculate what the typical deviationwhich is a dispersion measure which indicates in a set of data how much the values can move away from the mean or average. What makes it become a more than necessary function when knowing any kind of statistics on the data that you are driving
In this way, to know how to apply this tool inside excel spreadsheet is very importantsince that way you will be able to know the probabilities that a event happens or not. To do 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 deviate from the average value. It is important to note that the standard deviation is always greater than or equal to zero.
In order for this concept to be clearly understood, it will be necessary to analyze two concepts about deviation, such as the following:
- Deviation: It is the separation that exists 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 average, but this time they are taken deviation values. Even though this reasoning is logical and intuitivethere is a failure that can be verified with the following image.
As can be seen, 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 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 the 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 to do is go to MS Excel where you have the data you are working with or enter a blank application document.
- next thing will be enter the values that you want to use, for this choose the 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 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 display the standard deviation value.
- The following will be to write the formula of 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 that 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 rank 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 it will be necessary that you place it as follows: =PSTDEV(A1,B3,C5).
- Once these data have been entered, you must press the key “Enter” for it to take place the formula solution and so you can know the result of the standard deviation of the data series that you are evaluating.
Facebook Comments
Disqus