How to calculate standard deviation in Excel. Statistical application. Determining the population mean

home

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

How to calculate percentage deviation in Excel

The percentage deviation is calculated by subtracting the old value from the new value, and then dividing the result by the old value. The result of this formula calculation in Excel should be displayed in cell percentage format. In this example, the calculation formula is as follows (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 markup.

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

It is important to pay attention to the presence of parentheses in this formula. By default, in Excel, the division operation always takes precedence over the subtraction operation. Therefore, if we do not put parentheses, then the value will first be divided, and then another value will be subtracted from it. Such a calculation (without the presence of parentheses) will be erroneous. Closing the first part of a calculation in a formula with parentheses automatically raises the priority of the subtraction operation above the division operation. Enter the formula correctly with parentheses in cell D2, and then simply copy it into the remaining empty cells of the range D2:D5. To copy the formula most in a fast way

Alternative formula for calculating percentage deviation in Excel

In an alternative formula that calculates the relative deviation of sales values ​​from the current year, immediately divide by the sales values ​​of the previous year, and only then one 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, and therefore correct. But the alternative formula is easier to write, although it may be more difficult for some to read in order to understand the principle of its operation. Or it is more difficult to understand what value a given formula produces as a result of a calculation if it is not signed.

The only drawback of this alternative formula is the inability to calculate the percentage deviation for 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 if the number in the substitute is negative.

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.

In order to find the average value in Excel (no matter whether it is a numeric, text, percentage or other value), there are many functions. And each of them has its own characteristics and advantages. Indeed, in this task certain conditions may be set.

For example, the average values ​​of a series of numbers in Excel are calculated using statistical functions. You can also manually enter your own formula. Let's consider various options.

How to find the arithmetic mean of numbers?

To find the arithmetic mean, you need to add up all the numbers in the set and divide the sum by the quantity. For example, a student’s grades in computer science: 3, 4, 3, 5, 5. What is included in the quarter: 4. We found the arithmetic mean using the formula: =(3+4+3+5+5)/5.

How to quickly do this using Excel functions? Let's take for example a series of random numbers in a string:

Or: make the active cell and simply enter the formula manually: =AVERAGE(A1:A8).

Now let's see what else the AVERAGE function can do.

Let's find the arithmetic mean of the first two and last three numbers. Formula: =AVERAGE(A1:B1,F1:H1). Result:

Condition average

The condition for finding the arithmetic mean can be a numerical criterion or a text one. We will use the function: =AVERAGEIF().

Find the arithmetic mean of numbers that are greater than or equal to 10.

Function: =AVERAGEIF(A1:A8,">=10")

The third argument – ​​“Averaging range” – is omitted. First of all, it is not required. Secondly, the range analyzed by the program contains ONLY numeric values. The cells specified in the first argument will be searched according to the condition specified in the second argument.

Attention! The search criterion can be specified in the cell. And make a link to it in the formula.

Let's find the average value of the numbers using the text criterion. For example, the average sales of the product “tables”.

The function will look like this: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Range – a column with product names. The search criterion is a link to a cell with the word “tables” (you can insert the word “tables” instead of link A7). Averaging range – those cells from which data will be taken to calculate the average value.

As a result of calculating the function, we obtain the following value:

Attention! For a text criterion (condition), the averaging range must be specified.

How to calculate the weighted average price in Excel?

How did we find out the weighted average price?

Formula: =SUMPRODUCT(C2:C12,B2:B12)/SUM(C2:C12).

Using the SUMPRODUCT formula, we find out the total revenue after selling the entire quantity of goods. And the SUM function sums up the quantity of goods. By dividing the total revenue from the sale of goods by the total number of units of goods, we found the weighted average price. This indicator takes into account the “weight” of each price. Its share in the total mass of values.

Standard deviation: formula in Excel

Distinguish between average standard deviation for the general population and for the sample. In the first case, this is the root of the general variance. In the second, from the sample variance.

To calculate this statistical indicator, a dispersion formula is compiled. The root is extracted from it. But in Excel there is a ready-made function for finding the standard deviation.

The standard deviation is tied to the scale of the source data. This is not enough for a figurative representation of the variation of the analyzed range. To obtain the relative level of data scatter, the coefficient of variation is calculated:

standard deviation / arithmetic mean

The formula in Excel looks like this:

STDEV (range of values) / AVERAGE (range of values).

The coefficient of variation is calculated as a percentage. Therefore, we set the percentage format in the cell.

The Excel program is highly valued by both professionals and amateurs, because users of any skill level can work with it. For example, anyone with minimal “communication” skills in Excel can draw a simple graph, make a decent plate, etc.

At the same time, this program even allows you to perform various types of calculations, for example, calculations, but this requires a slightly different level of training. However, if you have just begun to become closely acquainted with this program and are interested in everything that will help you become a more advanced user, this article is for you. Today I will tell you what the average is standard deviation formula in Excel, why it is needed at all and, strictly speaking, when it is used. Go!

What it is

Let's start with the theory. The standard deviation is usually called Square root, obtained from the arithmetic mean of all squared differences between the available values, as well as their arithmetic mean.

By the way, this value is usually called the Greek letter “sigma”. The standard deviation is calculated using the STANDARDEVAL formula; accordingly, the program does this for the user itself. The point is this concept

is to identify the degree of variability of the instrument, that is, this is, in its own way, an indicator originally from descriptive statistics. It identifies changes in the volatility of an instrument over a certain time period. The STDEV formulas can be used to estimate the standard deviation of a sample, ignoring Boolean and text values.

Formula Helps calculate the standard deviation in excel formula

, which is automatically provided in Excel. To find it, you need to find the formula section in Excel, and then select the one called STANDARDEVAL, so it’s very simple.

After this, a window will appear in front of you in which you will need to enter data for the calculation. In particular, two numbers should be entered in special fields, after which the program itself will calculate the standard deviation for the sample.

Undoubtedly, mathematical formulas and calculations are a rather complex issue, and not all users can cope with it straight away. However, if you dig a little deeper and look at the issue in a little more detail, it turns out that not everything is so sad. I hope you are convinced of this using the example of calculating the standard deviation.

The standard deviation function is already from the category of higher mathematics related to statistics. There are several options for using Functions in Excel. standard deviation This:

• STANDARDEV function.
• STANDARD DEVIATION function.
• STDEV function

We will need these functions in sales statistics to identify the stability of sales (XYZ analysis). This data can be used both for pricing and for creating (adjusting) the assortment matrix and for other useful sales analyses, which I will definitely talk about in future articles.

Preface

Let's look at the formulas first in mathematical language, and then (below in the text) we will analyze in detail the formula in Excel and how the resulting result is used in the analysis of sales statistics.

So, Standard Deviation is an estimate of the standard deviation of a random variable x regarding its mathematical expectation based on an unbiased estimate of its variance)))) Don’t be afraid of incomprehensible words, be patient and you will understand everything!

