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.

- Measure the mean of these five numbers:

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

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

- Square each calculated difference.
- 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, andVARA. 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 version | Data type | Logical and Text |

VAR.S | 2010-2019 | Sample | Ignored |

VAR.P | 2010-219 | Population | Ignored |

VARPA | 2000-2019 | Population | Evaluated |

VAR | 2000-2019 | Sample | Ignored |

VARP | 2000-2019 | Population | Ignored |

VARA | 2000-2019 | Sample | 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:

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

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

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

**= AVERAGE (B2: B7)**

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

**=B2-$B$8.**

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

**=C2^2**

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

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

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.