Dispersion is a measure of dispersion that describes the comparative deviation between data values and the mean. It is the most used measure of dispersion in statistics, calculated by summing and squaring the deviation of each data value from the mean. The formula for calculating variance is given below:
s 2 – sample variance;
x av—sample mean;
n — sample size (number of data values),
(x i – x avg) is the deviation from the average value for each value of the data set.
To better understand the formula, let's look at an example. I don’t really like cooking, so I rarely do it. However, in order not to starve, from time to time I have to go to the stove to implement the plan of saturating my body with proteins, fats and carbohydrates. The data set below shows how many times Renat cooks every month:
The first step in calculating variance is to determine the sample mean, which in our example is 7.8 times per month. The rest of the calculations can be made easier using the following table.
The final phase of calculating variance looks like this:
For those who like to do all the calculations in one go, the equation would look like this:
Using the raw counting method (cooking example)
There is a more efficient way to calculate variance, known as the raw count method. Although the equation may seem quite cumbersome at first glance, it is actually not that scary. You can make sure of this, and then decide which method you like best.
is the sum of each data value after squaring,
is the square of the sum of all data values.
Don't lose your mind right now. Let's put this all into a table and you will see that there are fewer calculations here than in the previous example.
As you can see, the result was the same as when using the previous method. The advantages of this method become apparent as the sample size (n) increases.
Variance calculation in Excel
As you probably already guessed, Excel has a formula that allows you to calculate variance. Moreover, starting with Excel 2010, you can find 4 types of variance formula:
1) VARIANCE.V – Returns the variance of the sample. Boolean values and text are ignored.
2) DISP.G - Returns the variance of the population. Boolean values and text are ignored.
3) VARIANCE - Returns the variance of the sample, taking into account Boolean and text values.
4) VARIANCE - Returns the variance of the population, taking into account logical and text values.
First, let's understand the difference between a sample and a population. The purpose of descriptive statistics is to summarize or display data so that you quickly get the big picture, an overview so to speak. Statistical inference allows you to make inferences about a population based on a sample of data from that population. The population represents all possible outcomes or measurements that are of interest to us. A sample is a subset of a population.
For example, we are interested in a group of students from one of the Russian universities and we need to determine the average score of the group. We can calculate the average performance of students, and then the resulting figure will be a parameter, since the whole population will be involved in our calculations. However, if we want to calculate the GPA of all students in our country, then this group will be our sample.
The difference in the formula for calculating variance between a sample and a population is the denominator. Where for the sample it will be equal to (n-1), and for the general population only n.
Now let's look at the functions for calculating variance with endings A, the description of which states that text and logical values are taken into account in the calculation. In this case, when calculating the variance of a particular data set where non-numeric values occur, Excel will interpret text and false Boolean values as equal to 0, and true Boolean values as equal to 1.
So, if you have a data array, calculating its variance will not be difficult using one of the Excel functions listed above.
Statistics uses a huge number of indicators, and one of them is calculating variance in Excel. If you do this yourself manually, it will take a lot of time and you can make a lot of mistakes. Today we'll look at how to break down mathematical formulas into simple functions. Let's look at some of the simplest, fastest and most convenient calculation methods that will allow you to do everything in a matter of minutes.
Calculate variance
The variance of a random variable is the mathematical expectation of the squared deviation of a random variable from its mathematical expectation.
We calculate based on the general population
To calculate mat. Waiting for the program to use the DISP.G function, and its syntax looks like this: “=DISP.G(Number1;Number2;…)”.
A maximum of 255 arguments can be used, no more. Arguments can be prime numbers or references to the cells in which they are specified. Let's look at how to calculate variance in Microsoft Excel:
1. The first step is to select the cell where the calculation result will be displayed, and then click on the “Insert Function” button.
2. The function management shell will open. There you need to look for the “DISP.G” function, which can be in the “Statistical” or “Full alphabetical list” category. When it is found, select it and click “OK”.
3. A window with the function arguments will open. In it you need to select the line “Number 1” and on the sheet select the range of cells with the number series.
4. After this, the calculation results will be displayed in the cell where the function was entered.
This is how you can easily find variance in Excel.
We make calculations based on the sample
In this case, the sample variance in Excel is calculated with the denominator indicating not the total number of numbers, but one less. This is done for a smaller error using the special function DISP.V, the syntax of which is =DISP.V(Number1;Number2;...). Algorithm of actions:
- As in the previous method, you need to select a cell for the result.
- In the Function Wizard, you should find “DISP.B” under the “Full Alphabetical List” or “Statistical” category.
- Next, a window will appear, and you should proceed in the same way as in the previous method.
Video: Calculating variance in Excel
Conclusion
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 calculating it can take a lot of time and effort.
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 standard deviation formula in Excel is, 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 the square root obtained from the arithmetic mean of all squared differences between the available quantities, 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 essence of this concept is to identify the degree of variability of an instrument, that is, it is, in its own way, an indicator derived 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
The formula that is automatically provided in Excel helps to calculate the standard deviation 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.
Video to help
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 in the fastest way, just 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 with the left mouse button and Excel will automatically fill in the empty cells with the formula and will determine the range D2:D5, which needs to be filled up to cell D5 and no more. This is a very handy Excel life hack.
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 result of using the AVERAGEIF function under the condition ">=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
There are standard deviations 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.