Set Negative values to Zero

When working in Excel, it may be required to set negative values to zero. For example, if you are trying to determine how much positive income you generated over a particular period of time, you would want to avoid negative figures.

This can be easily achieved using the MAX Function.

Let’s review the example below.

Negative values are shown.
Negative values are shown.

Looking at the below equation, we use the MAX Function and set number1 to Zero. What this does is negates any value that is no Zero or greater. Additionally, it returns a result of Zero.

Syntax:

=MAX (number1,[number2]), ...)
=MAX(0,A2-B2)

Ok, let’s set the negative values to zero!

Only positive values are returned.
Only positive values are returned.

In the sales example below, it shows how this formula can be used to capture the postive sales only between two quarters of data.

Positive Sales Only
Total Positive Sales

Leave a Reply

Scroll to Top