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.

7

7 Answers

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.

7

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

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

1

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.

1

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)

1

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

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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