The VARPA is one of the five functions in Google Sheets for calculating the population variance.

In other words, we can use the VARPA function in Google Sheets for calculating the average of the squared difference from the population mean.

Here are those five functions.

How do they differ?

The first function is for use with structured data that means the entered data must not be just a range or array.

It should contain a field label or row header.

Functions 2 and 3 are statistical functions and you can use them with a range/array.

In these two, the latter replaces the former. But it seems Google has kept both of them in Sheets for backward compatibility.

In this tutorial, we will learn how to use the VARPA, i.e., function # 4, in Google Sheets.

Function 5 is a Math function to calculate variance when records (rows) in your data are hidden or filtered out.

In this, SUBTOTAL(11,range) excludes filtered out rows whereas SUBTOTAL(111,range) excludes filtered out as well as hidden rows.

There are five more functions in Google Sheets for the variance calculation. Use them with a population sample.

Here are them.

Must Check:- Google Sheets Function Guide.

VARPA Function Syntax and Arguments in Google Sheets

Here are the syntax and arguments of the function VARPA in Google Sheets.

Syntax:- VARPA(value1, [value2, …])

Arguments:-

value1 – The first value, array, or range reference of the population.

value2, … – Additional values, arrays, or range references to include in the population.

Let’s go to a few formula examples to understand how the VARPA function evaluates the given data when they are;

1. Array or range reference.
2. Arguments are a list of values directly entered within the formula.

VARPA – Formula Examples

Array or Range Reference in the Formula

You may get confused with the terms array or range reference. I’ll try to explain them below.

I have the following VARPA formula in cell A11 which was copied to B11:J11.

=varpa(A2:A10)

As you can see, I have used the range reference A2:A10 in the formula.

Note:- I’ve edited the formula in cell I11 as below to remove the blank cell from the range reference.

=varpa(I2:I9)

Let’s compare the results in row # 11.

You may check the above VARPA function results in A11:J11. You can understand the following.

1. B11 and C11 – Text evaluates as 0 (zero).
2. D11 and E11 – The FALSE value evaluates as 0 (zero).
3. F11 and G11 – The TRUE value evaluates as 1.
4. H11 and I11 – Ignore blanks. To test it, I have used the array H2:H10 in the H11 formula and the array I2:I9 in the I11 formula.
5. J11 – Error value causes an error.

As you can see, in the above examples, we have used a range reference within the VARPA formula in Google Sheets.

Let’s test the function now by specifying an array as below.

The below formula is a replacement for the formula in cell A11.

=VARPA({445,448,447,455,457,469,474,482,484})

The bold part is an array.

Let’s replace the second number, i.e., 448, with the string “Hellow” as below.

=VARPA({445,"Hellow",447,455,457,469,474,482,484})

It will return the same output in cell B11.

Similarly, you can test other formulas in the row by changing the same value to TRUE, FALSE, an error, or by removing it.

You will get the same output in row # 11.

Numbers Directly Entered within the Formula

Now let’s test the VARPA function in Google Sheets by directly entering the list of numbers within the formula (not as an array).

Formula 1:

=VARPA(445,448,447,455,457,469,474,482,484)

Output: 206.67 (A11)

Now I am replacing the number 448 with the text “Hellow.” The VARPA formula will return an error.

=VARPA(445,"Hellow",447,455,457,469,474,482,484)

Here also the TRUE and FALSE evaluates to 1 and 0, respectively.

=VARPA(445,TRUE,447,455,457,469,474,482,484)

No doubt, an error value will cause the VARPA function in Google Sheets to return an error.

Population Variance without Using the VARPA Function in Google Sheets

I’ve listed some dedicated functions in table#1 above for population variance calculation in Google Sheets.

We can also use some other built-in functions/operators for the population variance calculation in Google Sheets.

Example:-

We will consider the numbers in the cell Range: A2:A10.

1. First, insert =average(A2:A10) in cell B2 to get the average (mean) of the values in A2:A10.
2. In cell C2, insert =ArrayFormula(sum((A2:A10-B2)^2)) to calculate the difference of each number from the mean in cell B2 and to square it.
3. Enter =C2/count(A2:A10) in cell D2, and that’s the variance.

That’s all about how to use the VARPA function in Google Sheets.

Thanks for the stay. Enjoy!