Variance Calculations With Microsoft Excel

Excel provides useful statistical functions for finding a variance. 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.

Microsoft Certification Courses

Variance of a Sample with the VAR Function

Variance Calculations With Microsoft Excel

If the data 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 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 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 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)

Variance Calculations With Microsoft Excel

Seattle, Washington CPA Stephen L. Nelson is the author of MBA's Guide to Microsoft Excel, from which this article was adapted. He holds an MBA in finance and MS in tax.

cell phone watches Best Price Art S8 Balanced 8 Channel Microphone