Standard deviation function in excel. How to find the standard deviation

Percentage deviation refers to the difference between two numerical values ​​as a percentage. Let us give specific example: let's say one day 120 tablets were sold from the wholesale warehouse, and the next day - 150 pieces. The difference in sales is obvious, 30 more tablets were sold the next day. Subtracting 120 from 150, we get the deviation, which is +30. The question arises: what is the percentage deviation?

How to calculate percent deviation in Excel

The deviation percentage is calculated by subtracting the old value from the new value, and then dividing the result by the old value. The result of calculating this formula in Excel should be displayed in the percentage format of the cell. In this example, the calculation formula looks like this (150-120) / 120 = 25%. The formula is easy to check 120 + 25% = 150.

Note! If we swap the old and new numbers, then we will have a formula for calculating the margin.

The figure below shows an example of how the above calculation can be represented in the form of an Excel formula. The formula in cell D2 calculates the percent deviation between the sales values ​​for the current year and last year: = (C2-B2) / B2

It is important to pay attention to the presence of parentheses in this formula. By default, in Excel, division always takes precedence over subtraction. Therefore, if we do not put parentheses, then the value will be split first, and then another value is subtracted from it. Such a calculation (without parentheses) would be wrong. Closing the first part of a calculation in a formula with parentheses automatically raises the priority of the subtraction operation over the division operation.

Correctly with parentheses, enter the formula in cell D2, and then just copy it to the rest of the empty cells in the range D2: D5. To copy the formula by the most fast way, it is enough to move the mouse cursor to the keyboard cursor marker (to the lower right corner) so that the mouse cursor changes from an arrow to a black cross. Then just double-click the left mouse button and Excel will automatically fill in the empty cells with the formula, while it will itself determine the range D2: D5, which must be filled up to cell D5 and no more. This is a very handy Excel life hack.

Alternative formula to calculate percent deviation in Excel

In an alternative formula that calculates the relative variance of sales values ​​with current year immediately divide by the sales values ​​of the previous year, and only then the unit is subtracted from the result: = C2 / B2-1.

As you can see in the figure, the result of calculating the alternative formula is the same as in the previous one, which means it is correct. But the alternative formula is easier to write down, although it may be more difficult for someone to read so as to understand how it works. Or it is more difficult to understand what value it produces as a result of the calculation this formula if not signed.

The only drawback of this alternative formula is the inability to calculate the percentage deviation with negative numbers in the numerator or in the substitute. Even if we use the ABS function in the formula, the formula will return an erroneous result when negative number in a substitute.

Since in Excel, by default, the priority of the division operation is higher than the subtraction operation, there is no need to use parentheses in this formula.

Among the many indicators that are used in statistics, it is necessary to highlight the calculation of variance. It should be noted that doing this calculation manually is a rather tedious task. Fortunately, Excel provides functions to automate the calculation process. Let's find out the algorithm for working with these tools.

Variance is a measure of variation that is the mean square of deviations from mathematical expectation... Thus, it expresses the spread of numbers around the mean. The variance can be calculated as the general population, and by selective.

Method 1: calculation for the general population

For calculation this indicator in Excel for the general population, the function is used DISP.G... The syntax for this expression is as follows:

DISP.G (Number1; Number2; ...)

A total of 1 to 255 arguments can be applied. As arguments, both numeric values ​​and references to the cells in which they are contained can be used.

Let's see how to calculate this value for a range with numeric data.

Method 2: calculation by sample

In contrast to the calculation of the value for the general population, in the calculation of the sample, the denominator does not indicate the total number of numbers, but one less. This is done to correct the error. Excel takes into account this nuance in a special function that is intended for this type of calculation - DISP.V. Its syntax is represented by the following formula:

DISP.B (Number1; Number2; ...)

The number of arguments, as in the previous function, can also range from 1 to 255.

As you can see, the Excel program can greatly facilitate the calculation of variance. This statistic can be calculated by the application, both for the general population and for a sample. In this case, all user actions are actually reduced only to specifying the range of numbers to be processed, and the main Excel work does it himself. This will certainly save a significant amount of users' time.

In this article I will talk about how how to find the standard deviation... This material is extremely important for a full understanding of mathematics, therefore, a mathematics tutor should devote a separate lesson or even several lessons to studying it. In this article, you will find a link to a detailed and understandable video tutorial that explains what the standard deviation is and how to find it.

Standard deviation makes it possible to estimate the spread of values ​​obtained as a result of measuring a parameter. It is designated by a symbol (Greek letter "sigma").

The calculation formula is pretty simple. To find the standard deviation, you need to take Square root from variance. So now you have to ask, "What is variance?"

What is variance

The definition of variance sounds like this. Variance is the arithmetic mean of the squared deviations of values ​​from the mean.

To find the variance, perform the following calculations sequentially:

