IF Negative Then Zero (0) – (Formula in Excel)

Let me share an example with you. In the following table, you have a table with the two lists of the number in columns A and B. And in column C, you have a difference between both.

In Excel, a formula based on conditional logic can return a zero for any negative number in a cell or range of cells. So in this tutorial, we look at all the different ways to write a formula to convert negative numbers into zeros.

Convert Negative Numbers using IF

The first method is to use the IF function that allows you to create a condition to check if a number is negative and then convert that number into a zero.

Use the following steps.

1. Enter the IF function in cell C2.
2. Specify the subtraction formula for cell A1 from B1. Use the greater sign with the zero to create a condition.
3. For the value_if_true, specify zero. And for the value_if_false, enter the subtraction formula that you have used in the condition.
4. Enter the closing parentheses and hit enter to get the result.

By using the above steps, you have written a formula that subtracts two values and tests the result.

=IF(A2-B2<0,0,A2-B2)

The IF part of the formula checks if the result of this subtraction is less than 0. If it is, the formula will return 0. It is useful to avoid negative results appearing in your calculations. For example, if you’re calculating profits and costs, a negative result could suggest a loss, but in some contexts, you may treat that loss simply as a zero gain rather than a negative number.

If the subtraction result is not less than 0, i.e., if it’s either zero or a positive number, then the formula will return that result. This part of the formula ensures that positive differences between the values in cells A2 and B2 are accurately reflected in the result.

Change Negative Numbers into a Zero with MAX Function

Alternatively, you can use the MAX function to ensure that all negative values are shown as zero. Let’s take the same example.

=MAX(A2-B2,0)

In the above formula, you have used the max function where one argument is the subtraction formula of A2 from B2 and the second one is a zero.

Let’s Understand it this way, the max function returns the maximum values from multiple values. When the result of the subtraction is a positive number it returns that number, but if the result is a negative number, it returns zero. As a result, any negative number becomes zero, and positive numbers remain unchanged.

Show a Zero for a Negative Number

You can also use custom formatting to show a zero for a negative number. Means if you only need to display negative numbers as zero but keep the actual data unchanged, you can use the below steps to apply it.

1. Select a cell or range of cells. Press the Shortcut key Control + 1 (Command +1 if you are using Mac) to open the Format Cells dialog box.
2. Click on the Custom Option and enter the 0;”0″;0 into the input box. Click OK to apply the settings.

The first part (0) specifies how positive numbers should appear, the second part (0) specifies how negative numbers should appear, and the third part (0) specifies how zeros should appear. But it won’t change the value but the formatting. The original value will stay intact.