Summary

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.

Generic formula

=MAX(A1,0)

Explanation 

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.