Excel VAR.P Function
Excel VAR.P Function calculates variance of entire population, ignoring empty cells, text and logical values found in cell references of population data.
The syntax for Excel VAR.P Function is:
255 arguments in total can be added in Excel VAR.P Function.
number1 (required argument) – This is first argument of population data.
[number2],… (optional arguments) – These are additional arguments of population data.
Excel VAR.P Function is introduced to replace Excel VARP Function in Excel 2010 and later versions. It is improved and more accurate version than Excel VARP Function categorized under Statistical function. With the assumptions that data represents the entire population. Arguments can be added as numbers, named ranges, arrays or references in Excel VAR.P Function.
Arguments can contain numbers, text values, logical values and empty cells. But Excel VAR.P Function evaluates only numeric values, ignoring empty cells, text and logical values (TRUE and FALSE). However, Excel VAR.P Function evaluates logical values and text representations of numbers, if added directly as arguments in function.
Suppose you have monthly sales figures over three years, 2015, 2016, 2017 and you want to calculate variance of three years’ sales figures.
As data of three years’ sales figures represents entire population and you want to ignore text, logical values and empty cells, if found in data, so the formula to calculate variance will be as follows:
Figure 1. VAR.P_Function
Following points must be followed regarding Excel VAR.P Function.
- Excel VAR.P Function assumes that data set represents entire population. If you have just sample data then Excel VAR.S Function or VAR Function must be used.
- Excel VAR.P Function ignores text and logical values if found in population data. To evaluate these values you should calculate variance by using Excel VARPA Function.
- Excel VAR.P Function evaluates logical values and text representations of numbers only if add directly in function as arguments.
- If text values are entered directly as arguments, Excel VAR.P Function returns #VALUE! error.