Wasim Akram
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Simple Methods to Excel Formula to Return Zero If Negative Value is Found
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.
Read More: How to Show Negative Numbers in Excel (4 Quick Tricks)
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.
Read More: How to Add Negative Numbers in Excel (4 Easy Ways)
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.
Read More: [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)
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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
