Go Back

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to replace negative numbers to zero in the same cell where the information is currently located.
Solved by B. F. in 24 mins
Using Excel 2007... is it possible for a cell showing the sum of two numbers to show a zero when the sum is a negative number?
Solved by T. D. in 29 mins
If the values in column I are negative I want them to be zero.
Solved by F. A. in 13 mins

Leave a Comment

avatar