How Do You Calculate Variance in Excel?
There are two basic ways of calculating variance in Excel using the function VAR or VAR.S. These functions can then calculate variance in several ways: use of numbers in arguments of function =VAR(2,3,4,5,6,7,8,125), use of cells as arguments in the formula =VAR(A2,A3,A4,A5), use of a range of cells as arguments in a formula =VAR(A2:A10,B4:C10), and a mix of above examples in one formula =VAR(A2:A10,B4,5,7).
VAR and VAR.S functions can be used to calculate variance for a sample of values. VAR is the most common function, which can be used to calculate variance in virtually all Excel versions. VAR.S is the latest version available in Excel 2010 and calculates the sample variance of a supplied group of values.
Variance is a statistical measure of variation. It measures dispersion in a set of data. This implies that the greater the variance the bigger the difference between individual numbers in a given set of numbers. Mathematically, variance is the average squared difference between each observation (each number) and the mean of the entire data set. It is the average squared deviation from the mean.
There are four steps involved in calculating variance: the mean, deviation, squared and average. The mean is generally the central value of a data set. The most commonly known mean is the arithmetic average, which is calculated by summing up all the numbers and then dividing the sum by the count of numbers used. For instance, the total of the numbers 10, 20, 30, 40, 50, is 10+20+30+40+50=150. Dividing this by the count of numbers (five) gives the arithmetic average, which is 30. Deviations from the mean are calculated by subtracting the mean from each number, which are then squared to get rid of any negative numbers. The final step is calculating variance as average of squared deviations.