Description of the formula: The standard deviation is measured in units of measurement of the random variable itself and is used when calculating the standard error of the arithmetic mean, when constructing confidence intervals, when statistically testing hypotheses, when measuring the linear relationship between random variables. Defined as the square root of the variance of the random variable

Now standard deviation is an estimate of the standard deviation of a random variable x relative to its mathematical expectation based on an unbiased estimate of its variance:

Dispersion;

- i th element of the selection;

Sample size;

Arithmetic mean of the sample:

It should be noted that both estimates are biased. In the general case, it is impossible to construct an unbiased estimate. However, the estimate based on the unbiased variance estimate is consistent.

Three sigma rule() - almost all values ​​of a normally distributed random variable lie in the interval. More strictly, with approximately 0.9973 probability, the value of a normally distributed random variable lies in the specified interval (provided that the value is true and not obtained as a result of sample processing). We will use a rounded interval of 0.1

If the true value is unknown, then you should use not, but s. Thus, the rule of three sigma is transformed into the rule of three s. It is this rule that will help us determine the stability of sales, but more on that later...

Now Standard Deviation Function in Excel

I hope I didn't bore you too much with math? Perhaps to someone this information will be needed for an essay or some other purposes. Now let's look at how these formulas work in Excel...

To determine the stability of sales, we do not need to delve into all the options for the standard deviation functions. We will use just one:

STDEV function

STDEV(number1;number2;... )

Number1, number2,..- from 1 to 30 numeric arguments corresponding to the general population.

Now let's look at an example:

Let's create a book and a makeshift table. You will download this example in Excel at the end of the article.

To be continued!!!

Hello again. Well!? I had a free minute. Let's continue?

And so the stability of sales with the help STDEV functions

For clarity, let’s take a few improvised goods:

In analytics, be it a forecast, research or anything else related to statistics, it is always necessary to take three periods. This could be a week, a month, a quarter or a year. It is possible and even best to take as many periods as possible, but not less than three.

