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.
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.
Read More: How to Show Negative Numbers in Excel
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
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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles
- How to Add Brackets to Negative Numbers in Excel
- How to Change Positive Numbers to Negative in Excel
- How to Apply Formula for Positive and Negative Numbers in Excel
- Excel Negative Numbers in Brackets and Red
- How to Count Negative Numbers in Excel
- How to Make a Group of Cells Negative in Excel
- How to Put a Negative Number in Excel Formula
- How to Make Negative Numbers Red in Excel
- How to Convert Negative Value to Positive in Excel Using Formula
- Excel Formula If Cell Contains Negative Number
- Excel Formula to Return Blank If Cell Value Is Negative
- How to Sum Negative and Positive Numbers in Excel