Converting A Negative Value Cell to Zero Automatically

Put this formula in D2:

=IF(B2>C2,B2*0.125,0)

Thanks a lot Andrew....this is exactly what i was looking for.

Can you help me again on this one.

Now the alignment is different but i want the exact same thing.

I am attaching a new excel sheet with the new alignment. Can you do the same magic on this one too.

I want all of the profits added and minus all the loss and loss from previous year. Then if the total come positive, 12.5% on that or else if negative then the cell should display zero (0).

Thanks a lot again for the help.

Yes I can, but only if the format is not going to change (i.e. two rows of profit, two rows of loss and one row of previous year loss). If you try to insert or delete rows then a simple formula wouldn't work.

If you really do want just that format then the formula would be:

=IF(SUM(C2:C3)-SUM(C4:C6)>0,(SUM(C2:C3)-SUM(C4:C6))*0.125,0)

That works out 12.5% of the difference between profit and loss (if it's positive), while the previous formula works out 12.5% of the profit, by the way - I wasn't sure which one you wanted.

The easiest way to do it would be to represent the profits as positive numbers and the loses as negative, then you could use the easier formula:

=IF(SUM(C2:C6)>0,SUM(C2:C6)*0.125,0)

Alternatively, if you just labelled column B "Profit" or "Loss" you could use a SUMIF formula:

=IF(SUMIF(B2:B6,"=Profit",C2:C6)-SUMIF(B2:B6,"=Loss",C2:C6)>0,(SUMIF(B2:B6,"=Profit",C2:C6)-SUMIF(B2:B6,"=Loss",C2:C6))*0.125,0)

Originally Posted by royUK

Don't report posts because they are solved

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Thank you Roy for the heads up. Will remember and do this next time. Sorry for the trouble.