Variance in Excel, Google Sheets, Python and R

Quick reference

All results below use the data set 2, 4, 4, 4, 5, 5, 7, 9, whose sample variance is ≈ 4.5714 and population variance is 4.

Excel

Type your values into a column, say A1 to A8, then in an empty cell enter=VAR.S(A1:A8) for sample variance or =VAR.P(A1:A8) for population variance. Excel returns 4.571428571 and 4 respectively. For standard deviation, the matching functions are STDEV.S and STDEV.P.

Excel has six variance functions, which is where confusion starts:

The A versions are rarely what you want. A cell containing "n/a" becomes a zero, which drags the mean down and inflates the variance without any warning.

Common Excel problems

• Numbers stored as text. Values pasted from a web page or PDF are often left-aligned text. VAR.S skips them silently, so the result uses fewer values than you think. Check with =COUNT(A1:A8).
• #DIV/0! error. VAR.S needs at least two numbers. With one, the denominator n − 1 is zero.
• Hidden or filtered rows. VAR.S includes hidden rows. Use=SUBTOTAL(110, A1:A100) (sample) or =SUBTOTAL(111, A1:A100)(population) to work on visible rows only.

Google Sheets

Google Sheets accepts the same names as Excel: VAR.S, VAR.P,VAR, VARP, VARA and VARPA, with the same meanings. Formulas copy between the two without changes.

Python

Standard library

import statistics

data = [2, 4, 4, 4, 5, 5, 7, 9]
statistics.variance(data)   # 4.571428571428571  (sample)
statistics.pvariance(data)  # 4                  (population)

numpy

import numpy as np

np.var(data)          # 4.0                 (population, the default)
np.var(data, ddof=1)  # 4.571428571428571   (sample)

pandas

import pandas as pd

s = pd.Series(data)
s.var()        # 4.571428571428571  (sample, the default)
s.var(ddof=0)  # 4.0                (population)

Watch the defaults. numpy uses ddof=0 (population) and pandas uses ddof=1 (sample). Moving a calculation from one library to the other changes the answer unless you set ddof explicitly. The name stands for "delta degrees of freedom": the denominator is n − ddof.

R

x <- c(2, 4, 4, 4, 5, 5, 7, 9)
var(x)                               # 4.571429  (sample)
var(x) * (length(x) - 1) / length(x) # 4         (population)

R's built-in var() and sd() always use n − 1. There is no population option, so rescale by (n − 1) / n as shown. Missing values make the result NA unless you add na.rm = TRUE.

Checking your answer

If two tools disagree, the cause is nearly always one of three things: one divides byn and the other by n − 1; some values were skipped as text or blanks; or a missing value was treated as zero. Paste the same numbers into thevariance calculator to get both versions side by side with the working shown. It also tells you which entries it could not read as numbers instead of dropping them quietly.