How to calculate variance in Excel – sample & population variance formula

This tutorial explains how to perform variance analysis in Excel and the formulas used to calculate sample and population variance.

Variance is a crucial statistical tool measuring data dispersion from the mean, indicating variability. For example, equatorial temperatures exhibit less variance than those in other climates. This article details various Excel variance calculation methods.

• What is Variance?
• Calculating Variance in Excel
  • Excel Variance Functions
  • VAR.S vs. VARA and VAR.P vs. VARPA
• Calculating Sample Variance in Excel
  • VAR Function
  • VAR.S Function
  • VARA Function
  • Sample Variance Formula Examples
• Calculating Population Variance in Excel
  • VARP Function
  • VAR.P Function
  • VARPA Function
  • Population Variance Formula Examples
• Variance Formula Usage Notes
• Variance vs. Standard Deviation in Excel

What is Variance?

Variance quantifies the spread of data points around the mean. It's the average of the squared differences from the mean.

Consider this example: five tigers aged 14, 10, 8, 6, and 2 years old.

1. Calculate the mean:
2. Find the differences from the mean. Visualized:
3. Square each difference.
4. Average the squared differences:

The variance is 16. A variance of 0 indicates no variability (all data points are identical). Higher variance implies greater data spread. This example shows population variance (the entire group). Sample variance uses a slightly different formula for subsets of a larger population.

Calculating Variance in Excel

Excel offers six built-in variance functions: VAR, VAR.S, VARP, VAR.P, VARA, and VARPA. Function selection depends on your Excel version, whether you're calculating sample or population variance, and how you want to handle text and logical values.

Excel Variance Functions

The table below summarizes Excel's variance functions:

Function	Excel Version	Variance Type	Text/Logical Handling
VAR	2000-2019	Sample	Ignored
VAR.S	2010-2019	Sample	Ignored
VARA	2000-2019	Sample	Evaluated
VARP	2000-2019	Population	Ignored
VAR.P	2010-2019	Population	Ignored
VARPA	2000-2019	Population	Evaluated

VAR.S vs. VARA and VAR.P vs. VARPA

VARA and VARPA differ in how they treat text and logical values:

Argument Type	VAR, VAR.S, VARP, VAR.P	VARA & VARPA
Logical values in arrays/references	Ignored	Evaluated (TRUE=1, FALSE=0)
Text representations of numbers in arrays/references	Ignored	Evaluated as zero
Logical values/text numbers typed directly	Evaluated (TRUE=1, FALSE=0)
Empty cells	Ignored

Calculating Sample Variance in Excel

Sample variance is calculated from a subset of a population. The formula is:

Where: x̄ = sample mean, n = sample size.

Excel functions for sample variance: VAR, VAR.S, and VARA.

VAR Function

The older sample variance function (Excel 2000-2019). VAR.S is recommended for improved accuracy.

VAR(number1, [number2], ...)

VAR.S Function

The modern sample variance function (Excel 2010 and later).

VAR.S(number1, [number2], ...)

VARA Function

Calculates sample variance including text and logical values.

VARA(value1, [value2], ...)

Sample Variance Formula Examples

To calculate the variance of a sample (B2:B7):

=VAR(B2:B7) or =VAR.S(B2:B7) or =VARA(B2:B7)

Manual calculation verification is shown in:

VARA yields different results with Boolean/text values because it treats them differently than VAR and VAR.S.

Calculating Population Variance in Excel

Population variance describes the spread of data in the entire population. The formula is:

Where: x̄ = population mean, n = population size.

Excel functions: VARP, VAR.P, and VARPA.

VARP Function

Calculates population variance (Excel 2000-2019). VAR.P is preferred.

VARP(number1, [number2], ...)

VAR.P Function

The improved population variance function (Excel 2010 and later).

VAR.P(number1, [number2], ...)

VARPA Function

Calculates population variance including text and logical values.

VARPA(value1, [value2], ...)

Population Variance Formula Examples

For population data (B2:B11):

=VARP(B2:B11) or =VAR.P(B2:B11) or =VARPA(B2:B11)

Manual verification:

VARPA produces different results with non-numeric data.

Variance Formula Usage Notes

• Supply arguments as values, arrays, or cell references.
• Argument limits vary by Excel version.
• Use VAR/VAR.S or VARP/VAR.P to ignore non-numeric data.
• Use VARA or VARPA to include text and logical values.
• At least two numeric values are needed for sample variance, and at least one for population variance.
• Text arguments causing number interpretation errors result in #VALUE! errors.

Variance vs. Standard Deviation in Excel

While variance is useful, standard deviation provides more practical information. Standard deviation is the square root of the variance. It's used with the mean to describe the data's typical range.

Excel provides functions for calculating standard deviation (see separate tutorial).

Practice Workbook

[Calculate Variance in Excel - examples (.xlsx file)] (Link to file would go here)

The above is the detailed content of How to calculate variance in Excel – sample & population variance formula. For more information, please follow other related articles on the PHP Chinese website!