Subtracting positive amts from negative and positive from positive

  • Thread starter Thread starter bwbmom
  • Start date Start date
B

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?
 
bwbmom said:
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?
Click to expand...


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.
 
Back
Top