VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel

VAR.P function - Function that calculates variance based on the entire set, ignoring logical values and text in Excel
VARA - Function that calculates variance based on a sample, including logical values and text in Excel

The following article introduces you to the VAR.S function - one of the functions in the statistical function group is very popular in Excel.

VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel Picture 1

Description: The function performs a calculation of variance based on a sample, ignoring logical values and text. Support functions from Excel 2010 onwards.

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

Inside:

- number1, [number2], .): The arguments in the whole need to calculate the variance, where number1 is the required argument, the next arguments are optional and contain up to 254 number arguments .

Attention:

- The VAR.S function assumes that its arguments are a sample of the population.

- Arguments can be numbers, names or arrays that contain numbers.

- If the argument is an array or a reference, only values in that array are used.

- When typing logical values and presenting numeric values into function arguments -> these values are still calculated.

- Function error occurs when arguments are text or error values cannot be converted.

- If you want to calculate both logical values and text -> use VARA.

- The equation to calculate the VAR.S function is:

[frac {{sum {{{left ({x - overline x} right)} ^ 2}}}} {{left ({n - 1} right)}}]

Inside:

+ x is the sample mean AVERAGE (number1, number2, .)

+ n is the sample size.

For example:

Calculate variance based on a sample of number values in the following data table:

VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel Picture 2

- In the cell to calculate enter the formula : = VAR.S (D6: D10)

VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel Picture 3

- Press Enter -> variance based on a sample of number values is:

VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel Picture 4

-Where the values are text or logic -> the function ignores these values:

VAR.S function - Function that calculates variance based on a sample, ignoring logical values and text in Excel Picture 5

Above are instructions and some specific examples when using the VAR.S function in Excel.

Good luck!

ignoring logical values and text in Excel

Jessica Tanner

Update 19 May 2020

You should read it

May be interested

MINA function - The function returns the smallest value in the given numbers including text and logical values in Excel
mina function: the function returns the smallest value in the given numbers including text and logical values. syntax: mina (value1, [value2], ...)
AVERAGEA function - The function returns the average of the arguments including numeric, text, and logical values in Excel
averagea function: the function returns the average of the arguments including numeric, text, and logical values. syntax: averagea (value1, [value2], ...)
FREQUENCY function - Function that calculates and returns the frequency of occurrences of values in a range in Excel
frequency function: function that calculates and returns the frequency of occurrences of values within a certain range and returns a vertical number array. syntax: frequency (data_array, bins_array)
STDEV function - The function of estimating standard deviations based on a sample in Excel
in the statistical probability of determining the corresponding deviation between samples is very meticulous and detailed, there is still some confusion. the following article details the stdev function that helps you estimate the standard deviation based on an existing sample.
How to use the SUMIF function in Excel
the sumif function in excel is a function used to compute values in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.
How to use the MAXA function in Excel, detailed examples
the maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.
MINA function in Excel, how to use and examples
the mina function in excel helps determine the smallest value in a range of numbers, including logical values and text numbers. with simple syntax, mina supports optimizing the calculation process in spreadsheets.
SUMPRODUCT function in Excel: Calculates the sum of corresponding values
the sumproduct function is an extremely useful function when you have to deal with a lot of data numbers in microsoft excel. here are the things you need to know about the sumproduct function in excel.
AND function in Excel
logical functions are used a lot in excel with results that return true, false. the and function is a very common logical function in excel, but for those of you who are new to excel, it may not be clear.
Logical functions (logical) in Excel
logical functions are used a lot during data processing in excel. the article summarizes the syntax and functions of functions in logical function groups in excel.