Excel VAR Function
Excel VAR function returns variance of sample, ignoring text and logical values supplied as arguments in cell references.
The syntax of Excel VAR function is:
You can enter up to 255 arguments representing sample data set.
number1 – It is required and first argument of sample.
[number2],… – These are optional and additional arguments of sample
Excel VAR function is available for compatibility with Excel 2007 and earlier versions. Excel VAR function has been replaced with Excel VAR.S function that is available in Excel 2010 and later versions, but you can use both function to measure sample variance.
Arguments in Excel VAR function can be supplied as numbers, names, arrays or cell references. These arguments may contain numbers, text values, logical values and empty cells, but Excel VAR function only counts numbers ignoring empty cells, Text and logical values in cell references. Logical values and text representations of numbers supplied directly as argument in Excel VAR function are evaluated to estimate sample variance.
Suppose you have weights of children with 4 years of age as sample in primary schools of a city and you want to measure variance in children’s weights.
As the data set of children’s weight represents a sample and is not the data set of entire population of primary schools in city and you also want to ignore text and logical values in cell references, so the formula to calculate sample variance is;
Figure 1. VAR_Function
Following points guidelines must be followed while using Excel VAR function:
- It returns variance of sample not the entire population. If you want to calculate variance of entire population, use Excel VAR.P function instead.
- Excel VAR function ignores text and logical values in a sample. If you want to include and evaluate these values in variance calculation of sample, then use Excel VARA function.
- Logical values and text representations of numbers are counted in Excel VAR function, if supplied directly as argument.
- Excel VAR function returns #DIV/0! error, if fewer than 2 arguments are supplied.
- It returns #VALUE! error, if text value(s) is supplied directly as argument.