Excel formula to find difference between two numbers

Get FREE Advanced Excel Exercises with Solutions!

Want to know how to find the difference between two numbers (positive or negative) in Excel? Or how to calculate the percentage difference for negative numbers in Excel using formula?

You’re in the right place.

Let’s continue…

Subtraction definition and nomenclature

subtraction definition and nomenclature

The subtraction operation has the following participants:

  • Minuend: A quantity or number from which another is to be subtracted. In the above example, 9 is the minuend.
  • Minus Sign (-): Then we use a minus symbol (-) to find the difference between two numbers.
  • Subtrahend: Subtrahend is the quantity or number to be subtracted from minuend.
  • Equal Sign (=): Then we place an equal sign (=).
  • Difference: Difference is the result of the subtract operation.

How to Find Difference Between Two Numbers Using Excel Formula

In MS Excel, you will not find any SUBTRACT function to perform the subtraction operation. Instead, we use the regular minus (-) symbol to perform subtractions.

1) Using numbers directly in the formula

In this way, we input the numbers directly in the formula. You know, every formula starts with an equal sign in Excel

So, input an equal sign (=) to start an Excel formula > Input the minuend value > Input the minus sign (-) > Place the subtrahend value > Press Enter

Example: =50-5

subtraction in excel with fixed numbers

Note: If the subtrahend value is negative, use the parentheses to place the number in the subtraction formula like this: =-91-(-23)

Why is this method not suggested?

  • If you have more than one subtraction, you have to write a formula for every subtraction individually
  • You cannot copy the same formula for another set of numbers
  • Time-consuming as you have to write a formula for every set of numbers individually

2) Using cell references instead of numbers in the formula

This is more effective and time-saving. We can make a formula for a single set of numbers and then use the formula for other cells.

So, we shall use this method and this is why Excel is so useful.

  • In the cell C2, input this formula: =A2-B2

excel difference between two numbers positive or negative

  • Press Enter and this is the result we get.

Excel formula enter

Excel formula applied to other cells

3) Calculate the absolute difference between two numbers in Excel (Using ABS() function)

To get the absolute difference between two numbers in Excel, we shall use the ABS() Excel function.

ABS() function returns the absolute value of a number, a number without its sign.

Syntax of ABS Function: ABS(number)

We have used the ABS() Function in the same data. You see that the previous negative differences are now showing as positive numbers (without any sign).

Use of Excel ABS Function

Read More: How to Subtract Two Columns in Excel

The percentage difference between the two numbers in Excel

Percent Difference and Percent Error are actually the same things. In Percent Difference, you will compare two experimental values.

This is the equation to calculate the Percent Difference.

percent difference formula

Here, E1 = First experimental value and E2 = Second experimental value

 Note: Percentage Change ((New value/Old value) -1) and Percent Difference are two different phenomena in Physics.

In Excel, we can easily calculate the Percent Difference:

Calculate Percent Difference in Excel

We have used this formula in the cell C2: =2*ABS(A2-B2)/(A2+B2)

How does this formula work?

  • The difference between numbers A2 and B2 (A2-B2) can be negative. So, we have used the ABS() function (ABS(A2-B2)) to make the number absolute.
  • Then we multiplied the absolute value by 2 and then divided the value by (A2+B2)

Read More: How to Create a Subtraction Formula in Excel


Calculate Percentage Change for negative numbers in Excel

Theoretically and even practically, you cannot find the percentage change for negative numbers. When it is not possible theoretically, how can we calculate them in Excel?

Not possible. Whatever methods you use, you will find misleading results.

Here I will show 3 methods to calculate the percentage change of negative numbers in Excel but all of them will mislead you. So, be aware of it before using them in your work.

1) Calculate the % change of negative numbers by making the denominator absolute

Say, you want to calculate the percentage of two values:

Old value: -400

New value: 200

We know, % change = ((New value – Old value )/ Old value) x 100%

If we apply this formula to calculate the % change of the two above values (-400 & 200), this will be the result:

= ((200 – (-400))/-400)*100%

= 600/(-400)*100%

= -150%

This is a completely wrong answer.

As in this way, you don’t get the correct answer, so some companies use the ABS method. In this method, the denominator is made absolute.

In the following image, I have shown the ABS denominator method.

percent change of negative numbers using ABS function

These results are also misleading because you see -100 to 200 shows the biggest % change when -500 to 200 shows the smallest % change.

Take a look at the following image:

percent change of negative numbers using Shifting Method

These results are also misleading.

