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.


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.

Dataset to Calculate Percentage Variance between Two Numbers in Excel


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.

Using Generic Formula to Calculate Percentage Variance between Two Numbers in Excel

❸ 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.

Using Generic Formula to Calculate Percentage Variance between Two Numbers in Excel

❺ 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)-1

Now 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.

Using Alternative Formula to Calculate Percentage Variance between Two Numbers in Excel

❸ 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.

Using Alternative Formula to Calculate Percentage Variance between Two Numbers in Excel

Read More: How to Calculate Budget Variance in Excel (with Quick Steps)


Similar Readings


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.

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.

Calculate Percentage Variance between Two Negative Numbers in Excel

❸ 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.

Calculate Percentage Variance between Two Negative Numbers in Excel

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.

Using IFERROR Function to Tackle #DIV/0 Error While Calculating Percentage Variance between Two Numbers

❸ 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.

Using IFERROR Function to Tackle #DIV/0 Error While Calculating Percentage Variance between Two Numbers in Excel

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.


Related Articles

Mrinmoy Roy
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo