How to calculate variance in Excel - VAR, VAR.S, VAR.P and other functions

In this tutorial, we will look at how to do variance analysis Excel and what formulas to use to find variance of a sample and population.

Variance is one of the most useful tools in probability theory and statistics. In science, it describes how far each number in the data set is from the mean. In practice, it often shows how much something changes. For example, temperature near the equator has less variance than in other climate zones. In this article, we will analyze different methods of calculating variance in Excel.

What is variance?

Variance is the measure of variability of a data set that indicates how far different values are spread. Mathematically, it is defined as the average of the squared differences from the mean.

To better understand what you are actually calculating with the variance, please consider this simple example.

Supposing there are 5 tigers in your local zoo that are 14, 10, 8, 6 and 2 years old.

To find variance, follow these simple steps:

  1. Calculate the mean (simple average) of the five numbers:
    The mean formula
  2. From each number, subtract the mean to find the differences. To visualize this, let's plot the differences on the chart:
    Variance in Excel
  3. Square each difference.
  4. Work out the average of the squared differences.

The variance formula

So, the variance is 16. But what does this number actually mean?

In truth, variance just gives you a very general idea of the dispersion of the data set. A value of 0 means there is no variability, i.e. all the numbers in the data set are the same. The bigger the number, the more spread out the data.

This example is for population variance (i.e. 5 tigers are the whole group you are interested in). If your data is a selection from a bigger population, then you need to calculate sample variance by using a slightly different formula.

How to calculate variance in Excel

There are 6 built-in functions to do variance in Excel: VAR, VAR.S, VARP, VAR.P, VARA, and VARPA.

Your choice of the variance formula is determined by the following factors:

  • The version of Excel you are using.
  • Whether you calculate sample or population variance.
  • Whether you want to evaluate or ignore text and logical values.

Excel variance functions

The below table provides an overview of the variation functions available in Excel to help you choose the formula best suited for your needs.

Name Excel version Data type Text and logicals
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

VAR.S vs. VARA and VAR.P vs. VARPA

VARA and VARPA differ from other variance functions only in the way they handle logical and text values in references. The following table provides a summary of how text representations of numbers and logical values are evaluated.

Argument Type VAR, VAR.S, VARP, VAR.P VARA & VARPA
Logical values within arrays and references Ignored Evaluated
(TRUE=1, FALSE=0)
Text representations of numbers within arrays and references Ignored Evaluated as zero
Logical values and text representations of numbers typed directly into arguments Evaluated
(TRUE=1, FALSE=0)
Empty cells Ignored

How to calculate sample variance in Excel

A sample is a set of data extracted from the entire population. And the variance calculated from a sample is called sample variance.

For example, if you want to know how people's heights vary, it would be technically unfeasible for you to measure every person on the earth. The solution is to take a sample of the population, say 1,000 people, and estimate the heights of the whole population based on that sample.

Sample variance is calculated with this formula:
Sample variance formula

Where:

  • x̄ is the mean (simple average) of the sample values.
  • n is the sample size, i.e. the number of values in the sample.

There are 3 functions to find sample variance in Excel: VAR, VAR.S and VARA.

VAR function in Excel

It is the oldest Excel function to estimate variance based on a sample. The VAR function is available in all versions of Excel 2000 to 2019.

VAR(number1, [number2], …)
Note. In Excel 2010, the VAR function was replaced with VAR.S that provides improved accuracy. Although VAR is still available for backward compatibility, it is recommended to use VAR.S in the current versions of Excel.

VAR.S function in Excel

It is the modern counterpart of the Excel VAR function. Use the VAR.S function to find sample variance in Excel 2010 and later.

VAR.S(number1, [number2], …)

VARA function in Excel

The Excel VARA function returns a sample variance based on a set of numbers, text, and logical values as shown in this table.

VARA(value1, [value2], …)

Sample variance formula in Excel

When working with a numeric set of data you can use any of the above functions to calculate sample variance in Excel.

As an example, let's find the variance of a sample consisting of 6 items (B2:B7). For this, you can use one of the below formulas:

=VAR(B2:B7)

=VAR.S(B2:B7)

=VARA(B2:B7)

As shown in the screenshot, all the formulas return the same result (rounded to 2 decimal places):
Calculating sample variance in Excel

To check the result, let's do var calculation manually:

  1. Find the mean by using the AVERAGE function:
    =AVERAGE(B2:B7)

    The average goes to any empty cell, say B8.

  2. Subtract the average from each number in the sample:
    =B2-$B$8

    The differences go to column C, beginning in C2.

  3. Square each difference and put the results to column D, beginning in D2:
    =C2^2
  4. Add up the squared differences and divide the result by the number of items in the sample minus 1:
    =SUM(D2:D7)/(6-1)

