Excel VAR.S() Function

The VAR.S() function (also commonly known as the VARS function) is a statistical function in Excel. The main purpose of this function is to help the user to calculate the variance of a given sample data set. Now, let's have a look at the detailed definition of this function.

The VAR.S() function gives the variance of the given set of values or range of values. It is basically the sum of squares of deviation of a value from its mean divided by the number of values. It is the square of standard deviation and just like for standard deviation, the lower the variance, the better, as it implies that the data is not that different. Blank cells, text, and error values are ignored.

Although the VAR.S will ignore the text and logical values that will be passed into as cell references, it will evaluate logical values and numbers values passed directly as arguments. In simple terms, we can say that this function will ignore TRUE when it arrives in a range like B2:B11 but will consider the TRUE when it is passed as an argument.

The VAR.S() function was not introduced until the Excel 2010 version. Before this, the Excel users used the VAR function. The VAR.S function is an upgraded version of the VAR function; therefore, there is not much of a difference. If you want to calculate the variance of an entire population, then go for the Excel VAR.P function (also commonly known as VARP). Both the functions work the same way.

NOTE: The VAR.S() function ignores the blank, text, error and non-numeric values, including the Boolean values.

Syntax

Parameter

• number1 (required): This parameter represents the first number or reference to calculate variance. The first number is compulsory.
• [number2] and so on..(optional): Numbers for which you want to find variance. In VAR.S() function, these numbers can be provided as single numbers, cell references, ranges, arrays.

NOTE: You can provide up to 254 number arguments or arrays of values to the VAR.S function.

IMPORTANT POINTS facts to know about VAR.S

1. The VAR.S() function considers the given arguments as a sample of the data set, not an entire population. If you want to calculate the variance of an entire population, use Excel VAR.P function.
2. The VAR.S() function only considers the numbers in references, ignoring blank cells, text, error and Boolean values like TRUE or FALSE.
3. The passed arguments can contain numbers or names, arrays, or cell references that include numbers.
4. The arguments can be hard-coded values instead of cell references.
5. If you want to evaluate logical values (TRUE or FALSE) and/or text in cell references, use the Excel VARA() function.

Examples

Example 1: Calculate variance for the following set of numbers.

Follow the below steps to calculate the variance of the above set of numbers:

Step 1: Open the Excel worksheet and paste the above data.

Step 2: Insert another row with the name VAR.

Step 3: Apply the VAR.S formula and, in the numbers argument, pass the range of numbers. Simply select the cells in the column, and Excel will calculate the variance for you.

Step 4: Drag the formula to the subsequent columns, and you will have the following output.

NOTE: Use the STDEV.S function to calculate standard deviation of these numbers and then use SQRT function to calculate square root of the variance - results will be same

Example 2: Calculate variance for the following set of numbers

Follow the below steps to calculate the variance of the above set of numbers:

Step 1: Open the Excel worksheet and paste the above data.

Step 2: Insert another row with the name VAR.

Step 3: Apply the VAR.S formula and, in the numbers argument, pass the range of numbers. Simply select the cells in the column, and Excel will calculate the variance for you.

Step 4: Drag the formula to the subsequent columns, and you will have the following output.

Var.S Function Errors

If the Excel Var.S Function return any error, it will be likely because of any one of the following reasons:

• #DIV/0! - This error occurs if you have supplied less than 2 numeric values to the Var.S Function.
• #VALUE! - This error occurs if the values provided in the arguments of Var.S function are text values that cannot be converted to numbers.

Difference in VAR.P & VAR.S function

NOTE: The VAR() and VAR.S() function return the exact same output, so we can use either function to calculate the sample variance for a given dataset.