EDUCBA

Home » Excel » Excel Resources » Statistical Functions in Excel » Excel Variance

Variance in Excel

Excel Variance (Table of Contents)

Introduction to Variance in Excel

Variance is used in cases if we have some budgets, and we may know the variances observed in implementing the budgets. In some cases, the term variance is also used to calculate the difference between the planned and the actual results. Variance calculation is a great way for data analysis as this let us know the spread through of variation in the data set.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Variance is nothing but just information that shows us how well the data is spread through. Calculating variance is required, especially in cases where we have been doing a sampling of data. This is important because we use the correct function to calculate the variance like VAR.S or VAR.P. We have a few cases to calculate variance in excel where we have some data that has been projected for the period, and we want to compare that with the actual figures.

How to Calculate Variance in Excel?

Let’s understand how to Calculate Variance in Excel with some examples.

You can download this Variance Excel Template here – Variance Excel Template

Example #1 – Calculating variance in excel for the entire population

If the data set is for the complete population, then we need to use the VAR.P function of excel. This is because, in excel, we have two functions that are designed for different datasets.

We may have data that is collected based on sampling, which might be the population of the entire world.

VAR.P uses the following formula:

VAR.P Formula

Step 1 – Enter the data set in the columns.

variance example 1-1

Step 2 – Insert the VAR.P function and choose the range of the data set. Here one thing should be noted that if any cell has an error, then that cell will be ignored.

variance example 1-2

Step 3 – After pressing the Enter key, we will get the variance.

variance example 1-3

We have calculated the variance of Set B by following the same steps given above. The result of the variance of Set B is shown below.

variance example 1-4

Example #2 – Calculating the variance for sample size in excel

If we have data set that represents samples, then we need to use the function of VAR.S instead of using the VAR.P

This is because this function has been designed to calculate the variance, keeping in mind the sampling method’s characteristics.

VAR.S uses the following formula:

VAR.S Formula

Step 1 – Enter the data set in the column.

variance example 2-1

Step 2 – Insert the VAR.S function and choose the range of the data set.

variance example 2-2

Step 3 – We will get the variance.

variance example 2-3

We have calculated the variance of Set B by following the same steps given above. The result of the variance of Set B is shown below.

variance example 2-4

Example #3 – Calculating the quantum of variance for data in excel

We may just want to calculate the variance in the data, and we may need the variance in terms of quantity and not in terms of data analysis.

If we need to check the change, then we need to use the following method.

Step 1 – Calculate the difference that is between the two of the data by using the function of subtraction.

Calculating the quantum example 3-1

Step 2 – After pressing the Enter key, we will get the result. To get the entire data variance, we have to drag the formula applied to cell C2.

Calculating the quantum example 3-2

Step 3 – Now, the variance can be positive and negative, and this will be the calculated variance.

Example #4 – Calculating the percentage of variance for the data set in excel

We may need to calculate the percentage change in the data over a period of time, and in such cases, we need to use the below method.

Step 1 – First, calculate the variance from method 3rd.

Step 2 – Now calculate the percentage by using the below function.

Change in the value/original value*100. This will be our percentage change in the data set.

variance example 4-1

Step 3 – To get the percentage of the entire data variance, we have to drag the formula applied to cell D2.

variance example 4-2

Things to Remember About Variance in Excel

  • If we have data set that represents the complete population, then we need to use the function of VAR.P.
  • If we have a data set that represents the samples from the world data, then we need to use the function of VAR.S.
  • Here S represents the samples.
  • If we are calculating the change in terms of quantum, then a negative change means an increase in actual value and a positive change means a decrease in value.
  • In the case of using the VAR.P, the arguments can be number or name, arrays or reference that contains numbers.
  • If any of the cells that have been given as a reference in the formula contains an error, then that cell will be ignored.

Recommended Articles

This is a guide to Variance in Excel. Here we discussed How to calculate Variance in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Top 25 Useful Advanced Excel Formulas and Functions
  2. FLOOR Function in Excel
  3. Excel Square Root Function
  4. Excel Variance
0 Shares
Share
Tweet
Share