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 about 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.

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. 

Variance Formula in Excel:

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 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

2. Subtract the mean value to get the difference. To represent it, plot the difference as:

3. Square each calculated difference.
4. 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.

If you want to learn how you can calculate variance in excel then, Get the best excel homework help from the experts

How to calculate variance in excel using the functions?

There are different functions to calculate the variance in Excel, which are: VAR.S, VAR.P, VARPA, VAR, VARP, and VARA. One can use any of the variance formulas 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.

Methods for how to calculate variance in excel (sample)

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 now for 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 versions of 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, and 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 a 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)

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

=B2-$B$8.

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

=C2^2

4. 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:

Different excel variance functions

How to calculate variance in excel (population)

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. But let’s first know its formula:

Population Variance Formula:

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:

Pooled Variance Formula

In the statistics, the Pooled variance is a method used for estimating the variance of many different populations when the mean of each population may be different, but one can assume that the variance of each population is the same. 

With the use of this method when you get the result of a numerical estimate, you can call this pooled variance.

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 the 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 including various types of functions 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 accessibility 24/7. So, avail of our statistics services and score A+ grades in your academics. Get the best excel homework help from the experts

Also, Read

• How to Find Z Score in Excel
• How to Find Range in Excel
• How to Calculate Variance In Excel Using Different Functions

Frequently Asked Questions