• Determine the average (simple arithmetic mean of a series of values).
• Then subtract the average from each of the values ​​and square the resulting difference (you got squared difference).
• The next step is to calculate the arithmetic mean of the resulting squares of the differences (you can find out why exactly the squares are below).

Let's look at an example. Let's say you and your friends decide to measure the height of your dogs (in millimeters). As a result of the measurements, you obtained the following height measurements (at the withers): 600 mm, 470 mm, 170 mm, 430 mm and 300 mm.

Let's calculate the mean, variance and standard deviation.

First, find the average... As you already know, for this you need to add all the measured values ​​and divide by the number of measurements. Calculation progress:

Average mm.

So, the average (arithmetic mean) is 394 mm.

Now you need to define deviation of the growth of each of the dogs from the average:

Finally, to calculate variance, each of the obtained differences is squared, and then we find the arithmetic mean of the results obtained:

Dispersion mm 2.

Thus, the dispersion is 21704 mm 2.

How to find the standard deviation

So how do you now calculate the standard deviation, knowing the variance? As we remember, take the square root of it. That is, the standard deviation is:

Mm (rounded to the nearest whole number in mm).

Using this method, we found that some dogs (for example, Rottweilers) are very big dogs... But there are also very small dogs (for example, dachshunds, just don't tell them this).

The most interesting thing is that the standard deviation carries useful information... Now we can show which of the obtained height measurements are within the interval that we get if we postpone the standard deviation from the mean (on both sides of it).

That is, using the standard deviation, we get a "standard" method that allows you to find out which of the values ​​is normal (average), and which is extraordinarily large or, conversely, small.

What is standard deviation

But ... everything will be a little different if we analyze sampling data. In our example, we considered general population. That is, our 5 dogs were the only dogs in the world that interested us.

But if the data is a sample (values ​​that are selected from a large population), then the calculations need to be done differently.

If there are values, then:

All other calculations are made in the same way, including the determination of the average.

For example, if our five dogs are just a sample from the general population of dogs (all dogs on the planet), we should divide by 4, not 5, namely:

Sample variance = mm 2.

In this case, the standard deviation for the sample is mm (rounded to the nearest whole number).

We can say that we have made some "correction" in the case when our values ​​are only a small sample.

Note. Why squared differences?

But why, when calculating the variance, do we take exactly the squares of the differences? Suppose, when measuring some parameter, you received the following set of values: 4; 4; -4; -4. If we just add the absolute deviations from the mean (difference) between each other ... negative values ​​cancel out with positive ones:

.

It turns out that this option is useless. Then maybe it is worth trying the absolute values ​​of the deviations (that is, the modules of these values)?

At first glance, it turns out well (the resulting value, by the way, is called the average absolute deviation), but not in all cases. Let's try another example. Let the measurement result be the following set of values: 7; one; -6; -2. Then the average absolute deviation is:

Blimey! Again, the result is 4, although the differences are much more scattered.

Now let's see what happens if we square the differences (and then take the square root of their sum).

For the first example, you get:

.

For the second example, you get:

Now it's a completely different matter! The standard deviation is the greater, the greater the spread of the differences ... which is what we were striving for.

In fact in this method used the same idea as when calculating the distance between points, only applied in a different way.

And from a mathematical point of view, the use of squares and square roots provides more value than we could get on the basis of absolute values deviations, so that the standard deviation is applicable to other mathematical problems.

Sergey Valerievich told you how to find the standard deviation.

Statistics use a huge number of indicators, and one of them is the calculation of variance in Excel. If you do it yourself manually, it will take a lot of time, you can make a lot of mistakes. Today we will look at how to decompose mathematical formulas into simple functions. Let's take a look at a few of the simplest, fastest and convenient ways calculations that will allow you to do everything in a matter of minutes.

Calculate the variance

Dispersion random variable is the mathematical expectation of the square of the deviation of a random variable from its mathematical expectation.

We calculate by the general population

To calculate mate. waiting, the program will use the DISP.G function, and its syntax is as follows "= DISP.G (Number1; Number2; ...)".

It is possible to use a maximum of 255 arguments, no more. Arguments can be prime numbers or cell references in which they are specified. Let's take a look at how to calculate variance in Microsoft Excel:

1. The first step is to select the cell where the result of the calculations will be displayed, and then click on the "Insert function" button.

2. The function control shell will open. There you need to look for the function "DISP.G", which can be in the category "Statistical" or "Complete alphabetical list". When it is found, you should select it and click "OK".

3. The function arguments window will open. In it, you need to select the line "Number 1" and on the sheet select a range of cells with a number series.

4. After that, the calculation results will be displayed in the cell where the function was entered.

This is how you can easily find the variance in Excel.

We make a calculation for the sample

In this case, the sample variance in Excel is calculated by indicating in the denominator not the total number of numbers, but one less. This is done for a smaller error using the special function VARB, the syntax of which = VARB (Number1; Number2; ...). Algorithm of actions:

• As in the previous method, you need to select a cell for the result.
• In the function wizard, find "DISP.V" under the "Complete alphabetical listing" or "Statistical" category.

• Next, a window will appear, and you should proceed in the same way as in the previous method.

Video: Calculate Variance in Excel

Conclusion

The variance in Excel is calculated very simply, much faster and more convenient than doing it manually, because the mathematical expectation function is quite complex and its calculation can take a lot of time and effort.

Good day!

In this article, I decided to consider how the standard deviation works in Excel using the STDEV function. I just haven't described or commented for a very long time, but also simply because this is a very useful function for those who are studying higher mathematics... And to provide assistance to students is sacred, I know from myself how difficult it is to master. In reality, the standard deviation functions can be used to determine the stability of the products being sold, create prices, adjust or form an assortment, and other equally useful analyzes of your sales.

Excel uses several variations of this rejection function:

Mathematical theory

To begin with, a little about the theory, how in mathematical language you can describe the standard deviation function for using it in Excel, for analyzing, for example, sales statistics data, but more on that later. I warn you right away, I will write a lot incomprehensible words...)))), if anything below in the text, look right away practical use in a programme.

