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.
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.
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.
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 the 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, 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:
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. The result of the 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 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 entire data variance, we have to drag the formula applied to cell C2.
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.
Step 3 – To get the percentage of the entire data variance, 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, 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 –
