How to calculate variance in excel exceldemy find a gas station near me

######

Let`s say we want to calculate the population variance of five data which have different values. For easier calculation make two columns which are for (X-μ) and (X-μ) 2 .Now for calculating the total number of data use the formula =COUNT(B2: B6). The resultant value will be N for this formula. For calculating the arithmetic mean for the individual values use the formula =AVERAGE (B2: B6). Use the formula =B2-$B$10 in cell C2 and drag the formulated cell downwards to copy this formula for the whole column to calculate the deviation about the mean (X-μ). To square, the deviation about the mean (X-μ) in a different column, use the formula =C2^2 and drag the formulated cell to copy the formula for the whole column. Then calculate the sum of the square of the deviation about the mean (X-μ) 2 using the formula =SUM (D2: D6). Then finally calculate the population variance by dividing the square of the deviation about the mean (X-μ) 2 by the total number of data. Here we found the result 82.16. The following picture shows the result and necessary data table for the calculation.

Here we will use the same example for the calculation. As you can see, there is only one difference between the previous formula and this. It is that the sample formula has a denominator of N-1. Previously the denominator was N. The whole calculation in Excel will be same. The only difference will be dividing the square of the deviation about the mean (X-μ) 2 by (N-1) instead of N. Here the calculated value is 102.7. Instead of 5, the deviation about the mean (X-μ) 2 is now divided by 4. So, with the same values of data, the sample variance is always greater than the population variance. The following picture shows the result and necessary data table for the calculation of sample variance.

The above description describes the detail calculation process of sample and population variance. In excel there are 4 built-in functions for calculating the variances. You just need to define the range for which you want to find the variance.

Suppose you want to calculate the population variance using the excel built-in function. Here we will be using the same example to see if our result matches with previous population variance result or not. The function that we will be using is VAR.P. This function gives the variance from a whole population. Put the range inside the brackets to perform the population variance just like this, =VAR.P(B2: B6). The following picture shows the population variance with VAR.P function.

There are other two functions to calculate the variance. They are VARA and VARPA. In VAR.P and VAR.S functions, the logical and text values are ignored. But VARA and VARPA evaluate the logical and text values in the calculation. The following picture shows the 4 functions together where different results are found.

VARA evaluates the simple variance whereas VARPA evaluates the population variance. The Excel 2007 and its earlier version don’t have these functions. They have VAR and VARP functions where the VAR calculate the sample variance and VARP calculates the variance based on an entire population. Conclusion

For proper visualization of statistical data, it is important to measure the variance. Without the variance, you can not measure the standard deviation. In this article, I showed different approaches for calculating the variance in excel. Following the step by step process for calculating the variance is a longer process of calculation. There are many functions for calculating the variance and these are faster than the step by step calculations.