How to Calculate Variance In Excel Using Different Functions

Variance is the most useful tool for statistics and probability theory. It can be used to describe how far a number of a data set is from the given mean value in science. Whereas in real practical use, it can be used to show how much the data changes. For example, the value of the temperature nearer to the equator has the least variance value as compared to the other climate zones. Therefore, in this post, you will find the ways for how to calculate variance in excel of a given sample or the given population. So, let’s check all the methods for it.

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.

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 

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:

Sample variance formula

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)

Calculating sample variance in Excel

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:

Sample variance formulas in Excel

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:

Population variance formula

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)

Calculating population variance in Excel

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

Population variance formula in Excel

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

0 0 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments