VAR.P Excel

What Is VAR.P Excel Function?

The VAR.P Excel function is an inbuilt Statistical function. It computes the variance based on the whole population while not considering the texts and logical values in the specified data.

Users can utilize the VAR.P Excel function to review the risks involved in an investment portfolio and the returns from it. The function also enables users to assess a manufacturing process for factors impacting the process quality. So, the function aids in making informed decisions in the financial and manufacturing domains.

For example, the following dataset holds a list of sales representatives and the units of items they sold.

The aim is to find the population variance, considering given data as the entire population and show the outcome in the target cell B13.

Then, we can use the VAR.P() in the target cell to secure the required result, adhering to the meaning of VAR.P Excel explained earlier.

The VAR.P Excel function accepts the range B2:B11, holding the whole population as the input.

So, the function returns 10.56 as the population variance, which points to the extent of dispersion of the given data around its average value.

What Is VAR.P Excel Function?

The VAR.P Excelfunction computes the population variance for the specified entire population.
Users can use the VAR.P function in Excel in the Finance and Insurance fields. While the function helps assess the risks involved in an investment portfolio, it assists insurance firms in computing the predicted losses according to the insured portfolio.
The VAR.P function in Excel takes one compulsory argument, number1, and between 2 and 254 optional number arguments.
The VAR.P function in Excel returns an incorrect value if the specified dataset is the sample of the whole population. In such a scenario, we must apply the VAR.S function in Excel.

Syntax

The VAR.P Excel function syntax is as follows:

Where,

number1 : The first number value associated with a population.
number2, … : The 2 to 254 number values associated with a population.

While we must supply the first argument when using Var.P Excel, the remaining arguments are optional.

Furthermore, we must know the following keynotes before applying the Var.P Excel function:

The VAR.P() considers that the supplied argument values are the whole population.
The supplied Var.P Excel function arguments can be numbers or names, arrays, or Excel cell references holding the numbers. However, the function considers logical values and text-denoting numbers that we supply directly as argument values.
When a supplied argument value is an array or reference, the function considers only the numbers in the specified range reference. On the other hand, it ignores blank cells, logical values, texts, and error values in the specified range reference.
When the directly provided argument values are error values, or they are texts not translating into numbers, the Var.P Excel returns errors.
The VAR.P()’s mathematical equation is the following:

Where,

x: A value in the given dataset.
x̅: The mean value of the sample.
n: The sample size.

How To Use VAR.P Function In Excel?

We can implement the VAR.P Excel function in the following two ways:

Access the function from the Excel ribbon.
Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Select a cell for showcasing the output → The Formulas tab → The More Functions group down arrow → The Statistical function group right arrow → The VAR.P Excel function.

VAR.P Excel - Access from the Excel ribbon

The Function Arguments window will appear, where we feed the Var.P Excel function arguments.

Furthermore, clicking the next field will show the subsequent field in the Function Arguments window.

Finally, once we enter all the argument values, click OK to view the function output in line with the meaning of VAR.P Excel explained previously.

Method #2 – Enter The Function Into The Worksheet Manually

