In a Previous post dedicated to Excel, we were reviewing some of its most outstanding mathematical and statistical functions. This time we are going to focus on the calculation of the standard deviation in Excel. This is a measure used to quantify the degree of dispersion or concentration that may occur within a given data sample.
When we talk about this specific function, we must bear in mind that in Excel there is the possibility of using two different functions (although related to each other), depending on whether the calculation is going to focus on a sample or on a broader set of elements. But before addressing the question, we must clarify what exactly this concept is:
What is the standard deviation?
The standard deviation is a statistical term which is often also called typical deviation. It is usually represented with the lowercase Greek letter sigma (σ) or, if we use the Latin alphabet, with the letter "s". In documents written in English it appears as SD, which stands for standard deviation.
In any case, the standard deviation is an indication, expressed as a percentage, of how the distribution of a series of elements in a list is in reference to its average value or expected value. Thus it can be deduced that:
- A low standard deviation (low percentage) means that most of the data in a sample are clustered near the mean. For example: in the list of values 4, 5, 5,6, whose mean is 5.
- A high standard deviation (high percentage), on the other hand, indicates that the data is distributed over a wider range of values. For example: in the list of values 1, 4, 6, 9, whose mean is also 5.
This concept is much clearer and easier to understand when it is shown in a graph like the following:
As we can see, the blue graph reflects a relatively low deviation of the data, as a large part of it is concentrated near the mean value, which gives rise to a more pointed "mountain"; On the other hand, the green graph is more flattened, the data is distributed with a lower concentration with respect to the mean value, so the deviation is higher. Halfway between the two is the gray graph.
When calculating the standard deviation in Excel we are going to have to choose between two different functions. If it is a sample (for example, the academic qualifications of a group of students), we must use M STDEV; On the other hand, if we want to calculate the average deviation of all the students in the class or of all those who take this course in all the schools of a city, the formula to choose is P.VESTDEV
These are different calculations that, in certain cases, can give very different results. However, if the chosen sample is sufficiently representative, there will be no great differences when comparing the results obtained by means of both formulas.
In the latest versions of Excel both options have been merged into the single STDEV function, which makes the task less complicated. This is how we can use it:
STDEV function
The most convenient way to calculate the standard deviation in Excel is to put all the data in a table, either in a row or in a column. In this way, it will be easier select range of values on which we want to apply the function.
Next, we select the Excel cell in which we want the standard deviation calculation to appear and We introduce the formula. If, for example, we want to apply the function to the values that are aligned between cell A1 and A10, we will write the following: =STDEV(A1:A10). In this way, the result of the calculation will appear automatically in that previously selected cell.
The STDEV function is one of many that are built into the Excel spreadsheet program. If this is not the case or, for whatever reason, this function is not available, we have the possibility of entering it manually. These would be the steps to follow:
- The first, we select the cell where we want the result of the calculation to appear.
- Then we go to Formulas menu (in the upper bar) and there we click on the button "Insert Function".
- In the pop-up window that appears, all the Excel functions. To find the one we want, we must go to the category «Statistics», there select STDEV and click on «OK».
- A new window then appears. In it we must enter the range corresponding to the data for which we want to calculate the standard deviation and then press "To accept".
- After doing this, all windows are closed and the result is displayed in the cell of the selected Excel sheet.
Other variance calculation functions in Excel
In addition to the standard deviation or typical deviation, Excel offers us the possibility of calculating deviations in a more specific way or according to other criteria. These are some of the options:
- STDEV: Calculation of the standard deviation from a sample that includes numbers, text, and logical values.
- DEVIAS2: Calculation of the sum of the squares of the deviations.
- AVDEV: Calculation of the average of the absolute deviations from the mean of a data series.