How to Calculate Variance with Microsoft Excel

Excel provides useful statistical functions for finding the variance of a data set. In general, these variance functions retrieve a set of values stored in a worksheet range and then make the expected calculation.

A variance, just to remind readers, is a common measure of describing the spread of observations in a distribution. A variance is related to another statistical measure, the standard deviation. A variance is equal to the square of the standard deviation.

Variance of a Sample with the VAR Function

If the data set you’re working with is a sample and you do not want to include logical values or text from the set in the calculation, you use the VAR function. For example, if you’re using a new production process that is supposed to increase productivity and have a series of data for the numbers of parts produced each day, you can find the sample variance. The VAR function uses the following syntax:

=VAR (data set range)

Variance of a Sample with the VARA Function

If the data set is a sample but you want to include logical values or text in the calculation, you use the VARA function. Excel counts cells containing the logical value TRUE as 1 and cells containing text or FALSE 0. The VARA function uses the following syntax:

=VARA (data set range)

Variance of a Population Sample with the VARP Function

If the data set you’re working with is a population and you do not want to include logical values or text from the set in the calculation, you use the VARP function. The VARP function uses the following syntax:

=VARP (data set range)

Variance of a Population with the VARPA Function

If the data set is a population but you want to include logical values or text in the calculation, you use the VARPA function. The VARPA function uses the following syntax:

=VARPA (data set range)

A Final Note About the Data Set Range Argument

One other note: You can include multiple worksheet ranges as your data set range argument when using the variance functions. For example, if you were calculating the variance of a sample using the VAR function and had your data stored in several different worksheet ranges, your VAR function might look like this:

=VAR(B1:B:10,B101:B110,B200)

Small business incorporation and limited liability corporation CPA Stephen L. Nelson has written do-it-yourself limited liability kits for all fifty states. He holds an MBA and MS in tax.

Did you like this post? Can we improve it in any way? What do you think? Please feel free to share your mind…