Variance is a fundamental statistical concept that measures how data points in a set are spread out relative to the mean. In Excel, calculating variance is essential for analyzing data variability in fields such as polling results, stock market fluctuations, and investment returns.

There are two types of variance calculations: population variance and sample variance. Population variance considers the entire dataset, while sample variance is based on a subset of the data.

Excel offers three primary functions to compute variance:

VAR: The traditional function available in all versions of Excel, used for calculating variance based on a sample.

VAR.S: An improved version introduced in Excel 2010, used for calculating sample variance.

VAR.P: Also introduced in Excel 2010, this function calculates variance for an entire population.

Both VAR.S and VAR.P are enhancements of the VAR function and are available in Excel 2010 and later versions.

Calculating variance using the VAR function in Excel

In older versions of Excel, the VAR function is used to compute variance based on sample data.

The syntax for the VAR function is:

=VAR(number1, [number2], …)

You can use numbers, individual cell references, ranges of cells, or any combination of these as arguments in the function.

Consider the following dataset of student scores to calculate variance:

To calculate variance using specific numbers as arguments:

To compute variance using cell references:

To find variance over a range of cells:

You can also mix numbers, cell references, and ranges in the same formula:

Calculating sample variance using the VAR.S function in Excel

The VAR.S function is an updated version of the VAR function, available in Excel 2010 and newer. It is used to estimate the variance based on a sample.

The syntax for the VAR.S function is:

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

To calculate sample variance using numbers directly:

To compute sample variance using cell references:

To find sample variance over a range of cells:

You can combine different types of arguments in one formula:

The VARA function can also be used to estimate sample variance, and it includes numbers, text, and logical values in the calculation:

Calculating population variance using the VAR.P function in Excel

The VAR.P function computes variance based on an entire population and is available in Excel 2010 and later versions. It shows how data points are spread out across the whole dataset.

The syntax for the VAR.P function is:

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

To calculate population variance using numbers:

To compute population variance using cell references:

The VARPA function can be used to find population variance and includes numbers, text, and logical values in its calculation:

By utilizing these functions, you can effectively calculate variance in Excel for both sample data and entire populations.