Practice free

VAR.S function

VAR.S returns the sample variance, dividing by n-1, for data that is a sample of a larger group. It is the usual choice for analysis and needs two or more numbers.

Example

Read it as a sentence: estimate the variance of the sample in A1:A8, which gives 4.571428571.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

number1 (required)
First number or range.
number2 (optional)
More numbers or ranges.

Returns: The sample variance.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=VAR.S({2,4,4,4,5,5,7,9}) 4.571428571 Array constant
=VAR.S(10,"20",TRUE) 90.33333333 Direct args: number, text-number, TRUE
=VAR.S({1,"2",TRUE,3}) 2 Array with text and boolean
=VAR.S(Data!A1:A8) 4.571428571 Range of numbers
=VAR.S(Data!B1:B8) 233.3333333 Mixed range: num, text-number, TRUE, blank, text, FALSE
=VAR.S(Data!A1:A4,Data!F1:F5) 1.694444444 Two ranges
=VAR.S(10,"abc") #VALUE! Direct arg: non-numeric text
=VAR.S(5) #DIV/0! Single number

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Needs two or more numbers.
  • Ignores text and logicals in ranges.

Where it works

Excel
VAR.S arrived in Excel 2010. Older versions do not have it.
Google Sheets
Same name, but behaves differently: `VAR`. Sheets has VAR and VAR.S.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .