In this article, we will be discussing the different ways you can calculate the variance in Excel. Keep reading to learn more about how you can use Excel to get better at calculations!
Types of Variance
There are two different ways of calculating variance; by sample and population. The formulas vary according to if you want to calculate the variance for the entire population or only a sample of the entire population. In Excel, two different functions are responsible for calculating variance according to population and sample. So, you need to determine if the range you’ve selected is a sample of the whole data or not.
How to Find Variance in Excel?
You can use exactly four functions to calculate variance in Excel; VAR.P, VAR.S, VARPA, and VARA. Each function is quite different from the others and calculates variance differently. Below, we have elaborated on each function and mentioned the format you can follow to use the function in your formula.
Use VAR.P Function
The VAR.P function calculates the variance of an entire population, ignoring the text. You can use this function when no data is missing in your sheets. For instance, you may want to calculate variance in a classroom’s math scores. If you have access to every student’s math scores, use the VAR.P function. Follow this format to use the VAR.P function in your formula: =VAR.P(range)
Use VAR.S Function
You need to use the VAR.S function to calculate a subset’s variance. Like the VAR.P function, this function also ignores texts in its arguments. Using the same example above, you can use the VAR.S function if you’re calculating the variance in the top three high scores. If you feel like the VAR.S function is the right function for you, refer to this format to use it in a formula: =VAR.S(range)
Use VARPA Function
If you have one or more data missing from your sheet, you can use the VARPA function. The VARPA function includes logic and texts while calculating the variance of a population. If a student failed to turn in their math score, you could not calculate the true variance between all student’s scores. In that case, you can use the VARPA function to take the missing score into account. Use this format to use the VARPA function in a formula: =VARPA(range)
Use VARA Function
The VARA function works similarly to the VARPA function. This formula also considers logic and texts from the range while calculating the variance of a sample from a population. You can use this function if you have data missing from your sheets to calculate the true variance between the data set. Refer to this format to use the VARA function in a formula: =VARA(range)
Manually Calculate Variance (Population)
If you do not want to use the functions to calculate variance, you may also manually calculate variance in Excel. Here are the steps you can refer to calculate the variance for a population in MS Excel manually:
Manually Calculate Variance (Sample)
You can also manually calculate the sample variance of a data range in Excel. The steps are similar to calculating populating variance, but you must recalculate the mean, the difference between mean and data, and the squared difference according to your sampled data. While calculating the variance, too, you must subtract the total number of sampled data to remove the bias. Follow these steps to calculate sample variance in Excel:
Other Functions to Calculate Variance
Older versions of Excel support the other variance functions, such as the VARP function and VAR function.
VARP function: It is used to calculate the variance of a population.VAR function: It calculates the variance in a population sample.
These functions have, however, been replaced by the VAR.P and VAR.S functions in the newer versions of Excel. So, until you’re using Excel version 2007 or below, we do not recommend you to use these functions. Formulas with VAR and VARP functions are written in the same way as the VAR.S and VAR.P functions.
