Subtracting positive amts from negative and positive from positive
 

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?

Subtracting positive amts from negative and positive from positive
 

bwbmom wrote:
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.

Subtracting positive amts from negative and positive from positive
 

Hi,

=if(B9<0,+B9+B31,if(b90,+B31-B9))

"bwbmom" wrote:

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?

Subtracting positive amts from negative and positive from posi
 

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

"Glenn" wrote:

bwbmom wrote:
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.
.