VAR Function in Google Sheets: Complete Guide

Key Insights

• VAR calculates sample variance, which estimates population variance from a subset of data—use it when your data represents a sample rather than the entire population
• VAR ignores text and logical values in ranges, but VARA includes them (treating FALSE as 0 and TRUE as 1), which can dramatically change your results
• Combine VAR with FILTER for powerful conditional variance calculations that let you analyze data spread across specific segments without helper columns

Introduction to VAR Function

Variance measures how spread out your data is from the mean. The VAR function in Google Sheets calculates sample variance—a critical distinction that affects when and how you should use it.

When you’re working with a subset of data (survey responses from 500 customers, not all customers; sales from 12 months, not the entire company history), you’re working with a sample. Sample variance uses n-1 in its denominator (Bessel’s correction) to provide an unbiased estimate of the true population variance.

VAR is your go-to function for most real-world statistical analysis because you’re rarely working with complete population data. Understanding variance helps you quantify risk, measure consistency, identify outliers, and make data-driven decisions about everything from inventory management to financial forecasting.

Syntax and Parameters

The VAR function accepts a flexible set of inputs:

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

Parameters:

• value1 (required): The first value, cell reference, or range
• value2, ... (optional): Additional values or ranges (up to 30 arguments)

Here’s the basic usage with a range:

=VAR(A1:A10)

You can also pass multiple ranges or individual values:

=VAR(A1:A10, C1:C10, E5)

How VAR handles different data types:

This distinction matters. If you type =VAR(A1:A10) and A3 contains “N/A”, that cell is simply skipped. But =VAR(1, 2, "text", 4) throws an error because the text is a direct argument.

// Cell A1:A5 contains: 10, 20, "missing", 30, 40
=VAR(A1:A5)  // Returns 166.67 (calculates variance of 10, 20, 30, 40 only)

VAR vs. Related Variance Functions

Google Sheets offers six variance functions. Choosing the wrong one produces incorrect results.

VAR and VAR.S are identical. VAR.S exists for Excel compatibility.

The critical decision is sample vs. population variance:

// Data in A1:A5: 2, 4, 6, 8, 10

=VAR(A1:A5)   // Returns 10 (sample variance, divides by n-1 = 4)
=VARP(A1:A5)  // Returns 8 (population variance, divides by n = 5)

When to use which:

Use VAR (sample) when:

• Your data is a subset of a larger group
• You’re making inferences about a population
• You collected data through sampling

Use VARP (population) when:

• You have data for every member of the group
• You’re describing only this specific dataset
• Examples: test scores for an entire class, all transactions in a closed period

The VARA/VARPA functions are rarely needed but useful when your data intentionally includes TRUE/FALSE values that should count as 1/0:

// A1:A4 contains: 5, TRUE, FALSE, 10
=VAR(A1:A4)   // Returns 12.5 (only 5 and 10)
=VARA(A1:A4)  // Returns 19.58 (5, 1, 0, 10)

Practical Examples and Use Cases

Example 1: Analyzing Monthly Revenue Volatility

You have 12 months of revenue data and want to understand how much it fluctuates:

// Revenue data in B2:B13 (Jan-Dec)
// B2:B13: 45000, 52000, 48000, 61000, 55000, 49000, 
//         58000, 62000, 51000, 47000, 53000, 68000

=VAR(B2:B13)  // Returns 47,969,696.97

// For interpretability, also calculate standard deviation
=STDEV(B2:B13)  // Returns 6,926.01 (in dollars)

The variance alone is hard to interpret because it’s in squared units. A standard deviation of ~$6,926 tells you monthly revenue typically deviates about $7K from the mean.

Example 2: Comparing Variance Across Product Lines

You’re a product manager comparing sales consistency across three product lines:

// Product A sales in B2:B13
// Product B sales in C2:C13  
// Product C sales in D2:D13

=VAR(B2:B13)  // Product A variance: 2,450,000
=VAR(C2:C13)  // Product B variance: 890,000
=VAR(D2:D13)  // Product C variance: 5,200,000

