Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
VAR
The VAR function returns the sample (unbiased) variance — a measure of dispersion — of a set of numeric values.
VAR(value, value…)
value: A number value or date/time value or a collection of these value types. All values must be of the same value type and a minimum of two values are required.
value…: Optionally include one or more additional values or collections of values.
Notes
The VAR 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 VAR 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 VARP function.
The square root of the variance returned by the VAR function is returned by the STDEV function.
Examples |
|---|
Suppose you administered five tests to a group of students. You arbitrarily selected five students to represent the total population of students (note that this is an example only; this would probably not be statistically valid). Using the sample data, you could use the VAR function to determine which test had the widest dispersion of test scores. This might be useful in determining lesson plans, identifying potential problem questions or for other analysis. You enter the test scores into a blank table, with the scores for each student in the sample in columns A to E and the five students in rows 1 to 5. The table would appear as follows. |
| A | B | C | D | E |
|---|---|---|---|---|---|
1 | 75 | 82 | 90 | 78 | 84 |
2 | 100 | 90 | 95 | 88 | 90 |
3 | 40 | 80 | 78 | 90 | 85 |
4 | 80 | 35 | 95 | 98 | 92 |
5 | 90 | 98 | 75 | 97 | 88 |
=VAR(A1:A5) returns approximately 520, the sample variance of the results of Test 1. =VAR(B1:B5) returns approximately 602, the sample variance of the results of Test 2. =VAR(C1:C5) returns approximately 90.3, the sample variance of the results of Test 3. =VAR(D1:D5) returns approximately 65.2, the sample variance of the results of Test 4. =VAR(E1:E5) returns approximately 11.2, the sample variance of the results of Test 5. Test 2 had the highest dispersion (variance is a measure of dispersion), followed closely by Test 1. The other three tests had lower dispersion. |
Example — Survey results |
|---|
To see an example of this and several other statistical functions applied to the results of a survey, see the COUNTIF function. |