# Formula for delta between two numbers

I am trying to find the delta between two sets of numbers. Some numbers are positive, some negative.

Using the following formula works about 99% of the time:

``````=IF(I18<0,I18+R18,IF(I18>0,I18-R18))
``````

However, when `I18` and `R18` are negative numbers, I need `I18-R18`. I have tried multiple `IF` `AND` statements, but just can't seem to get this right. Hopefully someone can guide me in the right direction.

• You forgot to say what should happen if `I18` is zero. – Blackwood May 8 '18 at 16:03
• "when `I18` and `R18` are negative numbers, I need `I18-R18`" Are you sure you mean this? I thought you wanted the delta as an absolute difference. If `I18` is `-2` and `R18` is `-1`, your result is going to be `-1`. Don't you want a positive number in all cases? – JoL May 8 '18 at 23:27
• Does `=ABS(I18)-ABS(R18)` produce the desired output? – Salman A May 9 '18 at 5:28
• Your question is really unclear. I could only understand what you were after when I saw the answer (and I'm still not sure that's what you want). Also, a delta between A and B can only be `A+B` if both A and B are zero. – Dmitry Grigoryev May 9 '18 at 9:14
• The delta usually means the difference between two numbers, which is always A-B, and unless you want to signify which of the numbers is the greater, you would take the absolute value abs(A-B). – Mick May 9 '18 at 12:54

It sounds like you just want the difference (delta) between `I18` and `R18`, and you want it to always be positive?

This formula will do that:

`=ABS(I18-R18)`

It will give the right answer whether `I18` is positive, negative or zero.

• Look how complicated the other answers are! Keep it simple! – Stewart May 8 '18 at 18:36
• To be fair, the question asked how to make the nested `IF()` work correctly. The other answers addressed the question that was asked. But sometimes, taking a different path will get you to the right place faster. – Steve Kennedy May 8 '18 at 19:13
• @Stewart To be more fair, it's unclear if OP wants the absolute difference. ToddCurry's and PeterH's answers look to fulfill a condition the OP set that does not lead to the absolute difference. – JoL May 8 '18 at 23:33
• @Stewart, most other answers are basically the same than this one, except that they develop the function ABS() in more basic conditional blocks. But nothing complicated, really. – cedbeu May 9 '18 at 5:41
• @cedbeu Complicated, definitely. If only because the other answers are harder to read and parse, mentally. – Stewart May 9 '18 at 6:13

Instead of checking the operands for negativity, check the result.

``````=IF(I18-R18>0,I18-R18,R18-I18)
``````
• the condition could also be replaced by `I18>R18` – Liora Haydont May 9 '18 at 13:16

``````If X < 0:
Z = X + Y

Else X > 0:
Z = X - Y
``````

Now you say that when X < 0 and Y < 0, you actually want the result to be X - Y and not X + Y. OK.

``````If Y < 0:
If X < 0:
Z = X - Y
Else:
Z = X + Y
Else:
If X < 0:
Z = X - Y
Else:
Z = X + Y
``````

That is written.

``````=IF(R18<0,IF(I18<0,I18-R18,I18+R18),IF(I18<0,I18-R18,I18+R18))
``````

As noted above, you don't have a zero case. You may be able to change one of the LT/GT comparators to LE/GE by simply adding an equal sign -- depending on your data and logic.

• Unless I'm misreading something, in the larger pseudocode you have `If Y<0 {A} Else {B}` but A and B are identical. Is there a typo? – Kamil Drakari May 9 '18 at 14:41

You can use the below:

``````=IF(AND(I18<0,R18<0),I18-R18,IF(I18<0,I18+R18,IF(I18>0,I18-R18)))
``````

The only flaw i can see with this is, what do you do in an instance where `I18 = 0`, you have nothing set for this.

Anyway if you wish to add something for that instance, see below:

``````=IF(AND(I18<0,R18<0),I18-R18,IF(I18<0,I18+R18,IF(I18>0,I18-R18,"IF I18 = zero goes here")))
``````

I didn't know about ABS. I was going to suggest

=SQRT((I18-R18)^2)

• Considering that the calculation of square roots its non-trivial and prone to rounding errors, that's less than optimal. – David Foerster May 9 '18 at 13:51

This worked for me -> IF(AND(I18<0,R18<0),I18-R18,IF(I18<0,((R18-I18)*-1),IF(I18>=0,I18-R18)))

This covers all the scenarios: Variable A | Variable B +ve | +ve +ve | -ve -ve | +ve -ve | -ve

• (1) The table of the four (2²) possible combinations of two binary (`+` / `-`) variables is trivial, and showing it does nothing to clarify your answer.  If you’re going to post an answer to a question as unclear as this one, it’s useful to explain what question you think you’re answering.  (2) Whatever it is that you’re doing, your formula seems unnecessarily complex.  Explaining what it is doing would be helpful. … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your answer to make it clearer and more complete. – Scott Feb 22 '19 at 3:00

I just ran into a situation (measuring color L.a.b. readings) where I need not simply the difference between x & y but the positive or negative delta movement. So normally -5 minus -2 = -3 but wanting to show that the delta was actually in the positive direction -3 is not what I am looking for so:

=IF(A1>B1, ABS(A1-B1)*-1,ABS(A1-B1))

Simply put if the 2nd (newer) reading is smaller I know the result is negative movement (so *-1) and has moved further down the negative side of the axis. Else if the 2nd reading is larger, the pure ABS value works knowing the difference is in the positive direction.