What exactly does the standard deviation do? It estimates the standard deviation of a random variable X relative to its mathematical expectation based on an unbiased estimate of its variance. Agree, it sounds confusing, but I think the students will understand what it is all about!

First, we need to determine the "standard deviation" in order to calculate the "standard deviation" in the future, the formula will help us with this: It is possible to describe the formula as follows: it will be measured in the same units as the measurements of the random variable and is used when calculating the standard arithmetic mean error when constructions are made confidence intervals, when testing hypotheses for statistics or when analyzing the linear relationship between independent values. The function is defined as the square root of the variance of independent variables.

Now we can define and standard deviation Is an analysis of the standard deviation of a random variable X in comparison to its mathematical perspective based on an unbiased estimate of its variance. The formula is written like this:
Note that all two estimates are provided biased. In general cases, it is not possible to construct an unbiased estimate. But an estimate based on an estimate of the unbiased variance will be consistent.

Practical implementation in Excel

Well, now let's move away from boring theory and in practice let's see how the STDEV function works. I will not consider all the variations of the standard deviation function in Excel, just one is enough, but in examples. For example, consider how the sales stability statistics are determined.

First, look at the spelling of the function, and as you can see, it is very simple:

STDEV.G (_number1 _; _number2_; ....), Where:

Now let's create an example file and, based on it, consider how this function works. Since for carrying out analytical calculations, it is necessary to use at least three values, as, in principle, in any statistical analysis, then I took 3 periods conditionally, it can be a year, a quarter, a month or a week. In my case, a month. For the greatest reliability, I recommend taking as many periods as possible, but in no way less than three. All the data in the table are very simple for clarity and functionality of the formula.

First, we need to calculate the average by months. We will use the AVERAGE function for this and the formula will be: = AVERAGE (C4: E4).
Now, in fact, we can find the standard deviation using the STDEV.G function in the value of which you need to put down the sales of the goods for each period. The result is a formula of the following form: = STDEV.G (C4; D4; E4).
Well, that's half of the work done. The next step we form the "Variation", this is obtained by dividing by the mean value, standard deviation and the result is converted into percentages. We get the following table:
Well, the main calculations are over, it remains to figure out how the sales are going steadily or not. Let us take as a condition that deviations of 10% are considered stable, from 10 to 25% these are small deviations, but anything above 25% is no longer stable. To obtain the result according to the conditions, we will use the logical one and to obtain the result, we will write the formula:

IF (H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

All ranges are taken conditionally for clarity, your tasks may have completely different conditions.
To improve the visualization of data, when your table has thousands of positions, it is worth taking the opportunity to impose certain conditions that you need or use to highlight certain options with a color gamut, this will be very clear.

First, select which conditional formatting will be applied to. In the control panel "Home" select "Conditional formatting" and in the drop-down menu item "Selection rules" and then click the menu item "Text contains ...". A dialog box appears in which you enter your conditions.

After we have prescribed the conditions, for example, "stable" - green, "normal" - yellow, and "not stable" - red, we get a nice and understandable table in which you can see what to pay attention to in the first place.

Using VBA for the STDEV.G function

Anyone interested can automate their calculations using macros and use the following function:

Function MyStDevP (Arr) Dim x, aCnt &, aSum #, aAver #, tmp # For Each x In Arr aSum = aSum + x "calculate the sum of array elements aCnt = aCnt + 1" calculate the number of elements Next x aAver = aSum / aCnt "average value For Each x In Arr tmp = tmp + (x - aAver) ^ 2" calculate the sum of the squares of the difference between array elements and the average value Next x MyStDevP = Sqr (tmp / aCnt) "calculate STDEV.G () End Function

Function MyStDevP (Arr) Dim x, aCnt &, aSum #, aAver #, tmp # For Each x In Arr aSum = aSum + x "calculate the sum of the array elements