How do I set up a formula which will subtract one cell from another (ex: B2-B3) and return the usual result, UNLESS the result is negative (basically < zero), in which case I need it to return "$0.00" instead of "Error" or "Div by 0".

Currently I am using =IF(B2-B3<0,"0.00") which works fine if the values are negative, but it returns "False" if the values are positive.

How do I modify this Excel formula so it will work properly and show the actual result or $0.00 if the result is negative or < 0?


Well, first of all, you shouldn’t be using quotes.  "0.00" is a text value (sometimes called a string) that consists of four characters: 0.00.

Beyond that, you were very, very close.  You want B2-B3 if it’s positive and 0 if it’s negative?  Just do

=IF(B2-B3<0, 0, B2-B3)
or, equivalently,
=IF(B2<B3, 0, B2-B3)
A slightly different approach is
=MAX(B2-B3, 0)
which gives you B2-B3 or 0, whichever is larger.

| improve this answer | |
  • Thanks for the quick reply - that did it! I had actually tried the first one but it did not work as I did not put spaces after the comma.... Syntax is vital in Excel! I wasn't familiar with the MAX formula so I learned some new stuff - Thanks! – Byl Usa Oct 18 '18 at 0:49
  • Hmmm;  that’s odd.   Spaces between the arguments to functions don’t matter;  I just include them for readability. – Scott Oct 18 '18 at 0:54
  • Yes that is odd. I obviously did something else wrong. Good to know that spaces don't matter after the comma though.... – Byl Usa Oct 18 '18 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.