Formula to avoid going negative when one value minus another

[Woofy_McWoof_Woof]

Hi, I'm trying to calculate a formula that if the result is less than zero then it returns the delta value but is capped at zero instead of the negative result and to then show the residual amount in the cell next to it.

For instance cell A2=+20, cell B2=-70, cell C2 should be A1+B1 which should in theory =-50 but I want it to show -20 and then show -50 in cell D2

First result	Change	Positive only	Negative only
20	-70	-20	-50

 

[etaf]

in negative only cell

=If(A1+b1<0, a1+b1, "")
in positive only

why is the result -20 ?
is that because its 0-20

if so
=If(A1+b1<0, first result cell * -1 , "")

Book2
	A	B	C	D
1	First result	Change	Positive only	Negative only
2	20	-70	-20	-50
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(A2+B2<0, A2* -1, "")
D2	D2	=IF(A2+B2<0, A2+B2, "")

but i may have it wrong - so more examples would help

 

[Woofy_McWoof_Woof]

in negative only cell

=If(A1+b1<0, a1+b1, "")
in positive only

why is the result -20 ?
is that because its 0-20

if so
=If(A1+b1<0, first result cell * -1 , "")

Book2
	A	B	C	D
1	First result	Change	Positive only	Negative only
2	20	-70	-20	-50
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(A2+B2<0, A2* -1, "")
D2	D2	=IF(A2+B2<0, A2+B2, "")

but i may have it wrong - so more examples would help

Hi, the 'Positive Only' result is due to the original +20 minus the 70 but capped at zero so in effect it shows there is a reversal of -20 to get to the zero.

 

[Woofy_McWoof_Woof]

in negative only cell

=If(A1+b1<0, a1+b1, "")
in positive only

why is the result -20 ?
is that because its 0-20

if so
=If(A1+b1<0, first result cell * -1 , "")

Book2
	A	B	C	D
1	First result	Change	Positive only	Negative only
2	20	-70	-20	-50
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(A2+B2<0, A2* -1, "")
D2	D2	=IF(A2+B2<0, A2+B2, "")

but i may have it wrong - so more examples would help

That's spot on, thanks. I'll try it in my master spreadsheet, drag it down and test it out. Thanks a lot

 

[etaf]

you are welcome