Question

What Excel formula does one use to calculate the variance between two cells?

Answer 1

To understand how one uses to calculate the variance between two cells, we will first understand the meaning of the term ‘Variance’ of a probability distribution. Variance tells us how far is a number in the data set from the mean. It is one of the most important tools in probability distribution and statistics.

Complete step by step solution:
Let us first see how to calculate variance mathematically. Let us suppose we have a data set of five items as: 1, 2, 3, 4 and 5.
The mean of this data set is equal to:
$\begin{aligned} & =\dfrac{1+2+3+4+5}{5} \\\ & =3 \\\ \end{aligned}$
So, the variance is the distance of each data from the mean. So, we will subtract mean from each number to get the variance. This can be represented with the help of following table:

Data	Mean	Variance
1	3	-2
2	3	-1
3	3	0
4	3	1
5	3	2

Now, in an Excel formula, there are six built-in functions to do variance. These are: VAR, VAR.S, VARP, VAR.P, VARA and VARPA.
The choice of our variance formula depends on the version of Excel, whether we are calculating sample or population variance and whether we want to solve or ignore text and logical data.
Considering all these factors in mind, the proper order of these built-in variance Excel formula could be written as:

Name	Excel Version	Data Type	Text and logical
VAR	2000-2019	Sample	Ignored
VAR.S	2010-2019	Sample	Ignored
VARA	2000-2019	Sample	Evaluated
VARP	2000-2019	Population	Ignored
VAR.P	2010-2019	Population	Ignored
VARPA	2000-2019	Population	Evaluated

Thus, the above data table could be used to get the Excel formula to calculate variance of between two cells according to their data type.

Note: Whenever calculating the variance analysis in Excel, one should provide arguments as values, arrays and cell-references. To evaluate only numericals ignoring empty cells, use the VAR or VAR.S . And to evaluate text and logical values, use the VARA or VARPA function.