Excel VAR.P Function

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.

Formula

The syntax for Excel VAR.P Function is:

=VAR.P(number1,[number2],...)

 

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.

Explanation

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.

Example

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:

=VAR.P(B2:B13,C2:C13,D2:D13)

Figure 1. VAR.P_Function

Notes

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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar