9

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.

  • 3
    You forgot to say what should happen if I18 is zero. – Blackwood May 8 '18 at 16:03
  • 3
    "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
  • 3
    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
  • 3
    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
55

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.

| improve this answer | |
  • 3
    Look how complicated the other answers are! Keep it simple! – Stewart May 8 '18 at 18:36
  • 11
    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
  • 1
    @cedbeu Complicated, definitely. If only because the other answers are harder to read and parse, mentally. – Stewart May 9 '18 at 6:13
6

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

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

Let's simplify your original code:

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.

| improve this answer | |
  • 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
3

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")))
| improve this answer | |
1

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

=SQRT((I18-R18)^2)

| improve this answer | |
  • 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
0

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

| improve this answer | |
  • (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
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.