Variance Calculations 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.