How to calculate variance in Excel?

How to calculate variance in Excel?

Excel has been the virtual mathematical and statistical expert for the digital world. With days, it has manifested itself in complex mathematical calculations. This makes it suitable for business audits editing and formatting and creates its position in certain other domains.

In statistical terms, finding mean, standard deviation, complex ratio, mid-range, assertion, percentages, estimation, and other multiple terms are now easily calculated through excel. It’s just selecting the rows with data cells and applying the formula on them. Let us drive on another statistical term named Variance and see how to calculate variance in Excel?

What is Variance?

Certainly, when we have a set of data, we need to analyze how far they vary from each other. For instance, if the data set is 10, 12, 22, 34, 56 …., here variance could be how far the range is fluctuating with respect to the mean of the data available.

Basically, the variance is used in experimental estimations deducing how the result of a certain test varies by changing certain parameters. Using variance, the businesspeople could lay a probability about whether they land up on some profitable grounds or not. In simpler terms, we could say it’s the relative comparison of every value in a set to the other values and to the mean of the entire set of values. Not completely, but variance does resemble a bit to the standard deviation.

Mathematical Formula for Variance:

Variance σ2=n−1∑i=1n(xi−xˉ)2


xi=ith of data value

xˉ=Mean of complete set of data

n=Number of data points

Read More:

How to Calculate Variance in Excel?

As mentioned, we can apply the direct variance formula over the data cells and get automated results in excel.

Procedure to Calculate the Variance in Excel:

Step 1: Align your data in a particular row as it becomes easy for the system to consider the data points under calculation.

Step 2: VAR.P(A1:A30), VAR.S(A1:A30) are the formulas to calculate Variance in Excel.

As you could see here both the formulas differ with some P and S characters. P represents the entire population and S represents the Sample. For instance, if you are implementing the formula on part of data to check whether it’s working properly or not, you should try with the .S formula. On the contrary, if you are about to apply the formula on the entire population of data use .P extension.

Here A1:A30 represents the cell numbers under calculation. This was the reason that in step 1 it is mentioned to align the data in a single row or column.

Step 3: And it’s done. Your results will appear in the subsequent column/row/cell.

Understanding variance results:

Now we have our variance info with us. Below are some studied and referenced estimations regarding the same:

  1. A higher value of variance illustrates that the numbers (data points) are farther from each other and form the mean value of all data points as well.

2. A smaller value of variance illustrates the numbers (data points) are nearer.

3. A zero variance condition states that all the data points are similar (identical).

Leave a Reply

Your email address will not be published. Required fields are marked *