Exceljet

Quick, clean, and to the point

Convert negative numbers to zero

Excel formula: Convert negative numbers to zero
Generic formula 
=MAX(A1,0)
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.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.