How to calculate variance in excel

How to Calculate Variance In Excel Using Different Functions

Want to know how to calculate Variance in Excel? 

But wait!! 

Do you even know how you can use variance in real life?

Some of you might not have any idea regarding it. Let me explain first where you can see that application of variance.

Example 1: You all have heard that the stock market has an average return of some percentage. It does not mean each year you get that percentage return. It can be less in a year or higher in the next year. Now, this is the variance or variability that you see in the stock market.

Another Example: Suppose you drive to the office and take a route daily. But you found that there is variability in the daily timing when you reach the office. Well, the variability could be because of the stop-light timing, traffic, and more. Moreover, the variability in the gas is also seen. All these variabilities can be calculated using the concept of variance.

Another question is why use Excel for calculating variance. The simple answer to it is:

  • Excel offers several statistical formulas to apply directly. 
  • Apart from this, Excel can help you to manage large data collected from different sources.
  • Excel also makes your work easier and simple. 

Now, let’s jump to the details about how to calculate variance in Excel. But first, know what variance is (technically).

What is the variance?

It is the calculation of the spread numbers in a data set file. Or it is the measurement of the variability of the given data set that represents how far a difference value is spread. In the mathematical term, it is the average of the squared of the calculated difference from the mean. 

For a better understanding of how to calculate variance in excel, let’s take an example of it:

Assume, there are 5 monkeys in a zoo that are of 10,6,14,8, and 2 years old. Find the variance of it.

  1. Measure the mean of these five numbers:

Mean = (10+6+14+8+2)/5 = 8

  1. Subtract the mean value to get the difference. To represent it, plot the difference as:
  1. Square each calculated difference.
  2. Measure the average of the square differences:

variance = {6^2+2^2+0^2+(-2)^2+(-6)^2}/5 = 16.

Therefore, the variance of the given data is 16.

Things you should keep in mind before calculating Variance in Excel
There are some of the things that you should know before calculating the variance. Particularly, you should consider:
While VAR.S and VAR can change technically, VAR.S can replace the Excel function for the data. Also, VAR.P for the population data set must use over the VARP as the new function.
For counting logical or text values, you should use VARPA (for population) or VARA (for samples).
A single argument (value 1) is necessary for the reference to a cell’s range. But finding the variance from the single value is quite redundant. Therefore, you should use more arguments for this.
Users can also use the reference to cell ranges or reference every value individually.
VAR.S, VARP, VAR, and VARP only support the value in the numeric.

How to calculate variance in excel using the functions?

There are different functions to calculate the variance in Excel, that are: VAR.S, VAR.P, VARPA, VAR, VARP, and VARA. One can use any of the variance formula to determine factors such as:

  • Whether one measures population or sample variance. 
  • The excel version which one is using.
  •  Whether one wishes to ignore or evaluate logical and text values.

Different excel variance functions

We have provided a brief description of the various functions that are accessible in Excel that help one to select the formula as per the need.

Name Excel versionData typeLogical and Text 
VAR.S2010-2019SampleIgnored
VAR.P2010-219PopulationIgnored
VARPA2000-2019PopulationEvaluated
VAR2000-2019SampleIgnored
VARP2000-2019PopulationIgnored
VARA2000-2019Sample Ignored 

Also Read

Methods for how to calculate variance in excel (sample)

https://statanalytica.com/blog/wp-content/uploads/2021/08/Simple-variance.mp4

The sample is the set of the extracted data from the whole population, and the variance which is used for calculating the sample value is known as sample variance.

Let’s take an example, if one wants to calculate the individual’s height, then it is calculated for 1000 individuals, and the assumed height of the entire population is based on the sample.

Sample variance formula to calculate the value as:

Where x bar is the simple average of the given values, and n is the number value of the sample.

Then the three different sample variance is used to calculate by VAR, VAR.S, and VARA.

VAR function for sample variance in excel

It is one of the oldest functions in excel to predict the variance of the sample. The VAR function is accessible for the excel 2000 to 2019 version.

VAR (number1, [number2], ….)

NOTE: In 2010 excel, this function was replaced by VAR.S, which helps in improving accuracy. But, VAR is also available till now for the backup compatibility, in the current excel version, VAR.S is used for calculating the variance in excel.

VAR.S function for sample variance in excel

It is one of the modern counterparts for the current version of Excel, which is used in excel 2010 and later version of the excel. 

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

VARA function for sample variance in excel

