How to Use the VAR.S and VAR.P Functions in Google Sheets to Calculate Statistical Variance

In statistical analysis, financial risk modelling, and scientific data processing, measuring the spread of numerical data around its mean is crucial. While standard deviation expresses dispersion in the original units of measurement, variance calculates the average of squared deviations from the mean. Variance serves as the mathematical foundation for regression analysis, hypothesis testing (ANOVA), and portfolio asset volatility modelling. Google Sheets provides two primary native functions to compute variance: VAR.S for sample datasets and VAR.P for entire populations.

Sample vs Population Variance: Mathematical Foundations

Understanding which function to choose depends on whether your dataset represents a subset or the complete population:

• Sample Variance (VAR.S): Calculates variance across a representative sample of a larger population. It applies Bessel’s correction by dividing the sum of squared differences by n - 1 (where n is the sample size). Without Bessel’s correction, random sampling tends to underestimate true population variability. Formula:
  s² = Σ(x - x̄)² / (n - 1)
• Population Variance (VAR.P): Calculates variance across an entire, exhaustive population without sampling. Because all data points are accounted for, it divides the sum of squared differences directly by N (the total population count). Formula:
  σ² = Σ(x - μ)² / N

Note: Standard deviation is simply the square root of variance ($s = \sqrt{s^2}$ and $\sigma = \sqrt{\sigma^2}$).

Syntax and Parameter Reference

Both functions share identical, flexible parameter structures:

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

• value1: The primary value, cell reference, or contiguous cell range containing numerical data (e.g., B2:B50).
• [value2, ...] (Optional): Additional individual numbers, non-contiguous ranges, or nested array expressions to include in the variance calculation.

Step-by-Step Practical Examples

Consider an e-commerce operational register where Column A records Transaction ID, Column B lists Order Amount (£), and Column C tracks Delivery Time (Days).

Example 1: Computing Sample Variance with VAR.S

Suppose you are auditing 30 randomly selected orders (cells B2:B31) out of 10,000 total monthly transactions:

1. Select an empty reporting cell (such as E2).
2. Enter the formula:

   =VAR.S(B2:B31)

3. Press Enter. Google Sheets subtracts the sample mean from each transaction value, squares each difference, sums them, and divides by 29 (30 - 1) to return the unbiased sample variance.

Example 2: Computing Population Variance with VAR.P

Suppose your small business employs exactly 12 full-time staff members, and cells D2:D13 contain their completed annual holiday days taken. Because this dataset represents the complete, exhaustive workforce population:

1. Select cell E3.
2. Enter the formula:

   =VAR.P(D2:D13)

3. Press Enter. Google Sheets calculates exact population variance by dividing the sum of squared deviations by 12.

Modern Syntax vs Legacy Compatibility

Earlier versions of spreadsheet software used generic function names that can cause confusion:

• VAR: Identical in behaviour to VAR.S. Modern spreadsheets introduced VAR.S to clarify that sample variance is being calculated.
• VARP: Identical in behaviour to VAR.P. Always prefer VAR.P for clarity across collaborative team sheets.
• VARA and VARPA: These variants evaluate boolean values (TRUE = 1, FALSE = 0) and text strings (treated as 0). By contrast, VAR.S and VAR.P safely ignore text labels and blank cells within evaluated ranges.

Filtering Data Dynamically Before Calculating Variance

To calculate variance exclusively for rows meeting specific conditional rules (for example, calculating order variance only for “Express” delivery orders in Column C):

=VAR.S(FILTER(B2:B100, C2:C100 = "Express"))

The FILTER function isolates only Express order values before passing the filtered array into VAR.S.

Troubleshooting Common Errors

When computing variance in Google Sheets, watch for these common error states:

• #DIV/0! Error: Occurs in VAR.S when the input range contains fewer than two numerical entries (since n - 1 = 0, triggering a division by zero). Ensure your sample contains at least two numeric values.
• #VALUE! Error: Occurs when non-numeric text strings are supplied directly as individual arguments (e.g., =VAR.S(10, "invalid", 25)). Cells containing text inside referenced ranges (e.g., A1:A10) are automatically ignored.
• Zero Variance (0): If all numbers in your dataset are identical (e.g., [10, 10, 10, 10]), there is no dispersion from the mean, and the function returns 0.