Force negative numbers to zero

There is a simple method in Excel that forces negative numbers to zero through the use of the MAX function. This tutorial will walk through how to do it.

Figure 1. Final result: Forcing negative numbers to zero using MAX function

Setting up the Data

Below is a table of the Before and After data, with corresponding differences.  We want to force the negative numbers in column D to zero and retain the positive numbers.

Figure 2.  Sample data for forcing negative numbers to zero

The quickest way to force negative numbers to zero is by using the MAX function in Excel.  

MAX function in Excel

The MAX function returns the largest value in a set of values.  

Syntax

=MAX(number1, number 2,...)

In cell E3, enter the formula:

=MAX(0,D3)

Figure 3.  Entering the MAX formula

• This formula compares the number zero “0” and the value in D3, then returns the larger value
• Zero is larger than any negative number; in this case, zero is larger than -2
• The MAX function returns 0 in cell E3

Copy the formula in the succeeding rows. Below table shows how the MAX function has forced the negative numbers to zero in cells E3 and E7.

Figure 4.   Final result: Forcing negative numbers to zero using MAX function

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Are you still looking for help with the IF function? View our comprehensive round-up of IF function tutorials here.