Excel VAR.S function


The Excel VAR.S function calculates the variance based on a sample of a dataset. This function is used to estimate how much the sample values differ from the average value of the sample. It’s an essential tool for statistical analysis when working with a subset of a larger population, providing insights into the variability of data points within the sample.
					=VAR.S(number1, [number2], ...)
  • number1: The first number, cell reference, or range in the sample.
  • number2, …: [Optional] Additional numbers, cell references, or ranges, up to 255.
Return value
The variance of the sample.

How to use

To calculate the variance for a sample, directly input numbers or specify cell references/ranges that contain the sample data. VAR.S is focused on numeric values and excludes text, logical values (TRUE and FALSE), and empty cells from its calculation.


Simple VAR.S
Estimating Variance for Sample Test Scores: To calculate the variance among scores from a sample of students to understand score dispersion.
If A1:A30 contains a set of test scores from a sample of students, VAR.S computes the variance, providing a statistical measure of the spread of scores around the average score.
VAR.S with Direct Values
Measuring Variability Among Specified Figures: To find the variance in a set of directly specified investment returns.
					=VAR.S(5%, 7%, 5%, 8%, 6%)
This formula calculates the variance of the investment returns, highlighting the variability among these returns relative to their average, useful for assessing investment risk.
VAR.S Across Multiple Ranges
Evaluating Dispersion Across Diverse Sample Sets: To determine the variance in customer satisfaction ratings from different product trials.
					=VAR.S(B2:B20, D2:D20)
Assuming B2:B20 and D2:D20 contain satisfaction ratings from samples of customers for two separate product trials, VAR.S assesses the combined ratings to compute the variance, indicating the overall variability in customer satisfaction.

Additional Notes

  • VAR.S should be used when the data represents a sample of the total population. If you have data for an entire population, VAR.P (or VARP in older Excel versions) is the more appropriate choice.
  • The distinction between VAR.S and VAR.P is crucial for accurate statistical analysis, as it impacts how variance is calculated and interpreted, influencing conclusions drawn from the data.

Related Functions

Excel VAR function

The Excel VAR function calculates variance based on a sample, important for understanding data dispersion.

Excel VAR.P function

The Excel VAR.P function computes variance using the entire population, crucial for accurate population variance assessments.

Excel STDEV.S function

The Excel STDEV.S function estimates the standard deviation using a sample, optimizing for sample-based variability analysis.

Content Navigation