< Go Back

Excel VAR.S Function

Excel VAR.S Function

Excel VAR.S Function estimates variance based on sample data, ignoring logical values and text values in the sample data set.

Formula

The syntax of Excel VAR.S Function is:

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

It can accept from 1 to 255 arguments maximum as per the following description;

number1 – This is required and the first argument of sample data

[number2],… – These are optional and additional arguments of sample data

Explanation

Excel VAR.S Function was introduced in Excel 2010 to replace Excel VAR Function and is available in all later versions. It returns the result of variance as a numeric value based on the sample data set. Arguments in Excel VAR.S Function can be entered as numbers, named ranges, arrays or cell references. These arguments may contain numbers, text values, logical values, and empty cells, but Excel VAR.S Function ignores empty cells, text values, and logical values and these are not counted or evaluated in the calculation of variance if found in sample data set.

Example

For example, you have sample data of children heights in a school and you want to estimate variance based on this sample data set.

Because this sample data set may contain text and logical values and you want to ignore such values in variance calculation, therefore you need to use Excel VAR.S Function to get the accurate result by using following formula;

=VAR.S(A2:A12)  

Figure 1. VAR.S_Function

Notes

Keep in mind the following points when you use Excel VAR.S Function:

  • Excel VAR.S Function assumes that dataset represents as a sample. If data set represents entire population then use Excel VARP or Excel VAR.P function to calculate variance.
  • Excel VAR.S Function does not count text and logical values found in the sample data set. If you want to count text and logical values found in the sample data set then you need to use Excel VARA Function.
  • Text representations of numbers are counted when you directly enter them as an argument in Excel VAR.S Function.
  • It returns #VALUE!error when text value is entered directly as an argument.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar