Subtracting positive amts from negative and positive from positive

[bwbmom]

I have a problem with a spreadsheet that SOMETIMES would result in
subtracting positive amounts from negative where I would want the result to
show negative but only the difference between the negative and the positive.
In other words, if B9 is negative and B31 is positive then I want the
negative in B9 to be
reduced by the positive in B31 and reflect a negative amount (ie. -900 minus
+200 = -700) but sometimes the amount in B9 is positive and B31 is positive
and so then I just want B31 subtracted from B9 to reflect a positive (i.e.
+900 minus +200 = +700).
I found another answer that partially works
=IF(COUNTIF(B9:B31,"<0"),-ABS(ABS(B9)-ABS(B31))) when B9 is negative but it
doesn't work if B9 is positive. How do I get the second condition into the
formula?

 

[Glenn]

I have a problem with a spreadsheet that SOMETIMES would result in
subtracting positive amounts from negative where I would want the result to
show negative but only the difference between the negative and the positive.
In other words, if B9 is negative and B31 is positive then I want the
negative in B9 to be
reduced by the positive in B31 and reflect a negative amount (ie. -900 minus
+200 = -700) but sometimes the amount in B9 is positive and B31 is positive
and so then I just want B31 subtracted from B9 to reflect a positive (i.e.
+900 minus +200 = +700).
I found another answer that partially works
=IF(COUNTIF(B9:B31,"<0"),-ABS(ABS(B9)-ABS(B31))) when B9 is negative but it
doesn't work if B9 is positive. How do I get the second condition into the
formula?

In your other post, I responded with this:

=A1-B1*SIGN(A1)

which becomes this with your new cell references:

=B9-B31*SIGN(B9)

If this doesn't work for you, provide examples for B9 and B31 with the results
you want. Include all possible combinations of negative, positive, zero and
blank values for both cells.

 

[Eduardo]

Hi,

=if(B9<0,+B9+B31,if(b9>0,+B31-B9))

 

[bwbmom]

Worked perfectly! Thanks. Now I just wish I understood the formula