How to calculate standard deviation in excel. Variance and standard deviation in MS EXCEL. Video to help

home

Andrey Lipov If we talk in simple language

, then the standard deviation shows how much the price of an instrument fluctuates over time. That is, the higher this indicator, the greater the volatility or variability of a number of values. Standard deviation

can and should be used to analyze sets of values, since two sets with seemingly the same average may turn out to be completely different in the spread of values.

Example

Let's take two rows of numbers. a) 1,2,3,4,5,6,7,8,9

. Average - 5. St. deviation = 2.7386 b) 20,1,7,1,15,-1,-20,4,18,5

. Average - 5. St. deviation = 12.2066

If you do not keep the entire series of numbers in front of your eyes, then the standard deviation indicator shows that in case “b” the values ​​are much more scattered around their average value.

Roughly speaking, in series “b” the value is 5 plus or minus 12 (on average) - not exact, but it reveals the meaning.

How to calculate standard deviation

To calculate the standard deviation, you can use a formula borrowed from calculating the standard deviation of mutual fund returns:
Here N is the number of quantities,
DOHaverage - the average of all values,

DOH period - value N.

In Excel, the corresponding function is called STANDARDEVAL (or STDEV in the English version of the program).

1. The step-by-step instructions are as follows:
2. Calculate the average for a series of numbers.
3. For each value, determine the difference between the mean and that value.
4. Calculate the sum of the squares of these differences.
5. Divide the resulting sum by the number of numbers in the series. Take Square root

from the number obtained in the last paragraph.

Your friends will find this information useful. Share with them!

Standard deviation is one of those statistical terms in the corporate world that lends credibility to people who manage to pull it off well in a conversation or presentation, while leaving a vague sense of confusion among those who don't know what it is but are too embarrassed to ask. In fact, most managers don't understand the concept of standard deviation and if you are one of them, it's time for you to stop living a lie. In today's article, I'll tell you how this underappreciated statistical measure can help you better understand the data you're working with.

Imagine that you are the owner of two stores. And to avoid losses, it is important to have clear control of stock balances. In an attempt to find out which manager manages inventory better, you decide to analyze the last six weeks of inventory. The average weekly cost of stock for both stores is approximately the same and is about 32 conventional units. At first glance, the average runoff shows that both managers perform similarly.

But if you take a closer look at the activities of the second store, you will be convinced that although the average value is correct, the variability of the stock is very high (from 10 to 58 USD). Thus, we can conclude that the average does not always evaluate the data correctly. This is where standard deviation comes in.

The standard deviation shows how the values ​​are distributed relative to the mean in our . In other words, you can understand how large the spread in runoff is from week to week.

In our example, we used Excel's STDEV function to calculate the standard deviation along with the mean.

In the case of the first manager, the standard deviation was 2. This tells us that each value in the sample, on average, deviates 2 from the mean. Is it good? Let's look at the question from a different angle - a standard deviation of 0 tells us that each value in the sample is equal to its mean (in our case, 32.2). Thus, a standard deviation of 2 is not much different from 0, indicating that most values ​​are close to the mean. The closer the standard deviation is to 0, the more reliable the average. Moreover, a standard deviation close to 0 indicates little variability in the data. That is, a runoff value with a standard deviation of 2 indicates an incredible consistency of the first manager.

In the case of the second store, the standard deviation was 18.9. That is, the cost of runoff on average deviates by 18.9 from the average value from week to week. Crazy spread! The further the standard deviation is from 0, the less accurate the average is. In our case, the figure of 18.9 indicates that the average value (32.8 USD per week) simply cannot be trusted. It also tells us that weekly runoff is highly variable.

This is the concept of standard deviation in a nutshell. Although it does not provide insight into other important statistical measurements (Mode, Median...), in fact, standard deviation plays a crucial role in most statistical calculations. Understanding the principles of standard deviation will shed light on many of your business processes.

How to calculate standard deviation?

So now we know what the standard deviation number says. Let's figure out how it is calculated.

Let's look at the data set from 10 to 70 in increments of 10. As you can see, I've already calculated the standard deviation value for them using the STANDARDEV function in cell H2 (in orange).

Below are the steps Excel takes to arrive at 21.6.

Please note that all calculations are visualized for better understanding. In fact, in Excel, the calculation happens instantly, leaving all the steps behind the scenes.

First, Excel finds the sample mean. In our case, the average turned out to be 40, which in the next step is subtracted from each sample value. Each difference obtained is squared and summed up. We got a sum equal to 2800, which must be divided by the number of sample elements minus 1. Since we have 7 elements, it turns out that we need to divide 2800 by 6. From the result obtained we find the square root, this figure will be the standard deviation.

For those who are not entirely clear about the principle of calculating the standard deviation using visualization, I give a mathematical interpretation of finding this value.

Functions for calculating standard deviation in Excel

Excel has several types of standard deviation formulas. All you have to do is type =STDEV and you will see for yourself.

It is worth noting that the STDEV.V and STDEV.G functions (the first and second functions in the list) duplicate the STDEV and STDEV functions (the fifth and sixth functions in the list), respectively, which were retained for compatibility with earlier versions of Excel.

In general, the difference in the endings of the .B and .G functions indicate the principle of calculating the standard deviation of a sample or population. I already explained the difference between these two arrays in the previous one.

A special feature of the STANDARDEV and STANDDREV functions (the third and fourth functions in the list) is that when calculating the standard deviation of an array, logical and text values ​​are taken into account. Text and true boolean values are 1, and false booleans are 0. I can't imagine a situation where I would need these two functions, so I think they can be ignored.

Standard deviation is a classic indicator of variability from descriptive statistics.

Standard deviation, standard deviation, standard deviation, sample standard deviation (eng. standard deviation, STD, STDev) - a very common indicator of dispersion in descriptive statistics. But, because technical analysis is akin to statistics, this indicator can (and should) be used in technical analysis to detect the degree of dispersion of the price of the analyzed instrument over time. Denoted by the Greek symbol Sigma "σ".

Thanks to Karl Gauss and Pearson for allowing us to use standard deviation.

Using standard deviation in technical analysis, we turn this "dispersion index"" V "volatility indicator“, maintaining the meaning, but changing the terms.

What is standard deviation

But besides the intermediate auxiliary calculations, standard deviation is quite acceptable for independent calculation and applications in technical analysis. As an active reader of our magazine burdock noted, “ I still don’t understand why the standard deviation is not included in the set of standard indicators of domestic dealing centers«.

Really, standard deviation can measure the variability of an instrument in a classic and “pure” way. But unfortunately, this indicator is not so common in securities analysis.

Applying standard deviation

Manually calculating the standard deviation is not very interesting, but useful for experience. Standard deviation can be expressed formula STD=√[(∑(x-x ) 2)/n] , which sounds like the root of the sum of squared differences between the elements of the sample and the mean, divided by the number of elements in the sample.

If the number of elements in the sample exceeds 30, then the denominator of the fraction under the root takes the value n-1. Otherwise n is used.

Step by step standard deviation calculation:

1. calculate the arithmetic mean of the data sample
2. subtract this average from each sample element
3. we square all the resulting differences
4. sum up all the resulting squares
5. divide the resulting amount by the number of elements in the sample (or by n-1, if n>30)
6. calculate the square root of the resulting quotient (called dispersion)

Management intervention is necessary to identify the causes of deviations.

To construct the control chart, I use the raw data, mean (μ) and standard deviation (σ). In Excel: μ = AVERAGE($F$3:$F$15), σ = STANDARDEVAL($F$3:$F$15)

The control chart itself includes: raw data, mean (μ), lower control limit (μ – 2σ) and upper control limit (μ + 2σ):

Download the note in format, examples in format

Looking at the presented map, I noticed that the source data shows a very distinct linear trend towards a decrease in the share of overhead costs:

To add a trend line, select a row with data on the chart (in our example, green dots), right-click and select the “Add trend line” option. In the Trendline Format window that opens, experiment with the options. I settled on a linear trend.

If the original data are not scattered around the average value, then describing them with the parameters μ and σ is not entirely correct. For description, instead of the average value, a linear trend line and control boundaries equidistant from this trend line are better suited.

Excel allows you to build a trend line using the FORECAST function. We need an additional row A3:A15 to known values X were a continuous series (block numbers do not form such a continuous series). Instead of the average value in column H, we introduce the FORECAST function:

The standard deviation σ (STDEVAL function in Excel) is calculated using the formula:

Unfortunately, I did not find a function in Excel to determine the standard deviation (with respect to the trend) in this way. The problem can be solved using an array formula. For those who are not familiar with array formulas, I suggest reading them first.

An array formula can return a single value or an array. In our case, the array formula will return one value:

Let's take a closer look at how the array formula works in cell G3

SUM(($F$3:$F$15-$H$3:$H$15)^2) determines the sum of squared differences; in fact the formula calculates the following sum = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2

COUNTA($F$3:$F$15) – number of values ​​in the range F3:F15

SQRT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNTA($F$3:$F$15)-1)) = σ

The value of 6.2% is the point of the lower control limit = 8.3% – 2 σ

The curly quotes on either side of the formula indicate that it is an array formula. To create an array formula, after entering the formula in cell G3:

H4 – 2*ROOT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNT($F$3:$F$15)-1))

you need to press not Enter, but Ctrl + Shift + Enter. Do not try to enter curly braces from the keyboard - the array formula will not work. If you need to edit an array formula, do it in the same way as with a regular formula, but again, when you finish editing, press Ctrl + Shift + Enter rather than Enter.

An array formula that returns a single value can be "dragged" like a regular formula.

As a result, we received a control chart built for data tending to decrease

P.S. After the note was written, I was able to refine the formulas used to calculate the standard deviation for trending data. You can view them in the Excel file