Excel Formula to Return Zero If Negative Value is Found

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.

Perform If Negative Then Zero with Excel Formula


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)

Use IF Function to Input Zero If Negative with Excel Formula

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

Perform If Negative Then Zero with Excel Formula

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.

Perform If Negative Then Zero with Excel Formula

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

Perform If Negative Then Zero with Excel Formula

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.

Utilize Format Cells Feature to Perform If Negative Then Zero

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

Perform If Negative Then Zero with Excel Formula

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.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo