Q:

# How do you calculate variance in Excel?

A:

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).

## Keep Learning

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.

Sources:

## Related Questions

• A: Use the DATE function in Excel to list dates. Use the TODAY function to list the current date or the NOW function to list current date and time.... Full Answer >
Filed Under:
• A: Though there are several functions in Excel for rounding numbers, the ROUND function is the most basic and the most commonly used. Keep in mind that when u... Full Answer >
Filed Under:
• A: To calculate covariance in Excel, use the covariance function. The syntax of the function is: COVARIANCE.P(array1,array2), where array1 and array2 are the ... Full Answer >
Filed Under:
• A: The weighted average function in Excel calculates the average of a group of numbers with one or more of the numbers given more weight or a higher level of ... Full Answer >
Filed Under:
PEOPLE SEARCH FOR