I specifically showed exaggerated sales, where the naked eye can see what is selling consistently and what is not. This will make it easier to understand how the formulas work.

And so we have sales, now we need to calculate the average sales values ​​by period.

The formula for the average value is AVERAGE (period data), in my case the formula looks like this = AVERAGE (C6: E6)

We apply the formula to all products. This can be done by grabbing the right corner of the selected cell and dragging it to the end of the list. Or place the cursor on the column with the product and press the following key combinations:

Ctrl + Down moves the cursor to the top of the list.

Ctrl + Right, the cursor moves to the right side of the table. Once again to the right and we will get to the column with the formula.

Now we clamp

Ctrl + Shift and press up. This way we will select the area where the formula will be drawn.

And the key combination Ctrl + D will drag the function where we need it.

Remember these combinations, they really increase your speed in Excel, especially when you work with large arrays.

The next stage, the standard departure function itself, as I already said, we will use only one STDEV

We write the function and set the sales values ​​of each period in the function values. If you have sales in the table one after another, you can use a range, as in my formula =STDEV(C6:E6) or list the required cells separated by semicolons =STDEV(C6;D6;E6)

Now all the calculations are ready. But how do you know what sells consistently and what doesn’t? Let’s just put the convention XYZ where,

X is stable

Y - with small deviations

Z - not stable

To do this, we use error intervals. if fluctuations occur within 10%, we will assume that sales are stable.

If between 10 and 25 percent, it will be Y.

And if the variation value exceeds 25%, this is not stability.

To correctly set the letters for each product, we will use the IF formula. Learn more about. In my table this function will look like this:

IF(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Accordingly, we extend all the formulas for all names.

I will try to immediately answer the question, Why the intervals of 10% and 25%?

In fact, the intervals may be different, it all depends on the specific task. I specifically showed you exaggerated sales values, where the difference is visible to the eye. Obviously, product 1 is not sold consistently, but the dynamics show an increase in sales. We leave this product alone...

But here is product 2, there is already obvious destabilization. And our calculations show Z, which tells us that sales are not stable. Product 3 and Product 5 show stable performance, please note that the variation is within 10%.

Those. Product 5 with scores of 45, 46 and 45 shows a variation of 1%, which is a stable number series.

But Product 2 with indicators 10, 50 and 5 show a variation of 93%, which is NOT a stable number series.

After all the calculations, you can put a filter and filter out stability, so if your table consists of several thousand items, you can easily identify which ones are not stable in sales or, conversely, which ones are stable.

“Y” didn’t work out in my table, I think for clarity of the number series, it needs to be added. I'll draw Product 6...

You see, the number series 40, 50 and 30 shows 20% variation. There doesn’t seem to be a big error, but the spread is still significant...

And so to summarize:

10.50.5 - Z is not stable. Variation more than 25%

40,50,30 - Y you can pay attention to this product and improve its sales. Variation less than 25% but more than 10%

45,46,45 - X is stability, you don’t need to do anything with this product yet. Variation less than 10%

That's all! I hope I explained everything clearly, if not, ask if it’s not clear. And I will be grateful to you for every comment, be it praise or criticism. This way I will know that you are reading me and that you, which is very IMPORTANT, are interested. And accordingly, new lessons will appear.

One of the main tools of statistical analysis is the calculation of standard deviation. This indicator allows you to estimate the standard deviation for a sample or for a population. Let's learn how to use the standard deviation formula in Excel.

Let’s immediately determine what the standard deviation is and what its formula looks like. This quantity is the square root of the arithmetic mean of the squares of the difference between all quantities in the series and their arithmetic mean. There is an identical name for this indicator - standard deviation. Both names are completely equivalent.

But, naturally, in Excel the user does not have to calculate this, since the program does everything for him. Let's learn how to calculate standard deviation in Excel.

Calculation in Excel

You can calculate the specified value in Excel using two special functions STDEV.V(based on the sample population) and STDEV.G(based on the general population). The principle of their operation is absolutely the same, but they can be called in three ways, which we will discuss below.

Method 1: Function Wizard

Method 2: Formulas Tab

Method 3: Manually entering the formula

There is also a way in which you won't need to call the arguments window at all. To do this, you must enter the formula manually.

As you can see, the mechanism for calculating standard deviation in Excel is very simple. The user only needs to enter numbers from the population or references to the cells that contain them. All calculations are performed by the program itself. It is much more difficult to understand what the calculated indicator is and how the calculation results can be applied in practice. But understanding this already relates more to the field of statistics than to learning to work with software.