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