By Stephen Nelson
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)
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.
Comments