## How to use the VAR.P function

**What is the VAR.P function?**

The VAR.P function returns the variance based on the entire population. The function ignores logical and text values.

**What is variance in statistics?**

Variance is a measure in statistics that shows how much a set of numbers are spread out from their average value. Variance is an important measure in statistics and science.

**How is the variance calculated?**

It is calculated by taking the average of squared deviations from the mean. The VAR.P function example below shows in great detail how the variance is calculated.

### Table of contents

## 1. VAR.P Function Syntax

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

## 2. VAR.P Function Arguments

number1 |
Required.Â A cell reference to the population. |

number2 |
Optional. Up to 254 additional arguments. |

## 3. VAR.P Function Example

Formula in cellÂ B13:

The formula calculates the variance based on the numbers in cell range B3:B7.

Both Set1 and Set2 above have the same average 30, however, values in Set2 are much more spread out.

Set1 variance: 170

Set2 variance: 8600

### 3.1 Explaining the math formula for calculating the variance

The equation for VAR.P is:

xÂ Ì…Â is the sample mean AVERAGE(number1,number2,â€¦)

n is the sample size.

#### Step 1 - Calculating the average

To calculate an average you need to add up all the values and then divide by the number of values.

10 + 30 + 25 + 50 + 35 equals 150.

The number of values is five. 150 divided by 5 equals 30

#### Step 2 - Subtract average value from the set of numbers

10-30= -20

30-30= 0

25-30= -5

50-30= 20

35-30 = 5

#### Step 3 - Square the difference

(-20)^2 = 400

(0)^2 = 0

(-5)^2 = 25

(20)^2 = 400

(5)^2 = 25

#### Step 4 - Calculate a total

400 + 0 + 25 + 400 + 25 = 850

#### Step 4 - Divide total by the number of values

850 / 5 = 170

## 4. Why is the VAR.P function returning an error?

If the VAR.P function returns an error, make sure that the source data has no error values, as the function cannot handle them.

The image above shows an error in cell B5. The VAR.P function returns an error because the source data in B3:B7 contains an error.

The IFERROR function lets you ignore error values.

How to find errors in a worksheet

## 5. Sort rows by variance based on a population

This example demonstrates a formula in cell B8 that sorts rows from cell range B3:N6 by the variance of a population from large to small. Cell ranges P3:P6 contains the variances from the source data and P7:P11 contains the variances based on the sorted rows.

### Explaining formula

#### Step 1 - Calculate the variance of a population

VAR.P(a)

#### Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, â€¦,] calculation)

LAMBDA(a,VAR.P(a))

#### Step 3 - Calculate the variance of a sample by row

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(C3:N6,LAMBDA(a,VAR.P(a)))

returns

{47221.2222222222;81130.9722222222;87499.7222222222;41286.1388888889}

#### Step 4 - Sort rows based on the variance of a sample

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],â€¦)

SORTBY(B3:N6,BYROW(C3:N6,LAMBDA(a,VAR.P(a))),-1)

becomes

SORTBY(B3:N6,{47221.2222222222;81130.9722222222;87499.7222222222;41286.1388888889},-1)

and returns

## 6. When to use the VAR.P function and when to use the VAR.S function?

The VAR.P function and the VAR.S function are both used to calculate the variance, however, they differ in how they calculate the variance. The VAR.P function assumes that the dataset is the entire population, while the VAR.S function assumes that the dataset is a sample of the population.

The difference between calculating the population and sample variance is that population variance divides by the number of observations in the population, while the sample variance divides by the number of observations minus one. This makes the sample variance larger than the population variance, because it tries to account for the uncertainty of estimating the population variance from a sample.

You should use the VAR.P function when you have data for an entire population, and use the VAR.S function when you have data for a sample of the population. It is sometimes not practical to calculate the variance for millions of observations, a "Sample size calculator" is often useful as it also meets a given the confidence level and a margin of error.

### Functions in 'Statistical' category

The VAR.P function function is one of many functions in the 'Statistical' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form