MrExcel Publishing
Your One Stop for Excel Tips & Solutions

negative numbers to show as zero


Posted by Lori on October 03, 2001 11:49 AM

I am sure this should be simple. If I am subtracting two numbers and would like them to show the end result if positive but zero if they tally to negative. This is built within a number of if then statements, suggesting that if I add an additional if statement then I will have too many arguments in the formula. I was hoping there is a different approach that someone could show me.

Please & Thanks


Posted by IML on October 03, 2001 11:59 AM

If you are subtracting A2 from A1, you could use
=MAX(A1-A2,0)

good luck

Posted by Ian Mac on October 03, 2001 12:00 PM


What formula are you using? con you give an example, you might be able to re-work the whole thing as a lookup??

Ian Mac

Posted by Mark W. on October 03, 2001 12:25 PM

What do you mean by...

..."show the end result"? The custom format...

General;"0"

can be used if you're only concerned with
the display of the results.

Posted by Lori on October 03, 2001 12:47 PM

The MAX idea worked great.

Thanks