Excel formula to find the difference between two numbers (2023)

I would like to know how to find themDifference between two numbers (positive or negative)in Excel? Or how to calculate percentage difference for negative numbers in Excel using a formula?

You're in the right place.

Let's continue...

Subtract definition and nomenclature

The subtraction operation has the following participants:

• reduce:Quantity or number from which to subtract another. In the example above, 9 is to dieHe had.
• Minus sign (-):We then use a minus sign (-) to find the difference between two numbers.
• Subtract:The subtrahend is the quantity or number to be subtracted.He had.
• Equal sign (=):Then we put an equal sign (=).
• Difference:The difference is the result of the subtraction operation.

Download Excel file

You will find it easy to follow me if you download the file I used to write this article.

How to find the difference between two numbers in Excel using formula

You won't find any in MS ExcelSUBTRACT functionto perform the subtraction operation. Instead, we use the regularnot less (-)Subtraction icon.

1) Use numbers directly in the formula

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

Then type an equal sign (=) to start an Excel formula > type theHe hadValue > Enter theAny less-(-) > Put thevalor do subtraendo> pressureTo enter

Example: =50-5

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

Why is this method not suggested?

• If you have more than one subtraction, you must write a formula for each subtraction individually.
• You cannot copy the same formula to a different set of numbers.
• It is very time consuming because you have to write a formula for each group of numbers individually.

2) Use cell references instead of numbers in the formula

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

So we'll use this method and that's why Excel is so useful.

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

• PressTo enterand this is the result we get.

• Now copy this formula to other cells in the column. You mayApply the same formula to multiple cells in Excelin more ways than one. We get the same result, but we just write an Excel formula and apply it to other cells.

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

to achieve thisabsolute difference between two numbersIn Excel we use theABDOMEN()Excel function.

ABDOMEN()The function returns the absolute value of a number, an unsigned number.

ABS function syntax:ABS (number)

we use themABDOMEN()function on the same data. You will see that the previous negative differences are now displayed as positive (unsigned) numbers.

The percentage difference between two numbers in Excel

percentage differencejerror ratethey are actually the same things. At thepercentage difference, compares two experimental values.

This is the equation to calculatepercentage difference.

On here,mi₁= First experimental value andmi₂= Second experimental value

In Excel, we can easily calculate the percentage difference:

We use this formula in the cellC2: =2*ABS(A2-B2)/(A2+B2)

How does this formula work?

• the difference between the numbersA2jB2(A2-B2) can be negative. so we use thisabs() function(ABS (A2-B2)) to make the number absolute.
• Then we multiply the absolute value by 2 and then divide the value by(A2+B2)

Keep reading: PivotTable: Percent Difference Between Two Columns

similar readings

• How to calculate time difference in Excel VBA (2 methods)
• Calculate the significant difference between two means in Excel
• Excel pivot table: difference between two columns (3 cases)
• Calculate difference between two dates in days in Excel
• How to calculate time difference in numbers (5 easy ways)

Calculate Percent Change for Negative Numbers in Excel

In theory, and even in practice, you can't find the percentage change for negative numbers. If not theoretically possible, how can we calculate them in Excel?

Impossible. Regardless of the methods used, you will find misleading results.

Here I show you 3 methods to calculate percentage change of negative numbers in Excel, but all of them will confuse you.Please consider this before using them in your work.

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

Suppose 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 percentage change in the two previous values ​​(-400 and 200), we get the following:

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

= 600/(-400)*100%

= -150 %

This is the completely wrong answer.

As the correct answer is not obtained in this way, some companies use theABS method. With this method, the denominator becomes absolute.

In the figure below, I have shown the ABS denominator method.

These results are also misleading, as you can see that -100 to 200 shows the biggest percentage change, while -500 to 200 shows the smallest percentage change.

Note the following image:

These results are also misleading.

You can see, although Company E saw the biggest drop in sales (from 600 to -100), the percentage change shows that it dropped the most (-117%).

2) Change the numbers to make them positive

This is a tricky method, but the result is unacceptable.

Let's say we have two values:

Old value: -50

New value: 20

Let's shift these two numbers by taking their absolute sum and then multiplying them by 2:(|-50|+ |20|)*2=140

changed_oldValor = -50 + 140 = 90

changed_newValor = 20 + 140 = 160

Now let's calculate your percentage change: ((160-90)/90)*100% = 77.78%

Let's see if this method gives satisfactory results:

Just look at the last two lines: the percentage change from 1 to 100 and from 1 to 1000 is almost the same.

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

No matter what methods you use, you will not get correct results.

So what will be the solution?

Fix: Show N/A (or whatever) when showing percentage change of negative numbers

We will use this formula:

<Code>IF(MIN(old_value, new_value)<=0, "N/A", (new_value-old_value)/old_value)

See image below. If either value is negative, the formula returns N/A.

Conclusion

Calculating the difference between two positive or negative numbers in Excel is really quite simple. Complexity arises when trying to find the percentage change between positive and negative numbers, or two negative numbers. I hope this article gives you a good guide on the issues. If you have any confusion let me know in the comments box.

related posts

• How to find significant difference between two numbers in Excel
• Difference between two dates in months in Excel (4 matching examples)
• How to Calculate the Difference Between Two Times in Excel (8 Methods)
• Calculate Time Difference in Minutes in Excel (3 Easy Methods)
• How to Calculate the Time Difference in Excel Between Two Dates (7 Ways)
• Calculate difference between two rows in pivot table (with easy steps)
• How to calculate the time difference between two dates in minutes in Excel