i need A FORMLUA that will find the difference from two colums that have negitive and pos

[njknjk]

i need to find the difference between two columns that have positive and negative numbers that are mixed and the end result should be the actual value eg

0.00 -5 -5
-31.93 -20 -11.93
60.41 -10 50.41
- 74.05 -74 -0.05
- 104.03 20 -84
120.91 -30 90.91
-122.08 120 -2.08


everything I try fails to work out the difference and keep the negative and positive value
=MAX(J10C10,I10)-MIN(C10,I10) this is the closest I have found so far

[MrShorty]

I see some inconsistency (or I am otherwise misunderstanding) what you are trying to do.

0-(-5)=+5, so why is this one -5 and not +5? -5-0=-5 so should this be b-a?
-31.93 -20 -11.93 -31.93-(-20) does equal -11.93, so this one makes sense as a-b.
60.41 -10 50.41 60.41-(-10) should equal 70.41, so why is this one 50.41? -10-60.41 would be -70.41, so even b-a does not make sense.
-74.05-(-74)=-0.05 so this is another one that makes sense as a-b.
-104.03-20=-124.03, why is this one -84? 20-104.03 would be -84.03, so this one would make sense as b-a.
120.91-(-30)=150.91, so why is this one 90.91? -30-120.91 would be -150.91, so b-a does not make sense here either.
-122.08-120=-242.08. 120-(-122.08)=242.08, so neither a-b nor b-a work.

the closest thing I see to working is abs(a)-abs(b) where the final result takes the sign of the value farthest from 0. I cannot see exactly how it would all come together (I don't like to nest that many functions into a single cell function), but I would see some combination of abs(), sign(), max(), probably index() and match(). Over several cells, I would do:

1) Helper columns with the absolute values (=ABS(c10), =ABS(I10)).
2) helper columns with the sign() function =sign(index(columns with current values,match(max(columns with abs() functions,columns with abs functions,0))
3) final result as =result of sign() function*(cell with one abs() function-cell with other abs() function).

Is that an accurate description of what you are trying to do?

[Pete_UK]

Given the inconsistencies that MrShorty has pointed out, it is difficult to know exactly what values you should be expecting, but you can put this in C1:

=(ABS(A1)-ABS(B1))*SIGN(IF(A1=0,1,A1))

which gives you all the results except for the 4th example.

Hope this helps.

Pete

[njknjk]

=(ABS(A1)-ABS(B1))*SIGN(IF(A1=0,1,A1))
this works except when the numbers were A1 -77 and A2 -76 answer should be -1

[MrShorty]

Assuming my logic is correct, all but the first example should be correctly calculated by something as simple as =sign(a1)*(abs(a1)-abs(a2)), this is because, other than example 1, the value farthest from 0 is always in column A. If I am understanding this correctly, this would also calculate the first one correctly if it was entered as -5 0.00, instead of having the 0 in column A1.

Can you confirm the logic/math I have suggested? I would hate to spend a lot of time chasing down the wrong rabbit hole.

If I am correct, can you constrain the data entry so that A1 is always the farthest from 0 and the value that determines the sign of the final result?