In the earlier blog, we covered the **STDEV.P function**. VAR.P Function in excel is closely related to it. VAR.P is an essential type of statistical function of excel. So let’s start learning the VAR.P formula.

Here we go 😊.

**When to Use VAR.P Function of Excel?**

The word VAR.P represents Variance of Population. So you guessed correctly! VAR.P Function calculates the population variance. Variance is calculated for different values from the population set. The population set contains homogenous numerical values. Variance depicts how much the values are dispersed from the **mean value**.

Data with less variance is more consistent when compared to data with a high Variance value.

**How is Population Variance Calculated?**

Let us suppose we have a population containing n numerical values represented by x_{1},x_{2},x_{3}…… x_{n}.

We will start by calculating the **average **value of x.

μ=(x_{1}+x_{2}+x_{3}……+ x_{n})/n

Thereafter, we will calculate the deviation of each numerical value x_{1},x_{2},x_{3}…… x_{n} from the population mean μ.

(x_{1}– μ), (x_{2}– μ) , (x_{3}– μ) ……….(x_{n}-μ)

The sum of the square of these deviations, when divided by n ( total number of numerical values ) gives the value of variance.

Where σ^{2} represents variance.

The square root of population variance gives the** standard deviation of a population**.

We can perform all these calculations in a few clicks with the help of the VAR.P Formula.

**Syntax and Arguments**

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

The following points would explain to you the inputs required by VAR.P Function.

– This is a numerical value of the population set. It can be a cell reference or a range.*number1*This is an optional VAR.P input argument.*[number2] –*

VAR.P Function can have 255 such total number arguments. Out of which only the first *number1 *argument is mandatory to supply.

**Important Points about VAR.P Function**

One must remember the following points about the VAR.P function for successful implementation.

- The square of standard deviation gives the value of variance. The result of the
**STDEV.P**function when raised to power 2 gives the result same as VAR.P Formula for the same inputs - If the Data represents any sample, use VAR.S Function.
- VAR.P only evaluates numbers in references, ignoring empty cells, text, and logical values like TRUE or FALSE.
- The function was introduced in Excel 2010. Use the VAR function for earlier versions of excel.
- Use the
**VAR.S Function**when the data represents a sample.

**Examples to Implement the VAR.P Function**

In this part of the blog, we would perform the experimentation with the VAR.P formula.

**Example 1 – Calculating Population Variance using Hard Coded numbers**

We will start implementing the formula for direct numerical values.

Let us suppose we have the population set containing 5 numbers as {1,2,3,4,5,9}

Use the following VAR.P Formula to get the variance of this population.

=VAR.P(1,2,3,4,5,9)

Alternatively, you can also supply the population set as an array of numbers in brackets.

=VAR.P({1,2,3,4,5,9})

As a result, the formula returns the value of variance as 6.67

**Explanation – **In the *first formula,* we have passed the numbers 1,2,3,4,5 and 9 as the *number1, number2, number3, number4, number5, *and *number6* function argument of VAR.P Function.

We have alternatively, passed an array of numbers {1,2,3,4,5,9} as the *number1 *argument of the VAR.P formula,

In both cases, the VAR.P Function evaluates the variance which comes out to be 6.67

**Example 2 – Calculating Variance of Sales using VAR.P Formula**

Let us suppose we have the monthly sales of a business for five years.

Here we have the sales for 2015, 2016, 2017, 2018 and 2019.

We want to know the year with the most inconsistent sales

We can find this by calculating the variance of the five years sales.

Use the following formula in cell B14.

=VAR.P(B2:B13)

As a result, the formula calculated the Variance of Sales of 2015 as 59441.7

**Explanation – **We have passed the sales of 2015 as the *number1 argument of the *VAR.P Formula.

Range B2:B13 contains the sales for the year 2015.

Similarly, we can calculate the sales variance for 2016, 2017, 2018 and 2019.

In comparison, we find that the sales variance for the year 2016 is the highest.

Therefore, the sales of the year 2016 are most inconsistent.

This brings us to the end of the VAR.P function blog.

Thank you for reading.