IF Negative Then Zero (0)

HomeExcel FormulasIF 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.

table-with-negative-values

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.

📂 sample-file

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.

convert-negative-number

Use the following steps.

  1. Enter the IF function in cell C2.
  2. Specify the subtraction formula for cell A1 from B1.
  3. Use the greater sign with the zero to create a condition.
  4. For the value_if_true, specify zero.
  5. And for the value_if_false, enter the subtraction formula that you have used in the condition.
  6. 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.

change-negative-numbers-to-zero

=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.

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

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

⇠ Back to Excel Formulas List