Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why Subtracting Two Negative Numbers Results in Positive Number | Excel Worksheet Functions | |||
Some Positive, Some Negative | Excel Worksheet Functions | |||
IF positive/If negative???? | Excel Worksheet Functions | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
Convert positive # to negative | Excel Discussion (Misc queries) |