Excel VAR function

Summary

The Excel VAR function calculates the variance of a dataset that represents a sample. Variance measures how much the numbers in a data set differ from the average (mean) value of that set. It’s a fundamental statistical function used to quantify the spread of data points. VAR is specifically designed for analyzing samples, making it ideal for scenarios where you’re working with a subset of a larger population.
Syntax
				
					=VAR(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 based on a 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 focuses on numeric values, excluding text, logical values (TRUE and FALSE), and empty cells from its calculation.

Examples

Simple VAR
Calculating Variance for Test Scores: To determine the variance among scores in a small class, highlighting the diversity of scores.
				
					=VAR(A1:A10)
				
			
If A1:A10 contains scores from a class test, VAR calculates the variance of these scores, providing insight into how varied the scores are from the class average.
VAR with Direct Values
Determining Variance Among Directly Specified Figures: To assess the financial variability in a set of expense figures.
				
					=VAR(500, 600, 550, 650, 700)
				
			
This formula calculates the variance of the directly specified expense figures, measuring how much these expenses deviate from their average, which can be useful for budget analysis.
VAR Across Multiple Ranges
Assessing Spread Across Diverse Data Sets: To evaluate the variance in performance ratings from different teams or groups.
				
					=VAR(B2:B10, D2:D10)
				
			
Assuming B2:B10 and D2:D10 contain performance ratings from two distinct groups, VAR assesses the combined ratings to compute the variance, indicating the overall spread or consistency in performance across the groups.

Additional Notes

  • VAR is best used when your data represents only a sample of the total population. If you’re analyzing an entire population, consider using VAR.P (or VARP in older versions of Excel) instead.
  • Excel offers several variations of the variance function to accommodate different data types and analysis needs, including VAR.S (which replaces VAR in Excel 2010 and later versions), VARA, and VAR.P.

Related Functions

Excel VAR.P function

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

Excel VAR.S function

The Excel VAR.S function estimates variance based on a sample, aiding in the analysis of data spread and dispersion.

Excel STDEV function

The Excel STDEV function estimates standard deviation based on a sample, vital for assessing data variability.

Content Navigation