Convert negative numbers to zero

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.