Excel Variance (Table of Contents)
Introduction to Variance in Excel
Variance is used in cases if we have some budgets and we may know what are the variances that are observed in implementing the budgets. In some cases, the term variance is also used to calculate the difference between what has been planned and what is the actual result that has been obtained. Variance calculation is a great way for data analysis as this let us know the spread through of variation in the data set.
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. Now, this is important that we use the correct function to calculate the variance like VAR.S or VAR.P. We have 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.
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:
Step 1 – Enter the data set in the columns.
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.
4.8 (2,393 ratings)
View Course
Step 3 – After pressing the Enter key we will get the variance.
We have calculated the variance of Set B by following the same steps given above. The result of variance of Set B is shown below.
Example #2 – Calculating the variance for sample size in excel
If we have data set that represents samples than 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 characteristics of the sampling method.
VAR.S uses the following formula:
Step 1 – Enter the data set in the column.
Step 2 – Insert the VAR.S function and choose the range of the data set.
Step 3 – We will get the variance.
We have calculated the variance of Set B by following the same steps given above. Result of variance of Set B is shown below.
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 the 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.
Step 2 – After pressing the Enter key, we will get the result. To get the variance of entire data, we have to drag the formula applied to cell C2.
Step 3 – Now the variance can be positive as well as negative also 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.
Step 3 – To get the percentage of variance of entire data, we have to drag the formula applied to cell D2.
Things to Remember About Variance in Excel
- If we have data set that represents the complete population than we need to use the function of VAR.P
- If we have 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 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 than 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 downloadable excel template. You can also go through our other suggested articles –