You see, though company E has seen the sharpest decline in income (from 600 to -100), the % change is showing that it has declined the lowest (-117%).

2) Shift the numbers to make them positive

This is a tricky method but the result is not acceptable.

Say, we have two values:

Old value: -50

New value: 20

We will shift these two numbers using their absolute plus and then multiplying it by 2: (|-50|+ |20|)*2 = 140

shifted_old value = -50 + 140 = 90

shifted_new value = 20 + 140 = 160

Now, we shall calculate their % change: ((160-90)/90)*100% = 77.78%

Let’s see whether this method gives us satisfactory results:

percent change of negative numbers

Just check out the last two rows: % change from 1 to 100 and 1 to 1000 are almost same.

But they should be: ((100-1)/1)*100% = 99% and ((1000-1)/1)*100% = 999%

Whatever methods you use, you will get no correct results.

So, what will be the solution?

Solution: Show N/A (or anything) when the % change of negative numbers appears

We shall use this formula:

<code>IF(MIN(old_value, new_value)<=0, “N/A”, (new_value-old_value)/old_value)

Check out the image below. When any of the two values is negative, the formula returns the “N/A” value.

Use of IF and MIN Functions in Excel

Download Excel File

It will be easy for you to follow me if you download the file that I have used to write this article.

Conclusion

Calculating the difference between two positive or negative numbers in Excel is actually very simple. The complexity comes when you try to find the percentage change between positive and negative or two negative numbers. I hope this article gives you a good guide on the topics. If you have any confusion, let me know in the comment box.


Related Articles


<< Go Back to Subtract in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

10 Comments
  1. But what if it’s time what you’re calculating??

    • Dear DON_1234567,
      You can use the simple subtraction formula to calculate the difference between two times.
      You can use the formula below-
      =C5-D5
      Check out the screenshot below-

      If you are still not getting the result. Then change the cell format to time.
      You can visit the below article to learn more. Thanks!
      Calculate Difference Between Two Times

  2. Thanks, Kawser! You ABSolutely save me! Is there a list showing common formulas somewhere? I takes so long to come up with them on my own.

    • Reply
      Kolapalli Pavan Kumar Feb 17, 2021 at 12:29 PM

      Can you please help me with counting the number of the total from opening and closing?
      For example
      My opening number is 6
      My closing number is 12
      Then the total number should be 7(including opening number) rather than 6.

  3. Reply
    Kolapalli Pavan Kumar Feb 17, 2021 at 12:28 PM

    Can you please help me with counting the number of the total from opening and closing?
    For example
    My opening number is 6
    My closing number is 12
    Then the total number should be 7(including opening number) rather than 6.

    • Dear KOLAPALLI PAVAN KUMAR,
      The output you are looking for can be achieved using the COUNT function in excel. Here the COUNT function counts the cell number given in the string.
      Apply the following formula-
      =COUNT(C2:C8)

      You can check the screenshot below-
      First, select a cell (E3) to apply the formula.

      Second, press Enter to get the desired output.

      Hope you got your solution. If you still didn’t get it, check out the article below-
      The Different Ways of Counting
      Thanks!

  4. Solution:

    =ABS(ABS(Q34-R34)/(Q34+R34))*(IF(Q34>R34;-1;1))

  5. =ABS(ABS(Q34-R34)/(Q34+R34))*(IF(Q34>R34;-1;1))

  6. hi
    I need one formula for Excel

    there are three numbers for the same digit another number and two numbers same

    output result required
    how many numbers are more than two

    example
    2210001
    2210001
    2210002
    2210002
    2210003
    2210003
    2210003
    2210004
    2210006
    2210006
    2210006
    2210006
    2210007
    2210007
    2210009
    2210009
    2210009

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 10:57 AM

      Hi ISRAVEL,

      To achieve this in Excel, you can use the COUNTIF function to count the occurrences of each number and then check which numbers occur more than twice. Here’s how you can set up your Excel sheet:

      Assuming your numbers are in column A starting from A2, you can use the following formula in another cell to get the count of numbers that appear more than twice: =COUNTIF(A:A, A2)

      Drag down this formula for each number in your list. This will give you the count of each number.

      Then, you can use another column to check if the count is greater than 2: =IF(B2>2, "More than two", "Not more than two")

      Drag this formula down for each number in your list.

      This setup will give you a clear indication of which numbers occur more than twice in your list.

      I hope this comment will help you get your required output. Please let us know in the comment section if you have any other queries.

      Regards

      Maruf Hasan

      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo