Convert negative numbers to zero

=MAX(A1,0)
To convert negative numbers to zero without affecting positive numbers, you can use a formula based on the MAX function. In the example shown, the formula in D5, copied down, is:
=MAX(B5,0)
If the number in column B is negative, MAX returns zero. Otherwise, MAX returns the original number.
In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero.
With the MAX function
The MAX function provides an elegant solution:
=MAX(B5,0)
This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero. However, if the number is B5 is negative, MAX will return zero, since 0 is greater than any negative number.
With the IF function
This problem can also be solved with the IF function like this:
=IF(B5<0,0,B5)
This formula returns exactly the same result. If B5 is less than zero, IF returns zero. Otherwise, IF returns the original number. Both formulas work fine. The MAX formula above is just an elegant way to replace the slightly more complex IF formula. However, if you need to customize the results returned after a logical test, the IF function is more flexible.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.