Function: VAR

The VAR function is a statistical function calculates and returns the variance of a sample. The variance provides a general idea of the spread of data, and can be used to calculate the standard deviation or determining risk of an investment. In this guide, we’re going to show you how to use the VAR function and also go over some tips and error handling methods.

Note that this function is replaced with VAR.S function in Excel 2010. Although this function is still available for backwards compatibility, we recommend using the VAR.S instead for better accuracy and future-proofing.

Supported versions

Excel 2003 and newer versions

VAR Function Syntax

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

Arguments

number1	The first number argument corresponding to a sample of a population.
[number2]	Optional. Any other number arguments. Up to 254 can be entered corresponding to a sample of a population.

Example

The VAR function accepts numeric values as its arguments. Any type of text or logical values will be ignored. You can use range references or static values just like in any other formula.

The function calculates the variance using the following function:

x: sample mean (average)
x ̅: sample element
n: sample size

Here is the comparison of the two approaches:

=VAR(sample)

=SUMPRODUCT(POWER(AVERAGE(sample)-sample,2))/(COUNT(sample)-1)

The example below uses a formula with the named range sample (B5:B9).

Download Workbook

Tips

In most cases, we recommend using the VAR.S function instead of the outdated VAR function.
The function assumes that its arguments are a sample from a population. If your data represents the entire population, use VAR.P.
The function ignores text and logical values like TRUE and FALSE. If you want to evaluate text values and FALSE as 0 and TRUE as 1, use the VARA function.
Empty cells are ignored.

Issues

Any error in the arguments will cause the function to return an error.