As you can see, the result of our manual var calculation is exactly the same as the number returned by Excel's built-in functions:
Sample variance formulas in Excel

If your data set contains the Boolean and/or text values, the VARA function will return a different result. The reason is that VAR and VAR.S ignore any values other than numbers in references, while VARA evaluates text values as zeros, TRUE as 1, and FALSE as 0. So, please carefully choose the variance function for your calculations depending on whether you want to process or ignore text and logicals.
Using the VAR, VAR.S and VARA functions in Excel

How to calculate population variance in Excel

Population is all members of a given group, i.e. all observations in the field of study. Population variance describes how data points in the entire population are spread out.

The population variance can be found with this formula:
Population variance formula

Where:

  • x̄ is the mean of the population.
  • n is the population size, i.e. the total number of values in the population.

There are 3 functions to calculate population variance in Excel: VARP, VAR.P and VARPA.

VARP function in Excel

The Excel VARP function returns the variance of a population based on the entire set of numbers. It is available in all versions of Excel 2000 to 2019.

VARP(number1, [number2], …)
Note. In Excel 2010, VARP was replaced with VAR.P but is still kept for backward compatibility. It is recommended to use VAR.P in the current versions of Excel because there is no guarantee that the VARP function will be available in future versions of Excel.

VAR.P function in Excel

It is an improved version of the VARP function available in Excel 2010 and later.

VAR.P(number1, [number2], …)

VARPA function in Excel

The VARPA function calculates the variance of a population based on the entire set of numbers, text, and logical values. It is available in all version of Excel 2000 through 2019.

VARA(value1, [value2], …)

Population variance formula in Excel

In the sample var calculation example, we found a variance of 5 exam scores assuming those scores were a selection from a bigger group of students. If you collect data on all the students in the group, that data will represent the entire population, and you will calculate a population variance by using the above functions.

Let's say, we have the exam scores of a group of 10 students (B2:B11). The scores constitute the entire population, so we will do variance with these formulas:

=VARP(B2:B11)

=VAR.P(B2:B11)

=VARPA(B2:B11)

And all the formulas will return the identical result:
Calculating population variance in Excel

To make sure Excel has done the variance right, you can check it with the manual var calculation formula shown in the screenshot below:
Population variance formula in Excel

If some of the students did not take the exam and have N/A instead of a score number, the VARPA function will return a different result. The reason is that VARPA evaluates text values as zeros while VARP and VAR.P ignore text and logical values in references. Please see VAR.P vs. VARPA for full details.
VARP and VARPA functions in Excel

Variance formula in Excel - usage notes

To do variance analysis in Excel correctly, please follow these simple rules:

  • Provide arguments as values, arrays, or cell references.
  • In Excel 2007 and later, you can supply up to 255 arguments corresponding to a sample or population; in Excel 2003 and older - up to 30 arguments.
  • To evaluate only numbers in references, ignoring empty cells, text, and the logical values, use the VAR or VAR.S function to calculate sample variance and VARP or VAR.P to find population variance.
  • To evaluate logical and text values in references, use the VARA or VARPA function.
  • Provide at least two numeric values to a sample variance formula and at least one numeric value to a population variance formula in Excel, otherwise a #DIV/0! error occurs.
  • Arguments containing text that cannot be interpreted as numbers cause #VALUE! errors.

Variance vs. standard deviation in Excel

Variance is undoubtedly a useful concept in science, but it gives very little practical information. For instance, we found the ages of the population of tigers in a local zoo and calculated the variance, which equals 16. The question is - how can we actually use this number?

You can use variance to work out standard deviation, which is a much better measure of the amount of variation in a data set.

Standard deviation is calculated as the square root of the variance. So, we take the square root of 16 and get the standard deviation of 4.

In combination with the mean, the standard deviation can tell you how old most of the tigers are. For example, if the mean is 8 and the standard deviation is 4, the majority of the tigers in the zoo are between 4 years (8 - 4) and 12 years (8 + 4).
Calculate variance and standard deviation in Excel

Microsoft Excel has special functions for working out standard deviation of a sample and population. The detailed explanation of all the functions can be found in this tutorial: How to calculate standard deviation in Excel.

That's how to do variance in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook to Calculate Variance in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

One Response to "How to calculate variance in Excel - VAR, VAR.S, VAR.P and other functions"

  1. Don Rector says:

    Thank you for the very useful and easy to understand lesson.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard