VARA
The VARA function returns the sample (unbiased) variance — a measure of dispersion — of a collection of any values.
VARA(value, value…)
value: Any value. value can contain a single value or collection. All values must be of the same value type (except that string values and boolean values can be included with number values). A minimum of two values are required. A string value can be included in a referenced cell, but can’t be directly entered as an argument to the function.
value…: Optionally include one or more additional values or collections of values.
Notes
The VARA function finds the sample (unbiased) variance by dividing the sum of the squares of the deviations of the data points by one less than the number of values.
It is appropriate to use VARA when the specified values represent only a sample of a larger population. If the values you are analysing represent the entire collection or population, use the VARPA function.
The function assigns a value of 0 to any string value, 0 to the boolean value FALSE, and 1 to the boolean value TRUE and includes them in the computation, if all other values are numbers. If there are date/time values or duration values included, the function returns an error. Empty cells are ignored.
The square root of the variance returned by the VARA function is returned by the STDEVA function.
Example |
|---|
Suppose you installed a temperature sensor in a house in California. The sensor records each day’s high and low temperatures. The data from the first few days of July is shown in the following table and is used as a sample for the population of high and low temperatures (note that this is an example only; this would not be statistically valid). On July 5, the sensor failed, so the data in the table shows n/a, or not available. |
A | B | C | |
|---|---|---|---|
1 | Date | High | Low |
2 | 01/07/2010 | 58 | 58 |
3 | 02/07/2010 | 84 | 61 |
4 | 03/07/2010 | 82 | 59 |
5 | 04/07/2010 | 78 | 55 |
6 | 05/07/2010 | n/a | n/a |
7 | 06/07/2010 | 81 | 57 |
8 | 07/07/2010 | 93 | 67 |
=VARA(B2:B8) returns approximately 1011.66666666667, the dispersion (variance is a measure of dispersion) as measured by VARA, of the sample of daily high temperatures. If you had a large data set that could not easily be visually scanned, or you wished to automate checking for missing values, you could compare the results of =VAR(B2:B8), which returns approximately 135.066666666667, and VARA, which returns approximately 1011.66666666667. If (as in this case) they are not equal, it would indicate the data set contains text (such as “n/a”), or one or more boolean values (TRUE or FALSE). |