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

VARA - Function that calculates variance based on a sample, including logical values and text in Excel
vara function: the function performs a calculation of variance based on a sample, including logical values and text. syntax: vara (value1, [value2], ...)
VARPA function - Function that calculates variance based on the entire set, including logical values and text in Excel
varpa function: function that calculates variance based on the entire set, including logical values and text. syntax: varpa (value1, [value2], ...)
Z.TEST function - The function returns one-sided value of the z test in Excel
z.test: the function returns the one-sided p value of the z test. support functions from excel 2010 onwards. syntax: z.test (array, x, [sigma])
LINEST function - The function returns a line description array using the least square method in Excel
linest function: the function returns the line description array using the least squares method. syntax: linest (known_ys, [known_xs], [const], [stats])
CELL function - The function returns information about the format or content of a cell in Excel
cell function: the function returns information about the format or content of a cell. syntax: cell (info_type, [reference])
ERROR.TYPE function - The function returns the number corresponding to one of the error values in Microsoft Excel
error.type function: the function returns the number corresponding to one of the error values in microsoft excel, or the # n / a error if there are no errors. syntax: error.type (error_val)