Variance is an important metric in statistics, and it can help you calculate like the risk of an investment. In this guide, we’re going to show you how to calculate variance in Excel.
What is variance?
Variance is the average of the squared differences from the average or mean. You can calculate the variance of a data by taking the differences between each number in the data set and the average, then squaring the differences (which makes them positive), and finally dividing the sum of the squares by the number of values in the data set.
The formula is as follows:
- σ²: variance
- x: mean (average) of data
- x ̅: data
- n: data size
A large variance value shows that numbers are far from the mean and each other. A small one, on the other hand, means an opposite correlation. Zero variance means that all numbers in the data set are identical.
Sample variance
If the data set does not represent the entire population, but a number of items from it is sample variance. A common example for this is an election poll.
The formula of a sample variance is almost the same except for the data size. Instead of using exact an data size, use minus 1.
How to calculate variance in Excel
Although, you can calculate variance in Excel by creating the same formula as above, there are built in functions that can make this even easier. The following table shows the formulas and properties that distinguish them.
| Function | Variance type | Excel Version | Text and logical values |
| VAR.S | Sample | 2010 | Ignored |
| VARA | Sample | 2000 | Evaluated |
| VAR | Sample | 2000 | Ignored |
| VAR.P | Population | 2010 | Ignored |
| VARPA | Population | 2000 | Evaluated |
| VARP | Population | 2000 | Ignored |
Each function uses the same syntax. You can provide data as references or static values. Aside from the sample versus population choice, you need to decide whether you want text and logical values to be evaluated as numbers. VARA and VARPA functions evaluate text and FALSE logical value to zero (0) and TRUE to 1.
The following example shows what each function returns for the same data set. The data set is at B5:B10 under the name “data”.
Please note that VAR and VARP functions have been updated with VAR.S and VAR.P functions in Excel 2010. Although Microsoft continues to support these functions for backwards compatibility, they encourage using VAR.S and VAR.P instead.
