The VARPA function in Google Sheets is one of four built-in functions for calculating the population variance. In simple terms, it returns the variance, which is the average of the squared differences from the population mean.
This function differs from others by evaluating text and Boolean values as part of the calculation, making it especially useful when your data contains such values.
Variance Functions in Google Sheets
Here are the four functions in Google Sheets used to calculate population variance:
| Sr. No. | Function Name | Category |
|---|---|---|
| 1 | DVARP | Database |
| 2 | VARP | Statistics |
| 3 | VAR.P | Statistics |
| 4 | VARPA | Statistics |
Additionally, if you need to calculate variance on visible rows only, the SUBTOTAL function can help:
- Use
SUBTOTAL(11, range)to exclude filtered rows - Use
SUBTOTAL(111, range)to exclude both filtered and hidden rows
How These Functions Differ
- DVARP is a database function that works with structured data—the range must include a header row and support criteria.
- VARP and VAR.P are statistical functions for numeric data. VAR.P is the modern version of VARP, but both are functionally equivalent. Google Sheets converts VAR.P to VARP upon entry.
- VARPA, the focus of this guide, is similar to VARP but treats text and Boolean values as valid inputs in the calculation.
We’ll focus on the VARPA function in Google Sheets in this tutorial.
VARPA Function Syntax and Arguments
Syntax:
VARPA(value1, [value2, …])Arguments:
value1: The first value, array, or range reference representing the population.value2, …: Additional values, arrays, or ranges to include in the population.
You can pass values as individual arguments or in a range/array. Let’s explore both methods below.
VARPA Formula Examples
Using a Range Reference
To demonstrate how VARPA handles different values, let’s use this sample dataset in cells A1:F4:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 5 | 5 | 5 | 5 | 5 | 5 |
| 5 | 5 | 5 | 5 | 5 | 5 |
| 4 | 4 | 4 | 4 | 4 | 4 |
| 1 | TRUE | FALSE | YES | #N/A |
Now enter this formula in cell A5 and copy it across to F5:
=VARPA(A1:A4)Results (A5:F5):
- A5: 2.69
- B5: 2.69 (TRUE = 1)
- C5: 4.25 (FALSE = 0)
- D5: 4.25 (“YES” = 0)
- E5:
#N/A(error propagates) - F5: 0.22 (blank is ignored)
Key Observations:
- TRUE evaluates as
1. - FALSE and text strings like
"YES"are treated as0. - Errors cause the formula to return the same error.
- Blank cells are ignored.
VARPA with Array Literals and Hardcoded Values
Array Literals
You can also use array literals directly inside the formula:
=VARPA({5, 5, 4, 1}) // Returns: 2.69
=VARPA({5, 5, 4, "Yes"}) // Returns: 4.25 ("Yes" = 0)
The behavior is consistent with the range example above.
Hardcoded Values
Another method is passing values directly:
=VARPA(5, 5, 4, 1) // Returns: 2.69
=VARPA(5, 5, 4, TRUE) // Returns: 2.69
=VARPA(5, 5, 4, "Yes") // Returns: #VALUE!
Unlike array literals or ranges, hardcoded text like "Yes" causes an error when used directly in the function.
Final Thoughts on the VARPA Function in Google Sheets
Use the VARPA function when:
- You’re working with a full population dataset, and
- Your data includes text or Boolean values that should be included in the variance calculation.
For purely numeric population data, VARP or VAR.P is appropriate. If you’re working with structured data and need conditional variance, use DVARP. To calculate variance on filtered or visible rows only, consider using SUBTOTAL.
