Excel Formula to Return Zero If Negative Value is Found

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel you will find negative values in some cells. You might want to remove those negative numeric values or visualize them converting into zeros. This is often needed for a user to make the report more lucrative. But sometimes it becomes difficult as an excel formula is implemented on those cells. This will not be a problem anymore. In this article, I will share with you how to perform if negative then zero with an excel formula.

Table of Contents hide

Excel Formula to Return Zero If Negative Value is Found: 3 Simple Methods

1. Use IF Function

2. Perform MAX Function

3. Utilize Format Cells Feature

Things to Remember

Conclusion

Excel Formula to Return Zero If Negative Value is Found: 3 Simple Methods

In the following article, I am sharing with you 3 simple and quick methods to excel formula if negative then zero.

Suppose we have a dataset of some products Item 1, Item 2 and their Differences. As you can see in the difference column there are some negative numeric values. Now we are going to make those negative to zero values using formulas and the format cells feature.

1. Use IF Function

Although in the output column the value is created using formulas, you can apply a different formula over those cells to make it into zero value. In this method, we have used the IF function to make negative then zero. The IF function in excel runs a condition over a given statement and returns an output based on the condition.

Steps:

First, choose a cell in the workbook. Here, I have selected cell (D5).
Second, apply the following formula-

=IF(B5-C5<0,0,B5-C5)

Hence, hit the Enter
Then, drag the “fill handle” down to fill all the cells.

Therefore, you will find the positive values remain the same but the negative values have converted into zeros.

2. Perform MAX Function

You can also perform the MAX function in excel to perform if negative then zero with the excel formula. Follow the steps below-

Steps:

Here let’s start with choosing a cell (D5) to write the formula.
Now put the formula down-

=MAX(B5-C5,0)

Where,

The MAX function returns the largest value within a statement.

In general, press Enter to get the output for the applied formula.
Then pull the “fill handle” down to fill the cells with the desired output.

Finally, we have reached our destination converting the negative values into zero.

3. Utilize Format Cells Feature

Besides, in some cases, you can make proper utilization of the format cells feature to perform if negative then zero with the excel formula. Check out the steps below to learn the process-

Steps:

Above all, choose the range of cells to change the format. Here, I have chosen cells (D5:D14).
While the cells are selected press Ctrl+1 to open the “Format Cells” dialog box.

Hence, in the dialog box select “Custom” and then type – #,##0;”0”.
Then press OK to continue.

In conclusion, we have our precious output in our hands by converting the excel formula if negative then zero.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

If Negative Then Zero.xlsx

Things to Remember

The format cells feature will change the format but not the values.

Conclusion

In this article, I have tried to cover all the methods to perform if negative then zero with the Excel formula. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

Excel Formula to Return Zero If Negative Value is Found

Excel Formula to Return Zero If Negative Value is Found: 3 Simple Methods

1. Use IF Function

2. Perform MAX Function

3. Utilize Format Cells Feature

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply