# How to Calculate Percentage Variance between Two Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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_number`

Now 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. ### 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)-1`

❶ 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. ### 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_number`

To calculate the absolute value of the second number, I will use the ABS function.

❶ 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. ### 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)`

❶ 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. ## 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.

## Related Articles Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  