How to use VAR.S Function in Excel

The Excel VAR.S function returns the variance of a sample. If data represents the entire population, use the VAR.P function. VAR.S ignores text values and logicals in references.

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

The VAR.S function syntax has the following arguments:

Number1 Required. The first number argument corresponding to a sample of a population.
Number2, … Optional. Number arguments 2 to 254 corresponding to a sample of a population.

Example: Let’s look at some Excel VAR.S function examples and explore how to use the VAR.S function as a worksheet function in Microsoft Excel:

Suppose we are given data about the height of children from three cities (in cm), as shown below:

VAR.S Function in Excel 1 - How to use VAR.S Function in Excel

The variance of the heights of the sample group is calculated in cell B16 of the spreadsheet. The formula for this, (shown in the formula bar), is:

Syntax: =VAR.S(B3:B14,D3:D14,F3:F14)

Result: 40.36

VAR.S Function - How to use VAR.S Function in Excel

The calculated variance of the individual heights of the sample group is 40.36 cm.

The example above shows the arguments to the Var.S function being input in the form of 3 cell ranges. However, you can also input figures directly, as individual numbers or number arrays.

For example, if you wanted to include two further heights, of 163cm and 155cm into the sample, you could add these directly into the above function as follows:

Either as individual numbers:

Syntax: =VAR.S(B3:B14,D3:D14,F3:F14,163,155)

Result: 39.06

VAR.S Function 1 - How to use VAR.S Function in Excel

or, as an array of numbers:

Syntax: =VAR.S(B3:B14,D3:D14,F3:F14,{163,155})

Result: 39.06

VAR.S Function 2 - How to use VAR.S Function in Excel

Note:

VAR.S assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance by using VAR.P.
Arguments can either be numbers or names, arrays, or references that contain numbers.
Logical values, and text representations of numbers that you type directly into the list of arguments are counted.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
Arguments that are error values or text that cannot be translated into numbers cause errors.
If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARA function.
VAR.S uses the following formula:

where x is the sample mean AVERAGE(number1,number2,…) and n is the sample size.