Home ➜ Excel Formulas ➜ **IF Negative Then Zero (0)**

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.

Now here need to use a formula to change negative values into zeros. 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.

- Enter the IF function in cell C2.
- Specify the subtraction formula for cell A1 from B1.
- Use the greater sign with the zero to create a condition.
- For the value_if_true, specify zero.
- And for the value_if_false, enter the subtraction formula that you have used in the condition.
- 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 the result is a negative number, then convert that negative number into a zero, or else the normal result.

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

## Change Negative Numbers into a Zero with MAX Function

You can also use the MAX function to change a negative number into a 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.

## Show a Zero for a Negative Number

You can also use custom formatting to show a zero for a negative number. Use the below steps to apply it.

- Select 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.
- Click on the Custom Option and enter the 0;”0″;0 into the input box.
- Click OK to apply the settings.

This method won’t change the value but the formatting. The original value will stay intact.

⇠ Back to Excel Formulas List