Excel VARP Function
Excel VARP function calculates a variance of entire population data, ignoring empty cells, text and logical values present in cell references.
The newer version of Excel VARP function is VAR.P function that is available with more accuracy in Excel 2010 and later versions. Excel VARP function is available for compatibility with Excel 2007 and earlier, but may not be available in future versions of Excel.
The syntax for Excel VARP function is:
It has the following arguments. You can add 1 to 255 arguments in total in Excel VARP function.
Number1 – This is the required argument and the first number of population data.
[number2],.. – These are optional and additional arguments.
Excel VARP function is categorized under Statistical function and assumes that data represents the entire population, not the sample data. Arguments of Excel VARP function can be numbers, named ranges, arrays, or references.
These arguments may contain numbers, empty cells, text and logical values but Excel VARP function ignores empty cells, text and logical values such as TRUE and FALSE, in cell references. However, if you insert logical values and text representations of numbers directly in Excel VARP function then these values are counted.
For example, you have data of subject scores that represents as population data and you want to calculate a variance of this population data, ignoring empty cells, text and logical values in data set.
You can see that empty cell and text value are present in a data table. So, to ignore such values while calculating the variance of a population you need to use the following formula using Excel VARP function.
Figure 1. VARP_Function
Following points must be remembered while using Excel VARP function.
- Excel VARP function assumes that arguments contain entire population data. If data represents a sample of the population, then you must use Excel VAR function to calculate variance.
- If you want to ignore the text and logical values present in population data, then Excel VARP function must be used, otherwise, you may use Excel VARPA function to evaluate text and logical values.
- Empty cells in cell references are ignored.
- If you want to evaluate logical values and text representation of numbers, then you must enter such values directly in Excel VARP function as the argument.
- If you directly enter text value as the argument then Excel VARP function returns #VALUE! error value.