It is used to return the value of sample variance, which is based on the set of text, numbers, and logical values.

VARA (value1, [value2], …)

Example of sample variance in excel

When one is working with the numeric sets of data, then they can use the functions to measure the sample variance of an excel sheet. Let’s take an example of it, measure the variance of the sample, including the 6 items (B2: B7). then, the formula will be as:

= VAR (B2: B7)

=VAR.S (B2: B7)

=VARA (B2: B7)

Add simple variance video here

To check the accuracy of the data, let’s calculate it manually too:

  1. Calculate the average using the AVERAGE function as to put in the empty cell B8:

= AVERAGE (B2: B7)

  1. Subtract the calculated average value from every number of the sample and put the value in column C2.

=B2-$B$8.

  1. Now, square each calculated difference and keep in data in column D2:

=C2^2

  1. Sum up the square of the difference and divide the outcome with the total number of data which is sample minus 1:

=SUM (D2: D7) / (6-1)

Now, it will represent the data as given below table:

How to calculate variance in excel (population)

https://statanalytica.com/blog/wp-content/uploads/2021/08/Population-variance.mp4

The population can be defined as a member of a given group, which is the observed value in a field of study. It is used to describe the data points of the spread entire population. The formula of population variance can be calculated as:

Where x bar is the mean value of the given population, and n is the size of the given population.

There are three different functions that are used to measure the population variance in excel: VARP, VAR.P, and VARPA.

VARP function for population variance in excel

This function is used to return the population variance, which is based on the whole set of population numbers. It can be accessible in the excel version of 2000 to 2019.

VARP(number1), [number2], ….)

NOTE: For the excel version of 2010, VARP is replaced by VAR.P, and it is suggested that try to use VAR.P in excel as the VARP function would be accessible for future excel versions.

VAR.P function for population variance in excel

It is used as the improved VARP function, which is accessible for excel 2010 and later versions.

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

VARPA function for population variance in excel

It is used to measure the population variance value for the entire number of the set, logical value, and text. It is used in excel 2000-2019 versions.

VARA (Value1, [value2], ….)

Example of population variance in excel

In this example, there is an exam score of 5 students, which has the maximum value from the entire group of sets. If you gather data from the students, then the data represent the whole population and calculate the population variance.

Let’s take an example of 10 students (B2: B11), so the variance can be calculated as:

= VARP (B2: B11)

= VAR.P (B2: B11)

= VARPA (B2: B11)

Add simple variance video here

Excel can be the best option to calculate the variance, and one can judge the manual var as:

 

Useful notes for how to calculate variance in excel

To calculate the variance in excel, follow the simple norms that make it easy to use:

  • Give arguments as arrays, values, or cell references.
  • For the 2007 or later version of Excel, one can use up to 255 arguments for a population or sample, but for 2003 or older versions of Excel, one can use 30 arguments.
  • To get the value of references, ignore the text, empty cells, and logical values. Use the function VAR.S or VAR to calculate sample variance whereas for the population variance, use VAR.P or VARP functions.
  • To get the text and logical values in references, VARPA or VARA functions can be used.
  • Give approximately two numeric values for using the sample variance formula and a single numeric value for population variance formula in the Excel sheet.
  • The arguments that contain the text can not be interpreted and cause #VALUE! Errors for the numbers.

Conclusion

This blog has provided information on how to calculate variance in excel that include various types of the function used to measure sample and population variance. These functions are used for different versions of Excel, which start from 2000 to the 2019 year. One can easily use these functions and do their work with ease.

So, whenever one needs to calculate the variance of the excel data, use these above-mentioned functions, and measure the value easily. If you are facing any problem related to the statistics assignments, then you can contact our experts who are known for their high-quality content with zero plagiarism. They can deliver the data before the deadlines, and one can use our services at reasonable prices with the accessibility of 24/7. So, avail of our statistics services and score A+ grades in your academics. Get the best excel homework help from the experts

Frequently Asked Questions

What exactly is a variance?

In terms of statistics and probability, variance is the method to calculate how far the set is spread out. Moreover, it shows how much the random figure differs from the imaginary or expected value. We can define variance as the average of the differences’ square between the observed and expected value.

What are the different types of variance?

There are different types of variances. Some of those are:

Cost Variances.
Fixed Overhead Variance.
Profit Variance.
Labour Variances.
Material Variances.
Overhead (Variable) Variance.
Sales Variance.