Product C has the highest variance—its sales are least predictable. This might indicate seasonal dependency, marketing inconsistency, or market volatility. Product B is most stable.

Example 3: Quality Control in Manufacturing

You’re monitoring widget weights where consistency matters:

// Widget weights (grams) in A2:A51 (50 samples)
// Target weight: 100g, acceptable variance threshold: 4

=VAR(A2:A51)  // Returns 3.2

=IF(VAR(A2:A51) > 4, "Process Out of Control", "Process Stable")
// Returns "Process Stable"

Combining VAR with Other Functions

Conditional Variance with FILTER

Calculate variance for specific subsets without creating helper columns:

// Column A: Region (East, West, Central)
// Column B: Sales figures

// Variance for East region only
=VAR(FILTER(B2:B100, A2:A100="East"))

// Variance for sales above $10,000
=VAR(FILTER(B2:B100, B2:B100>10000))

// Multiple conditions: East region AND Q1
=VAR(FILTER(B2:B100, (A2:A100="East") * (C2:C100="Q1")))

Coefficient of Variation

The coefficient of variation (CV) expresses standard deviation as a percentage of the mean, enabling comparison across datasets with different scales:

// CV formula (using standard deviation, which is sqrt of variance)
=STDEV(A1:A10) / AVERAGE(A1:A10)  // Returns decimal

// As percentage
=STDEV(A1:A10) / AVERAGE(A1:A10) * 100  // Returns percentage

// Using variance directly (less common but mathematically equivalent)
=SQRT(VAR(A1:A10)) / AVERAGE(A1:A10) * 100

Variance-to-Mean Ratio

Useful for detecting overdispersion in count data:

=VAR(A1:A50) / AVERAGE(A1:A50)
// Ratio > 1 suggests overdispersion
// Ratio ≈ 1 suggests Poisson-like distribution
// Ratio < 1 suggests underdispersion

Dynamic Variance with ARRAYFORMULA

Calculate rolling variance or variance across multiple groups:

// Variance for each department (assuming data is grouped)
// Departments in column A, values in column B
=UNIQUE(A2:A100)  // In E2, get unique departments

// In F2, array formula for each department's variance
=ARRAYFORMULA(
  IF(E2:E10="", "", 
    BYROW(E2:E10, LAMBDA(dept, 
      VAR(FILTER(B2:B100, A2:A100=dept))
    ))
  )
)

Common Errors and Troubleshooting

#DIV/0! Error

VAR requires at least two numeric values. With one value or no values, you get #DIV/0!:

=VAR(A1)  // #DIV/0! if A1 is a single value

// Fix with IFERROR
=IFERROR(VAR(A1:A10), "Insufficient data")

#VALUE! Error

Occurs when text is passed as a direct argument:

=VAR(1, 2, "three", 4)  // #VALUE! error

// This works fine (text in range is ignored)
=VAR(A1:A10)  // Even if some cells contain text

Unexpected Results from Empty Cells

Empty cells are ignored, which can surprise you:

// A1:A5 contains: 10, [empty], 20, [empty], 30
=VAR(A1:A5)    // Returns 100 (variance of 10, 20, 30)
=COUNT(A1:A5)  // Returns 3, not 5

If empty should mean zero, use:

=VAR(IF(A1:A5="", 0, A1:A5))

Robust Error Handling Pattern

For production spreadsheets, wrap VAR calculations:

=IFERROR(
  IF(COUNT(A1:A100) < 2, 
    "Need 2+ values",
    VAR(A1:A100)
  ),
  "Calculation error"
)

Summary and Quick Reference

VAR calculates sample variance—use it for most real-world analysis where your data represents a sample. Use VARP only when you have complete population data.

Quick Reference:

Related functions to explore: STDEV (standard deviation), AVERAGE, MEDIAN, PERCENTILE, and DEVSQ (sum of squared deviations).

Remember: variance is in squared units. For interpretable spread measurements, use STDEV instead—it returns values in the same units as your original data.