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.

Solution 1:

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.

Solution 2:

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

=IF(I18-R18>0,I18-R18,R18-I18)

Solution 3:

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.

Solution 4:

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")))

Solution 5:

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

=SQRT((I18-R18)^2)