Mrinmoy Roy
You can calculate the percentage variance between two numbers in multiple ways. The numbers can be either positive or negative. While calculating the percentage variance, the formula can return the #DIV/0 error. Because the formula uses the basic division method. Thus, in this article, I will show you how to calculate the percentage variance between two numbers in Excel both for positive and negative numbers. I will also show to handle the #DIV/0 error while calculating the percentage variance between two numbers in Excel.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Ways to Calculate Percentage Variance between Two Numbers in Excel
I will use the following dataset to show you to calculate the percentage variance between two numbers in Excel. Here, I will calculate the percentage variance between the numbers in the Estimated Revenue column and the Actual Revenue column. I will store the percentage variance in the Variance column. So, without having any further discussion let’s get started.
1. Calculate Percentage Variance between Two Numbers Using Generic Formula
First I will use the generic formula for calculating the percentage variance between two numbers.
The generic formula is,
=(first_number - second_number) / second_numberNow follow the steps below to calculate the percentage difference between two numbers in Excel.
❶ First select the entire Variance column.
❷ Then go to Home ➤ Number ➤ Percentage.
This will change the cell format from General to Percentage.
❸ Now insert the following formula in cell E5.
=(D5-C5)/C5❹ After that press ENTER.
Formula Breakdown
- Here, D5 represents the first_number.
- C5 represents the second_number.
❺ Now drag the Fill Handle from cell E5 to E12.
You will see the percentage variance between Estimated Revenue and Actual Revenue in the Variance column.
Read More: How to Calculate Variance Percentage in Excel (3 Easy Methods)
2. An Alternative Formula to Calculate Percentage Variance
Here, I will show a derivative formula of the first formula. You can use this formula instead of using the previous formula. They both will return the same result.
So, the formula is:
=(first_number/second_number)-1Now follow the steps:
❶ Insert the following formula in cell E5.
=(D5/C5)-1❷ After that, press ENTER.
Formula Breakdown
- Here, D5 represents the first_number.
- C5 represents the second_number.
❸ Now drag the Fill Handle from cell E5 to E12.
You will see the percentage variance between Estimated Revenue and Actual Revenue in the Variance column.
Read More: How to Calculate Budget Variance in Excel (with Quick Steps)
Similar Readings
- How to Calculate Coefficient of Variance in Excel (3 Methods)
- Calculate Mean Variance and Standard Deviation in Excel
- How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
3. Compute Percentage Variance between Two Negative Numbers
You can use the formula I showed in the first method to calculate the percentage variance between two negative numbers. But instead of dividing the difference between the two numbers with the second number, you have to use the absolute value of the second number.
So the formula becomes,
=(first_number - second_number) / absolute value of second_numberTo calculate the absolute value of the second number, I will use the ABS function.
Now follow the steps:
❶ Insert the following formula in cell E5.
=(D5-C5)/ABS(C5)❷ After that, press ENTER.
Formula Breakdown
- Here, D5 represents the first_number.
- C5 represents the second_number.
- ABS(C5) calculates the absolute value of the second_number.
❸ Now drag the Fill Handle from cell E5 to E12.
You will see the percentage variance between Estimated Revenue and Actual Revenue in the Variance column.
Read More: How to Do Variance Analysis in Excel (With Quick Steps)
4. Tackling #DIV/0 Error While Calculating Percentage Variance
We have to use the division process to calculate the percentage difference between two numbers. So, if one of the numbers is 0, Excel will show a #DIV/0 error.
To tackle this error, I will use the IFERROR function.
So, the syntax of the formula becomes,
=IFERROR((first_number/second_number)-1,0)Now follow the steps:
❶ Insert the following formula in cell E5.
=IFERROR((D5/C5)-1,0)❷ After that, press ENTER.
Formula Breakdown
- Here, D5 represents the first_number.
- C5 represents the second_number.
- The IFERROR function returns a 0 if the formula returns any #DIV/0.
❸ Now drag the Fill Handle from cell E5 to E12.
You will see the percentage variance between Estimated Revenue and Actual Revenue in the Variance column.
Read More: How to Calculate Variance in Excel (Easy Guide)
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.
Conclusion
To sum up, we have discussed 4 ways to calculate percentage variance between two numbers in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
