Jump to content

Documentation/Calc Functions/VARA

From The Document Foundation Wiki

Function name:

VARA

Category:

Statistical Analysis

Summary:

Calculates the sample variance of a set of numbers.

VARA is a variant of the VAR function. The main difference between the two is that VAR ignores cells containing text, whereas VARA treats text values as the number 0.

Syntax:

VARA(Number 1[; Number 2[; ...[; Number 255]]])

Returns:

Returns a non-negative real number, which is the sample variance of the supplied numbers.

Arguments:

Number 1, Number 2, ..., Number 255 give the set of real numbers for which the sample variance is to be calculated. Each argument may be a value or an expression, a reference to a single cell, a reference to a cell range, the name of a named or database range, or an inline array.

Note that although VARA can accept up to 255 arguments, each argument could specify a range of cells. This means that the number of real numbers processed could be many more than 255.

The following conditions (including errors) may be encountered:

  • Text strings are treated as the number 0.
  • Empty cells are ignored.
  • If less than two numbers are supplied, then VARA reports a #DIV/0! error.

Additional details:

  • The formula for the sample variance ([math]\displaystyle{ s^2 }[/math]) is:
[math]\displaystyle{ s^2\:=\:\frac{1}{n-1}\sum_{i=1}^{n}(x_{i}-\bar{x})^2 }[/math]
where
  • [math]\displaystyle{ n }[/math] is the size of the sample
  • [math]\displaystyle{ x_{i} }[/math] is the i-th value in the sample
  • [math]\displaystyle{ \bar{x} }[/math] is the sample mean
  • For more information on variances, visit Wikipedia's Variance page.

Examples:

Formula Description Returns
=VARA(A1:A6) where cells A1:A5 contain the values 1, 2, 3, 4, and 5 respectively, with cell A6 empty. Here the function calculates the sample variance of the five values in cells A1:A5, ignoring the empty cell A6. 2.5
=VARA(A1:A6) where cells A1:A5 contain the same values as in the previous example, with cell A6 containing the string "Calc functions". Here the function calculates the sample variance of the five values in cells A1:A5 and the value 0 allocated for the text string in cell A6. Note that the formula =VARA(1; 2; 3; 4; 5; 0) returns the same value. 3.5
=VARA(3; 4; 5; 6; 2; 3; 1; "LibreOffice") Here the function calculates the sample variance of the first seven values passed as individual arguments, along with the value 0 allocated for the text string in the final argument. Note that the formula =VARA(3; 4; 5; 6; 2; 3; 1; 0) returns the same value. 4
=VARA({3, 4, -5, 6.05, 2, 3, -1.5}) Here the function calculates the sample variance of the seven values passed in an inline array. 13.7991666666667

Related LibreOffice functions:

AVERAGE

AVERAGEA

STDEV

STDEV.P

STDEV.S

STDEVA

STDEVP

STDEVPA

VAR

VAR.P

VAR.S

VARP

VARPA

ODF standard:

Section 6.18.83, part 2

Related (or similar) Excel functions:

VARA