Variance is a commonly used metric in statistics, and a lot of Excel users need to calculate it as a part of their work.
Many people working with financial data also need to calculate and use variance for their analysis.
In this tutorial, I will share some ways you can use Excel formulas to calculate variance in Excel.
But before that, let’s quickly see what Variance means
What is Variance? An Easy Explanation
Suppose you have a data set containing a set of values. Now we can very easily calculate the average or mean of this data set.
The variance is how far each individual value is away from the mean value. In statistical terms, it is defined as the spread between numbers in a data set.
We see the usage of variance in our daily lives.
In a stock market, if there is an average 7% annual return on investment over five years, this does not mean that the return is 7% every year. It could have been higher or lower, but at the end of 5 years, the return value was 7%.
How much the return varied every year (when compared with the 7% average after five years) is the variance.
Variance can also be used for comparing the scores of different classes.
For example, two different classes may have the same average marks. But in the class with low variance, more students scored close to the average of the total score of the class.
There are two types of variances that can be calculated in Excel
- Sample Variance and
- Population Variance
Sample Variance is used when you want to calculate the variance of a small data set from a large data set.
Population Variance is used when you want to calculate the variance of the entire population or data set.
Also read: How to Calculate Covariance in Excel?
Calculating Variance in Excel (Formulas)
There are a total of six functions that are available in Excel to calculate Variance.
Since the variance can be calculated for a sample as well as a population, there are three variance formulas for each:
- Formulas for calculating Variance for a sample – VAR, VAR.S, and VARA
- Formulas for calculating Variance for a population – VARP, VAR.P, and VARPA
Out of these six functions, two functions (VAR & VARP) are outdated and are present for compatibility with older spreadsheets. They are likely to be removed in future Excel versions.
So in this article, we will look at the remaining four variance functions and understand how they work.
Method 1: Using the VAR.S Function
In this method, we will look at how to calculate the variance using the VAR.S function.
This function only accepts numerical values.
The letter S in the name of the function signifies that this function calculates the sample variance.
As an example, let’s take a look at the following data set, which shows the marks obtained by students in a test.
Our goal will be to calculate the sample variance of the marks obtained.
Select any cell where you want to calculate the variance. I am going to select cell C2.
To calculate the sample variance for a data set containing only numerical values, the formula is:
VAR.S(Range)
In this case, the range is from cell B2 to B15. So the formula will become:
VAR.S(B2:B15)
Enter the formula as shown.
You can see that the sample variance has been calculated in cell C2.
So in this method, we have seen how to calculate the sample variance from a given data set that contains only numerical values.
Method 2: Using the VAR.P Function
In this method, we will look at how to calculate the variance using the VAR.P function.
As explained earlier, the VAR.P function is used when you want to calculate Population Variance.
You can remember this by the letter P in the name of the function.
Similar to the VAR.S function, the VAR.P function only accepts numerical values.
For demonstration, let’s take a look at the following data set, which we have used in the first method as well.
Our goal will be to calculate the population variance of the marks obtained.
Select cell C2. This will be the cell where we will calculate the variance.
To calculate the population variance for a data set containing only numerical values, the formula is:
VAR.P(Range)
In this case, the range is from cell B2 to B15. So the formula will become:
VAR.P(B2:B15)
Write the formula in Excel as shown below
You can see that the population variance has been calculated in cell C2.
So in this method, we have seen how to calculate the population variance from a given data set that contains only numerical values.
Method 3: Using the VARA Function
In this method, we will look at how to calculate the variance using the VARA function.
Similar to the VAR.S function, this function is used to calculate Sample Variance.
However, as opposed to the VAR.S function, the VARA function accepts both numerical as well as text or logical values.
To demonstrate the use of this function, let’s take a look at the following data set, which shows the marks obtained by students in a test.
One of the students (Samantha Stern) was marked as absent.
This is a text value. Our goal will be to calculate the sample variance of the marks obtained, including the data value for the absent student.
Select any cell where you want to calculate the variance. I am going to select cell C2.
To calculate the sample variance for a data set containing both text and numerical values, the formula is:
=VARA(Range)
In this case, the range is from cell B2 to B15. So the formula will become:
VARA(B2:B15)
- Enter the formula as shown.
You can see that the sample variance has been calculated in cell C2.
In this method, we have seen how to calculate the sample variance from a given data set that contains both text and numerical values.
Method 4: Using the VARPA Function
In this method, we will look at how to calculate the variance using the VARPA function.
This function calculates the Population Variance and accepts both numerical as well as text or logical values.
Let’s see an example of using this function. We will use the same data set that was used in the previous method.
One of the students (Samantha Stern) has been marked as absent, which is a text value.
Let’s calculate the population variance, including the data value for the absent student.
First, select the cell where you want the results, which is C2 in this case.
To calculate the population variance for a data set containing both text and numerical values, the formula is as follows:
=VARPA(Range)
In this case, the range is from cell B2 to B15. So the formula will become:
=VARPA(B2:B15)
- Enter the formula as shown.
You can see that the population variance has been calculated in cell C2.
We have seen how to calculate the population variance from a given data set that contains both text and numerical values.
What is the difference Between Sample and Population in Statistics?
Population and sample are two important terms in the field of statistics.
Population refers to the entire dataset that you’re using for the calculation. For example, if you are doing calculations about the scores of students in a school, the population would be the scores of all the students in the school.
On the other hand, a sample is a part of the overall population where we perform our calculation only on the sample and extrapolate it for the population. This is usually the case when the population is quite large, and it would be hard to do the analysis for the entire population.
The main difference between population and sample lies in their size; while a population is typically very large, a sample consists of only some members from it.
In conclusion, we can say that while the population dataset consists of all elements relevant to research, samples represent specific sections within the population, which statisticians use as representative models to determine certain characteristics pertaining to larger groups they belong to.
In this article, we have seen four Variance functions available in Excel and how to use them.
If you want to calculate the sample variance of a data set that contains only numerical values, then use Method 1. If your data set has both numerical values and text, then use Method 3 to calculate the sample variance.
If you want to calculate the population variance of a data set that contains only numerical values, then use Method 2. If your data set has both numerical values and text, then use Method 4 to calculate the population variance.
Other Excel articles you may also like:
I would love to understand how to generate a meaningful graph in Excel that shows variance of a data set together with the mean.
Regards
Angelo R Bobak