When works in spreadsheet programs should 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 the typical deviation, which is a dispersion measure indicating in a data set how far the values can deviate from the mean or average. What makes it a more than necessary function when knowing any kind of statistic in the data you're driving.
In this way, being able to know how to apply this tool inside the Excel spreadsheet is very important, since that way you will be able to know the probabilities that a event occurs or not. To do this, follow everything in detail what will be explained next in the post.
What is the standard deviation in statistics and what does it allow us to know?
The standard or standard 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 to understand this concept clearly, it will be necessary to analyze two concepts about deviation, such as the following:
- Deviation: It is the separation that exists between a value any 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 the standard deviation is going to be calculated in a similar way to the average, but this time they are taken are the deviation values. Even though this reasoning is logical and intuitive, there is a failure that can be verified with the following image.
As you can see, in said image presented 6 values are observed, this means that N = 6, the mean of the observations is presented by the black line that is located in the entire center of the graph, it has a value of 3. Therefore, knowing the mean 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 à (2-3) = -1
- Deviation à (4-3) = 1
- Deviation à (2-3) = 1
- Deviation à (4-3) = 1
- Deviation à (2-3) = -1
- Deviation à (4-3) = 1
If you can see when you 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 out of 1, but a mathematical characteristic of the mean with respect to the values that form it is, than the sum of the deviations is zero, in 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 complete each of the steps that we will 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.
- The next will be enter values you want to use, for this choose the spine what you want and add each one 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 the data is entered in, 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 A, the next will be to click on a blank cell, here you must select the cell where you want the standard deviation value is displayed.
- The next thing is to write the formula of the standard deviation In the empty cell you have selected, this formula is as follows: = STDEV.P () where P stands for "Population". The population standard deviation 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 brackets you must write the letter and the number of the cell that contains the first portion of data, writes 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 must then place from A1: A10 these being the cells that contain all the data to evaluate. In case you want to show the standard deviation of values from different cells then it will be necessary to place it in the following way: = STDEV.P (A1, B3, C5).
- Once you have entered this data, you must press the key "Enter" for it to take place the solution of the formula and so you can know the result of the standard deviation of the data series you are evaluating.
