How to Calculate Variance in Google Sheets

Key Insights

• Use VAR.S for sample data (most common scenario) and VAR.P only when you have the complete population—choosing wrong inflates or deflates your results
• Combine variance functions with FILTER to calculate conditional variance without creating helper columns or separate data ranges
• Always wrap variance calculations in IFERROR when working with dynamic data that might contain empty ranges or non-numeric values

What Variance Actually Tells You

Variance measures how spread out your data is from the mean. A low variance means your data points cluster tightly around the average. A high variance means they’re scattered widely. That’s it—no mystical statistics required.

In spreadsheet analysis, you’ll calculate variance when comparing consistency across datasets, identifying outliers, assessing risk in financial projections, or building quality control dashboards. If someone asks “how consistent are these numbers?” variance gives you the quantitative answer.

Google Sheets provides built-in functions that handle variance calculations in seconds. The challenge isn’t computing variance—it’s knowing which function to use and when.

Understanding VAR vs VARP Functions

Google Sheets offers four variance functions that confuse most users. Here’s the breakdown:

The difference comes down to one question: Is your data a sample or the entire population?

If you’re analyzing survey responses from 500 customers out of 50,000 total customers, that’s a sample. Use VAR.S. If you’re analyzing test scores from every student in a class with no intention of generalizing beyond that class, that’s a population. Use VAR.P.

When in doubt, use VAR.S. Sample variance is appropriate in the vast majority of real-world scenarios because you’re almost always working with incomplete data and making inferences.

Here’s a side-by-side comparison showing why this matters:

| A           | B                    |
|-------------|----------------------|
| Data        | Results              |
| 10          |                      |
| 12          |                      |
| 15          |                      |
| 11          |                      |
| 13          |                      |
|             |                      |
| Sample Var  | =VAR.S(A2:A6)        |
| Result      | 3.7                  |
|             |                      |
| Pop Var     | =VAR.P(A2:A6)        |
| Result      | 2.96                 |

With only five data points, the difference is significant—sample variance is 25% higher. As your dataset grows, this gap shrinks, but it never disappears entirely.

Basic Variance Calculations

Both VAR.S and VAR.P share identical syntax:

=VAR.S(value1, [value2, ...])
=VAR.P(value1, [value2, ...])

You can pass ranges, individual cell references, or direct numeric values. The functions ignore text and logical values within ranges but will error on direct text arguments.

Sample variance on sales data:

=VAR.S(A2:A20)

This calculates the sample variance for 19 sales figures in column A. If your daily sales figures show a variance of 2,500 (with sales in dollars), you know there’s substantial day-to-day fluctuation.

Population variance on complete data:

=VAR.P(B2:B50)

Use this when B2:B50 contains every data point that exists—perhaps all 49 products in your inventory with their prices, and you’re analyzing price dispersion across your entire catalog.

Multiple ranges:

=VAR.S(A2:A20, C2:C20, E2:E20)

This treats all three ranges as one combined dataset, calculating a single variance across 57 data points.

Conditional Variance with FILTER

Real analysis rarely involves simple ranges. You usually need variance for specific categories, time periods, or conditions. The FILTER function handles this elegantly.

Variance for a specific category:

=VAR.S(FILTER(A2:A100, B2:B100="Electronics"))

This calculates variance only for rows where column B contains “Electronics.” No helper columns, no manual filtering.

Multiple conditions:

=VAR.S(FILTER(A2:A100, B2:B100="Electronics", C2:C100>=100))

Now you’re calculating variance for Electronics items with values of 100 or greater.

Date-based filtering:

=VAR.S(FILTER(D2:D500, E2:E500>=DATE(2024,1,1), E2:E500<=DATE(2024,12,31)))

This isolates 2024 data for your variance calculation without modifying your source data.

Dynamic category comparison:

=VAR.S(FILTER($A$2:$A$100, $B$2:$B$100=G2))

Put category names in column G and drag this formula down to compare variance across categories instantly.

Manual Variance Calculation

Understanding the underlying math helps when you need custom variations or want to verify results. Variance is calculated as the average of squared deviations from the mean.

Sample variance formula:

$$\text{Variance} = \frac{\sum(x_i - \bar{x})^2}{n-1}$$

Here’s how to build this manually in Google Sheets:

| A           | B                                          |
|-------------|--------------------------------------------|
| Data        | Manual Calculation                         |
| 10          |                                            |
| 12          | Mean:                                      |
| 15          | =AVERAGE(A2:A6)                            |
| 11          |                                            |
| 13          | Sum of Squared Deviations:                 |
|             | =SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2)      |
|             |                                            |
|             | Sample Variance (n-1):                     |
|             | =SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2)/(COUNT(A2:A6)-1) |
|             |                                            |
|             | Population Variance (n):                   |
|             | =SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2)/COUNT(A2:A6) |

Breaking down the sample variance formula:

=SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2)/(COUNT(A2:A6)-1)

1. AVERAGE(A2:A6) calculates the mean
2. A2:A6-AVERAGE(A2:A6) computes deviation for each value
3. (...)^2 squares each deviation
4. SUMPRODUCT(...) sums all squared deviations
5. /(COUNT(A2:A6)-1) divides by n-1 for sample variance

This manual approach becomes useful when you need weighted variance or want to exclude outliers beyond a certain threshold.

Practical Applications

Variance alone is useful, but it shines when combined with related statistics. Here’s a dashboard snippet that provides comprehensive dispersion analysis:

| A              | B                          | C                    |
|----------------|----------------------------|----------------------|
| Metric         | Formula                    | Result               |
| Count          | =COUNT(Data!A2:A500)       | 498                  |
| Mean           | =AVERAGE(Data!A2:A500)     | 245.67               |
| Variance       | =VAR.S(Data!A2:A500)       | 1,234.56             |
| Std Deviation  | =STDEV.S(Data!A2:A500)     | 35.14                |
| Coef of Var    | =STDEV.S(Data!A2:A500)/AVERAGE(Data!A2:A500) | 0.143   |

The coefficient of variation (standard deviation divided by mean) lets you compare variability across datasets with different scales. A CV of 0.143 means the standard deviation is 14.3% of the mean—useful for comparing consistency between product lines with different price points.

Comparing variance across groups:

| A          | B                                              |
|------------|------------------------------------------------|
| Region     | Variance                                       |
| North      | =VAR.S(FILTER(Sales, Region="North"))          |
| South      | =VAR.S(FILTER(Sales, Region="South"))          |
| East       | =VAR.S(FILTER(Sales, Region="East"))           |
| West       | =VAR.S(FILTER(Sales, Region="West"))           |

Lower variance indicates more predictable performance. If the South region shows variance of 500 while the North shows 2,000, Southern sales are more consistent—valuable information for forecasting and resource allocation.

Common Errors and Troubleshooting

Variance functions fail predictably. Here’s how to handle the common issues.

Empty or insufficient data:

VAR.S requires at least two numeric values. One value or an empty range returns #DIV/0!.

=IFERROR(VAR.S(A2:A10), "Insufficient data")

Text values in ranges:

Text cells are silently ignored. If your “numbers” are actually text (common with imported data), you’ll get unexpected results or errors.

=VAR.S(VALUE(A2:A100))

This forces text-to-number conversion but will error on non-numeric text. For mixed data:

=VAR.S(FILTER(A2:A100, ISNUMBER(A2:A100)))

Dynamic ranges with potential issues:

=IFERROR(
  IF(COUNT(FILTER(A2:A100, B2:B100=D2))>=2,
    VAR.S(FILTER(A2:A100, B2:B100=D2)),
    "Need 2+ values"),
  "Error in data")

This checks for sufficient data before calculating and catches any other errors.

Handling zeros:

Zeros are valid numeric values and will be included in variance calculations. If zeros represent missing data rather than actual zero values, filter them out:

=VAR.S(FILTER(A2:A100, A2:A100<>0))

Variance calculations in Google Sheets are straightforward once you understand the sample-versus-population distinction and master the FILTER combination for conditional analysis. Start with VAR.S, wrap it in IFERROR for production dashboards, and you’ll handle 95% of variance needs without breaking a sweat.