How to calculate the standard deviation in a microsoft excel spreadsheet? Step by step guide

When working in spreadsheet programs, you must constantly use mathematical formulas and equations to carry out different types of mathematical operations . In this regard, 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 standard deviation is, which is a measure of dispersion that indicates in a data set how far the values ​​can be with respect to the mean or average. What makes it become a more than necessary function when knowing any type of statistics in the data you are handling.

In this way, being able to know how to apply this tool within the Excel spreadsheet is very important, since that way you will be able to know the probabilities of an event occurring or not . To do this, follow in detail everything that will be explained below 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 average dispersion of a variable, so it is very interesting when you want to know how far the values ​​are from the average value. It is important to note that the standard deviation is always greater than or equal to zero .

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 any value in 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 mean, but this time the values ​​of the deviation are taken . Although this reasoning is logical and intuitive, there is a failure that can be verified with the following image.

As can be seen, 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, deviation can be understood as 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 was 1, but a mathematical characteristic of the mean with respect to the values ​​that form it is that the sum of the deviations is zero, in order to solve this it will be necessary to square the deviations.

Steps to calculate the statistical standard deviation in a Microsoft Excel worksheet

In order to carry out this mathematical calculation in your Excel worksheet, it will be necessary 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 with which you are working or enter a blank document of the application .
  • The next thing will be to enter the values you want to use, for this choose the column 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 where 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 A, the next thing to do will be to click on a blank cell, here you must select the cell where you want the standard deviation value to be displayed.

  • The next thing will be to write the standard deviation formula in the empty cell that you have selected, this formula is the following: = STDEV.P () where P means the “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 data points you have (N-1).

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button