Select a cell to show the outcome.
Type =VAR.P( in the cell.
[ Alternatively, type =V or =VAR and click the VAR.P Excel function twice from the listed suggestions to choose the option.]
Supply the arguments, separated by commas, and close the brackets.
Press Enter to secure the population variance value the VAR.P Excel returns in the chosen cell.

Examples

The following illustrations describe the practical ways of using the VAR.P Excel function.

Example #1

The source dataset holds the annual sales data of ten branch offices of a firm from 2021-23.

We must find and display the population variance for the specified data in cell B13.

Then, we can fetch the required output using VAR.P Excel function in the target cell.

Step 1: Select cell B13. Next, enter the VAR.P(), and press Enter.

=VAR.P(B2:B11,C2:C11,D2:D11)

On the other hand, we can enter the VAR.P Excelfunction in the following ways:

=VAR.P(B2:D11)

=VAR.P({20000;30000;10500;21000;25000;40000;38000;39000;35000;35000},{23000;33000;13500;24000;28000;43000;41000;42000;38000;38000},{18500;28500;9000;19500;23500;38500;36500;37500;33500;33500})

=VAR.P(20000,30000,10500,21000,25000,40000,38000,39000,35000,35000,23000,33000,13500,24000,28000,43000,41000,42000,38000,38000,18500,28500,9000,19500,23500,38500,36500,37500,33500,33500)

[ Alternatively, select the target cell and choose Formulas → More Functions → Statistical → VAR.P Excel function.

The Function Arguments window appears, where we enter the function argument values in the respective fields.

Finally, click OK to apply the VAR.P() in the chosen cell.]

Example #2

The following image shows a dataset with a sample size of 20.

We shall see the mathematical calculations involved in determining the population variance for the given data. Also, we shall verify the output using the inbuilt VAR.P Excelfunction. Assume the target cells are I3:J3.

Step 1: Choose cell D2, enter the Excel AVERAGE function, and press Enter.

=AVERAGE(A2:A21)

Step 2: Select cell F2, enter the following formula, and press Enter.

=A2-$D$2

Step 3: Choose cell G2, enter the formula below, and press Enter.

=F2*F2

Step 4: Choose cells F2:G2, and using the Excel fill handle, implement the two formulas in the column F and G cells.

Step 5: Select cell I3, enter the following formula based on the Excel SUM function, and press Enter.

=SUM(G2:G21)/D1

Step 6: Choose cell J3. Next, enter the VAR.P Excel function and press Enter.

=VAR.P(A2:A21)

VAR.P Excel Function - Example 2 - Step 6

First, we evaluate the mean value of the given dataset in cell D2 using the AVERAGE function, with the source dataset range as the input. Thus, we secure the required average value of 47.

Next, we compute the difference between each value in the dataset and the dataset’s average in the cell range F2:F21. Next, we determine the square of the differences in the cell range G2:G21.

Finally, we sum the square values and divide the total by the sample size, cited in cell D1. Thus, the formula gives the required population variance of 536.

On the other hand, we apply the VAR.P(), with the given source dataset range as the input, in cell J3 to acquire the required population variance of 536. Thus, the VAR.P() output confirms the mathematical calculations we performed to fetch the required population variance value.

Example #3

We have calculated the population variance for the source dataset using the VAR.P Excelfunction in cell B13.

However, the function output can be an error value in the following cases:

When not even two data points in the dataset range supplied as an argument value to the VAR.P() are numeric, the function output is the #DIV/0! Excel error value.

When a value that is supplied directly to the Var.P() is a text that does not translate into a number, the function returns the #VALUE! Excel error value.

Important Things To Note

The source dataset provided as input to the VAR.P Excel function must include a minimum of two numbers. Otherwise, the function will return the #DIV/0! error.
The VAR.P function in Excel does not count texts, blank cells, and logical values. However, if we provide such values directly as arguments to the function, it returns the #VALUE! Excel error.
If the text values provided directly as inputs to the VAR.P function in Excel do not translate into numbers, the function output is the #VALUE! error.

Frequently Asked Questions (FAQs)

1. What is Var p Excel function VBA?

The Var p Excel function VBA is the following:

WorksheetFunction.Var_P(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

For example, the source dataset lists a stock’s monthly prices.

The aim is to evaluate the population variance for the given data and showcase the output in cell D2. Then, we can utilize the Var_P() VBA method to acquire the desired output.

• Step 1: With the sheet holding the source dataset as the current spreadsheet, press Alt + F11 to access the VBA Editor.

• Step 2: Select the active VBAProject and choose Module under the Insert tab.

An empty module will open.

• Step 3: Feed the VBA code in the empty module to apply the VAR.P() in the cited cell.

• Step 4: Select the Play icon to run the VBA code.

Finally, open the current sheet to fetch the required population variance, which the VAR.P() returns in the target cell.

2. Should I use VaR p or VaR s in Excel?

You should use VaR p or VaR s in Excel, depending on whether the input dataset is the whole population or a sample of the population.

3. What are the factors that can affect VAR.P formula accuracy?

The factors that can affect VAR.P formula accuracy are the following:
• A longer duration of existing data ensures higher precision.
• The source dataset should be normal to ensure the formula’s accuracy.

Download Template

This article must be helpful to understand the VAR.P Excel, with its formula and examples. You can download the template here to use it instantly.

VAR.P Excel Download

VAR.P Excel