Table of Contents
To convert negative numbers to zero in Google Sheets, you can use the IF function. The IF function takes three arguments: the condition to test (in this case, if the cell is negative), the value to return if the condition is met (in this case, 0), and the value to return if the condition is not met (in this case, the cell’s current value). You can combine this with the ABS function to convert negative numbers to positive values.
You can use the following formulas to convert negative numbers to zero in Google Sheets:
Formula 1: Convert Existing Negative Numbers to Zero
=IF(A2<0, 0, A2)
For this particular formula, if the value in cell A2 is negative then it is converted to a zero. Otherwise, it is left alone.
Formula 2: Convert Negative Numbers to Zero in Formula
=MAX(0, B2-C2)
For this particular formula, if the result of the formula B2 – C2 is negative then the formula simply returns a zero. Otherwise, the formula returns the result of the subtraction.
The following examples shows how to use each formula in practice.
Example 1: Convert Existing Negative Numbers to Zero
Suppose we have the following dataset in Google Sheets:
We can use the following formula to convert each negative number in column A to a zero:
=IF(A2<0, 0, A2)
We can type this formula into cell B2 and simply click and drag the formula down to each remaining cell in column B:
Notice that all negative numbers have been converted to zero.
Example 2: Convert Negative Numbers to Zero in Formula
We can use the following formula to subtract the values in column C from the values in column B and return a zero if the result of the subtraction is negative:
=MAX(0, B2-C2)
We can type this formula into cell D2 and simply click and drag the formula down to each remaining cell in column D:
Notice that in each case where the formula would have produced a negative number, a zero is returned instead.
The following tutorials explain how to perform other common operations in Google Sheets: