## What Is VAR.P Excel Function?

The

VAR.PExcel function is an inbuiltStatisticalfunction. 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.PExcel 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.

##### Table of contents

###### Key Takeaways

- 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.

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)*

Or

*=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})*

Or

*=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.

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

*=AVERAGE(A2:A21)*

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

*=A2-$D$2*

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

*=F2*F2*

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

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

*=SUM(G2:G21)/D1*

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

*=VAR.P(A2:A21)*

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.

### Recommended Articles

This has been a guide to What Is VAR.P Excel. Here we learn the VAR.P function syntax and how to use it in Excel with examples and points to remember. You can learn more from the following articles –

## Leave a Reply