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 Carl 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 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:
- calculate the arithmetic mean of the data sample
- subtract this average from each sample element
- we square all the resulting differences
- sum up all the resulting squares
- divide the resulting amount by the number of elements in the sample (or by n-1, if n>30)
- calculate the square root of the resulting quotient (called dispersion)
In this article I will talk about how to find standard deviation. This material is extremely important for a full understanding of mathematics, so a math tutor should devote a separate lesson or even several to studying it. In this article you will find a link to a detailed and understandable video tutorial that explains what standard deviation is and how to find it.
Standard deviation makes it possible to evaluate the spread of values obtained as a result of measuring a certain parameter. Indicated by the symbol (Greek letter "sigma").
The formula for calculation is quite simple. To find the standard deviation, you need to take the square root of the variance. So now you have to ask, “What is variance?”
What is variance
The definition of variance goes like this. Dispersion 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 average of a series of values).
- Then subtract the average from each value and square the resulting difference (you get squared difference).
- The next step is to calculate the arithmetic mean of the resulting squared 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 received 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 let's find the average value. As you already know, to do this you need to add up all the measured values and divide by the number of measurements. Calculation progress:
Average mm.
So, the average (arithmetic mean) is 394 mm.
Now we need to determine deviation of the height of each dog from the average:
Finally, to calculate variance, we square each of the resulting differences, and then find the arithmetic mean of the results obtained:
Dispersion mm 2 .
Thus, the dispersion is 21704 mm 2.
How to find standard deviation
So how can we now calculate the standard deviation, knowing the variance? As we remember, take the square root of it. That is, the standard deviation is equal to:
Mm (rounded to the nearest whole number in mm).
Using this method, we found that some dogs (for example, Rottweilers) are very large dogs. But there are also very small dogs (for example, dachshunds, but you shouldn’t tell them that).
The most interesting thing is that the standard deviation carries useful information. Now we can show which of the obtained height measurement results are within the interval that we get if we plot the standard deviation from the average (to both sides of it).
That is, using the standard deviation, we obtain a “standard” method that allows us to find out which of the values is normal (statistically average), and which is extraordinarily large or, conversely, small.
What is standard deviation
But... everything will be a little different if we analyze sample 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 selected from a large population), then the calculations need to be done differently.
If there are values, then:
All other calculations are carried out similarly, including the determination of the average.
For example, if our five dogs are just a sample of the population of dogs (all dogs on the planet), we must divide by 4, not 5, namely:
Sample variance = 
mm 2.
In this case, the standard deviation for the sample is equal to 
mm (rounded to the nearest whole number).
We can say that we have made some “correction” in the case where our values are just a small sample.
Note. Why exactly squared differences?
But why do we take exactly the squared differences when calculating the variance? Let's say when measuring some parameter, you received the following set of values: 4; 4; -4; -4. If we simply add the absolute deviations from the average (differences) together... the negative values cancel out with the positive ones:
.
It turns out that this option is useless. Then maybe it’s 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 mean absolute deviation), but not in all cases. Let's try another example. Let the measurement result in the following set of values: 7; 1; -6; -2. Then the average absolute deviation is:
Wow! Again we got a result of 4, although the differences have a much larger spread.
Now let's see what happens if we square the differences (and then take the square root of their sum).
For the first example it will be:
.
For the second example it will be:
Now it’s a completely different matter! The greater the spread of the differences, the greater the standard deviation... which is what we were aiming for.
In fact, this method uses the same idea as when calculating the distance between points, only applied in a different way.
And from a mathematical point of view, using squares and square roots provides more benefits than we could get from absolute deviation values, making standard deviation applicable to other mathematical problems.
Sergey Valerievich told you how to find the standard deviation
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.
What does standard deviation measure?
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 amounts to 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 this 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 the essence of many processes in your business.
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 steps of 10. As you can see, I have 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 boolean values are 0. I can't imagine a situation where I would need these two functions, so I think they can be ignored.
The STDEV.B function returns the standard deviation calculated over a specified range of numeric values.
The STDEV.G function is used to determine the standard deviation of a population of numeric values and returns the value of the standard deviation, assuming that the values passed are the entire population and not a sample.
The STANDARDEV function returns the standard deviation value for a certain range of numbers, which is a sample and not the entire population.
The STD function returns the standard deviation of the entire population passed as its arguments.
Examples of using STDEV.V, STDEV.G, STDEV and STDEV
Example 1. An enterprise employs two customer acquisition managers. Data on the number of clients served per day by each manager is recorded in an Excel table. Determine which of the two employees works more efficiently.
Source data table:
First, let’s calculate the average number of clients with whom managers worked daily:
AVERAGE(B2:B11)
This function calculates the arithmetic average for the range B2:B11, which contains data on the number of clients accepted daily by the first manager. Similarly, we calculate the average number of clients per day for the second manager. We get:
Based on the obtained values, it seems that both managers are working approximately equally efficiently. However, a strong dispersion in the number of clients for the first manager is visually visible. Let's calculate the standard deviation using the formula:
STDEV.B(B2:B11)
B2:B11 – range of studied values. Similarly, we determine the standard deviation for the second manager and obtain the following results:
As you can see, the performance indicators of the first manager are characterized by high variability (scatter) of values, and therefore the arithmetic average absolutely does not reflect the real picture of performance. A deviation of 1.2 indicates more stable, and, therefore, effective work of the second manager.
Example of using the STANDARDEV function in Excel
Example 2. Two different groups of college students were given an exam in the same discipline. Assess student performance.
Source data table:
Let's determine the standard deviation of values for the first group using the formula:
STDEV(A2:A11)
We will make a similar calculation for the second group. As a result we get:
The obtained values indicate that the students of the second group were much better prepared for the exam, since the spread of grades is relatively small. Note that the STANDARDEV function converts the text value "failed" to the numeric value 0 (zero) and takes it into account in the calculation.
Example of the STANDARDEV.G function in Excel
Example 3. Determine the effectiveness of preparing students for the exam for all groups of the university.
Note: unlike the previous example, not a sample (several groups) will be analyzed, but the entire number of students - the general population. Students who do not pass the exam are not taken into account.
Let's fill out the data table:
To assess effectiveness, we will operate with two indicators: the average score and the spread of values. To determine the arithmetic mean we use the function:
AVERAGE(B2:B21)
To determine the deviation, we introduce the formula:
STDEV.G(B2:B21)
As a result we get:
The data obtained indicates academic performance slightly below average (<4), величина разброса характеризует довольно большое количество студентов, получивших 5 и 3 соответственно (учитывая, что анализировались только данные из диапазона от 3 до 5).
Example of the standard deviation function in Excel
Example 4. Analyze the performance of students based on the exam results, taking into account those students who failed to pass this exam.
Data table:
In this example, we are also analyzing the population, but some data fields contain text values. To determine the standard deviation we use the function:
STDEV(B2:B21)
As a result we get:
A high spread of values in the sequence indicates a large number of students who failed the exam.
Features of using STDEV.V, STDEV.G, STDEV and STDEV
The STDEV and STDEV functions have identical syntax like:
FUNCTION (value1; [value2];…)
Description:
- FUNCTION – one of the two functions discussed above;
- value1 – a required argument characterizing one of the values of the sample (or the general population);
- [value2] – an optional argument characterizing the second value of the range under study.
Notes:
- Function arguments can include names, numeric values, arrays, references to ranges of numeric data, Boolean values, and references to them.
- Both functions ignore empty values and text data contained in the passed data range.
- The functions return the error code #VALUE! if error values or text data that cannot be converted to numeric values were passed as arguments.
The functions STDEV.V and STDEV.G have the following syntax:
FUNCTION(number1;[number2];…)
Description:
- FUNCTION – any of the functions STANDARDDEVIATION.V or STANDARDDEVIATION.G;
- number1 – a required argument characterizing a numerical value taken from a sample or the entire population;
- number2 – an optional argument characterizing the second numeric value of the range under study.
Note: Both functions do not include numbers represented as text data, or the Boolean values TRUE and FALSE, in the calculation process.
Notes:
- Standard deviation is widely used in statistical calculations when finding the average of a range of values does not provide a true representation of the distribution of the data. It demonstrates the principle of distribution of values relative to the average value in a specific sample or the entire sequence. Example 1 will clearly examine the practical application of this statistical parameter.
- The functions STANDARDEVAL and STANDDEVIATION.B should be used to analyze only part of the population and calculate using the first formula, while STANDARDEV.G and STANDARDEVAL should take input data about the entire population and calculate using the second formula.
- Excel contains built-in functions STDEV and STDEV that are retained for compatibility with older versions of Microsoft Office. They may not be included in later versions of the program, so their use is not recommended.
- To find the standard deviation, two common formulas are used: S=√((∑_(i=1)^n▒(x_i-x_av)^2)/(n-1)) and S=√((∑_(i= 1)^n▒(x_i-x_ср)^2)/n), where:
- S – the desired value of the standard deviation;
- n – considered range of values (sample);
- x_i – individual value from the sample;
- x_avg – arithmetic mean value for the considered range.
Andrey Lipov
In simple terms, 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. Art. 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).
The step-by-step instructions are as follows:
- Calculate the average for a series of numbers.
- For each value, determine the difference between the mean and that value.
- Calculate the sum of the squares of these differences.
- Divide the resulting sum by the number of numbers in the series.
- Take the square root of the number you got in the last step.
Your friends will find this information useful. Share with them!
