The VARA function in Google Sheets is one of the four functions for estimating variance based on a sample.
I will come to the name of the other three functions and how they differ from the VARA.
But before that, we must understand the terms ‘variance’ and ‘sample.’
The below example may help you understand the term sample, or we can say SAMPLE vs. POPULATION.
Population:
E.g.:- All students in my college.
Sample:
E.g.:- All students in the physics department in my college.
We use a sample when we find it tough to collect data from a whole population because of its size.
Regarding variance, it’s the measure of how far a set of numbers (values in a dataset) is dispersed from their mean value. Please see the example section to see the calculation/formula behind it.
The VARA function in Google Sheets has three siblings. They are VAR, VAR.S, and DVAR.
The below table explains how they differ.
| Function Name | Category | Remarks |
| VAR | Statistics | |
| VAR.S | Statistics | It replaces VAR. So use it instead. |
| DVAR | Database | To use with structured data. It additionally supports conditions. |
| VARA | Statistics | Unlike the other three, it evaluates the text and Boolean values. You can find more details under the “Formula Example” section below. |
VARA Function Syntax and Arguments
Syntax:
VARA(value1, [value2, ...])Arguments:
value1 – The first value or array of the sample.
value2 – The second value or array to include in the sample.
Value1 and value2 are required. Subsequent values are optional.
If you do not specify any value, the formula will return N/A. If there is only value1, then it will return DIV/0.
How many values or arrays can we specify within the VARA function in Google Sheets?
It is from 1 to 30, but Google Sheets supports an arbitrary number of arguments.
Formula Example
We can calculate the variance based on a sample manually as below.
Example:
Set of sample numbers – 5, 10, 15
Finding Mean = (5+10+15)/3 = 10
Finding the squared differences of the mean =(5-10)^2, (10-10)^2, (15-10)^2
Sum it =(5-10)^2+(10-10)^2+(15-10)^2 = 50
Average the sum of the squared differences of the mean by dividing it by sample size – 1
I.e., 50/2=25.
We can easily calculate the same using the VARA function as below in Google Sheets.
=vara(5,10,15)If the above data points are in A1:A3, then you can use them directly within the formula.
=vara(A1:A3)How do the Blank Cells, Boolean, and Text Strings affect the variance when using the VARA function?
It affects the variance calculation in the following ways.
- Blanks don’t make any changes in the result as the function ignores it.
- Text and Boolean FALSE values are converted to 0.
- Boolean TRUE values are converted to 1.
Specifying Criteria within the VARA Function in Google Sheets
We can use expressions within the VARA function to return the variance of a sample extracted from records based on given criteria, aka conditions.
Usually, we use the FILTER function within the VARA function for that. But we can also use the QUERY.
Here is how we can use FILTER/QUERY within VARA to return conditional variance based on a sample.
Sample Data:
Assume we have the height of men and women of a particular age group in our locality and want to return the conditional variance based on this data.
The condition to specify is column C = M (male).
Formula Using the VARA + FILTER Combination:
=vara(filter(B2:B18,C2:C18="M"))
Formula Using the VARA + QUERY Combination:
=vara(query(A1:C18,"Select B where C='M' label B''"))When using QUERY, you must specify the label clause as above to remove the label of the height column.
Because VARA will consider labels as 0, and that may affect the result.
That’s all about the VARA statistical function in Google Sheets.
Thanks for the stay. Enjoy!
