If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Subtracting positive amts from negative and positive from positive
Hi,
=if(B90,+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? |
#4
|
|||
|
|||
Subtracting positive amts from negative and positive from posi
Worked perfectly! Thanks. Now I just wish I understood the formula
"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. . |
Thread Tools | |
Display Modes | |
|
|