The VAR function in Google Sheets is a statistical function used to calculate the variance of a sample. Its modern equivalent is VAR.S, where the “S” stands for “sample.”
Functionally, VAR and VAR.S are identical—they use the same arguments and return the same result. The main difference is that VAR.S is the newer, standardized version of the function. Google Sheets retains VAR for backward compatibility.
So, which one should you use?
Use VAR.S whenever possible, as it clearly indicates that the variance is being calculated for a sample, not a full population.
Understanding Variance (Before Using the Function)
Before diving into the formula, let’s quickly understand what variance means.
Variance measures how far a set of numbers are spread out from their average (mean). It’s closely related to standard deviation. In fact:
Variance = (Standard Deviation)²
To calculate variance:
- Find the average of your data.
- Calculate the squared differences from the average for each value.
- Take the average of those squared differences.
If you’re working with a population, divide by N (the number of values).
If you’re working with a sample, divide by N-1.
Manual Variance Calculation (Example)
Let’s manually calculate the variance of a sample dataset before using the formula.
Sample Data: Height of 10 Students (in cm)
| Student | Height |
|---|---|
| Esha | 160 |
| Farhan | 155 |
| Harsha | 140 |
| Alex | 148 |
| Daisy | 147 |
| Hannah | 130 |
| Julia | 162 |
| Ayesha | 168 |
| Noor | 139 |
| Mark | 138 |
| Total | 1487 |
Step 1: Calculate the Mean
Mean=1487/10 = 148.7
Step 2: Calculate Squared Differences from the Mean
=(160-148.7)^2+(155-148.7)^2+(140-148.7)^2+(148-148.7)^2+(147-148.7)^2+(130-148.7)^2+(162-148.7)^2+(168-148.7)^2+(139-148.7)^2+(138-148.7)^2 = 1354.1
Step 3: Calculate Variance
- Population variance:
1354.1/10 = 135.41
- Sample variance:
=1354.1/9 = 150.46
So the variance of this sample is 150.46.
VAR and VAR.S Syntax in Google Sheets
VAR Function
VAR(value1, [value2, ...])VAR.S Function
VAR.S(value1, [value2, ...])Arguments:
value1: Required. The first value or range representing the sample.value2: Optional. Additional values or ranges to include in the sample.
Note: You can’t use just a single value like =VAR(160)—this will return a #DIV/0! error. Instead, use a range like =VAR(A1:A10).
Example: Using VAR and VAR.S in Google Sheets
Let’s now calculate the variance of the same student height data using both functions.
If your height values are in cells B3:B12, then:
=VAR(B3:B12)=VAR.S(B3:B12)Both formulas return: 150.46
You can scroll up and compare this result with our manual calculation—it matches!
Usage Notes for VAR and VAR.S in Google Sheets
Keep the following points in mind when using VAR or VAR.S in Google Sheets:
- Blank cells are ignored.
- Zero values are included.
- Text values in the range are treated as blanks and ignored.
For example:
- If cell
B6is blank, the variance result will change. - If cell
B6is 0, it will be included in the calculation and lower the variance.
You can observe this behavior by testing different values in the range.
Final Thoughts on VAR and VAR.S Functions
The VAR and VAR.S functions in Google Sheets make it easy to calculate the variance of a sample dataset without manual computation. While both functions return the same results, it’s best practice to use VAR.S for clarity and modern compatibility.
