VAR.S function

The VAR.S function in Excel is used to calculate the variance of a sample from a larger population. Variance measures the spread of a data set—how much the individual values differ from the mean (average). The VAR.S function calculates sample variance, which is the appropriate measure when your data represents only a subset (sample) of a larger population.

Syntax

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

Parameters

• number1 (required): The first number, cell reference, or range that contains the sample data.
• number2, … (optional): Additional numbers, cell references, or ranges containing sample data.

You can use multiple ranges or individual values as arguments in the function.

Key Points

• Sample Variance: The VAR.S function assumes that the data you provide represents a sample from a larger population. If your data represents the entire population, you should use VAR.P instead.
• Formula: The formula for calculating sample variance is:

  $$\text{Variance} = \frac{\sum (x_i – \bar{x})^2}{n – 1}$$Where:

  • $x_i$ is each individual data point.
  • $\bar{x}$ is the sample mean.
  • $n$ is the number of data points in the sample.

  The formula divides by $n – 1$ instead of $n$ (as in the population variance formula) to correct for the bias in estimating the variance from a sample. This is called Bessel’s correction.

• Units: The variance is expressed in squared units (e.g., if the data is in meters, the variance will be in square meters).

Example Usage

1. Calculating Sample Variance

Suppose you have the following data representing the test scores of 5 students, which is a sample of a larger group of students:

To calculate the sample variance:

=VAR.S(A2:A6)

This will return the sample variance for the scores of the 5 students.

2. Variance for Multiple Data Ranges

If you want to calculate the sample variance for multiple data ranges, for example, two different sets of test scores:

=VAR.S(A2:A6, B2:B6)

This will calculate the sample variance for all the values in the ranges A2:A6 and B2:B6 combined.

Notes

• Sample vs Population Variance:
  • VAR.P: Used for calculating the variance of an entire population (where the data represents every data point in the population).
  • VAR.S: Used for calculating the variance from a sample (subset of a larger population). The correction for sample data (dividing by $n – 1$) helps account for the fact that a sample might not fully represent the population’s variability.
• Interpreting Variance: Variance gives you an idea of how spread out the data points are. A larger variance means the data points are more spread out from the mean, while a smaller variance indicates the data points are closer to the mean.

Related Functions

• STDEV.S: Calculates the sample standard deviation, which is the square root of the sample variance.
• STDEV.P: Calculates the population standard deviation, which is the square root of the population variance.
• VAR.P: Calculates the variance of an entire population (if the data represents the whole population).
• AVERAGE: Calculates the mean of a data set, which is the central value needed for variance calculation.
• COVARIANCE.S: Calculates the covariance of a sample between two data sets, which measures the degree to which two variables change together.

The VAR.S function is essential for calculating sample variance, which is particularly useful in statistics, research, quality control, and any field where data sampling is performed and the goal is to measure the variability of a sample from a larger population.