Thread: Negative Number as Zero Part Duece

Another problem just came up..... shg was a great help earlier, hopefully someone will be able to help me with this one.

Formula is =(E43-E42)+B45

If the total of the above formula is a negative number - I need it to be a value of zero..... again, formatting will not work in this case. It must be a value.

All help is appreciated!!

=max(0,(e43-e42)+b45)

Gary, the two answers here apply the same principle that shg used in his solution to your other post. Can you see how it works? MAX returns the largest value of the list of values specified.

=MAX(0,1,2,3,4,5) will return 5
=MAX(0,-1,-2,-3,-4,-5) will return 0

You can use a reference to another cell or a calculation as an argument for MAX, too.

=MAX(A1, A2, A3) will look at the values of the cells A1, A2 and A3 and return the biggest of the three.

=MAX(0,A1-A2) will perform the calculation A1-A2 and then compare the result with zero. If the result is bigger than zero, it will return the calculated result, but if it is a negative number, MAX will return 0, because that is the bigger of the two values.

Once you've understood how this works, you should be able to apply this principle to other formulas in your workbook, where you want to replace negative numbers with zero.

If the result of a calculation may return a negative number, but you want a zero instead, apply

MAX(0,<your calculation>)

hth

ntvinh986,

this forum always welcomes new users with honest intentions. You may want to check the forum rules to find out about the proper ways of behaving around here.

If you have a contribution to a question, feel welcome to post it in the respective thread. If you have a question, please open a new post.

Courteous behaviour is imperative and a little information about yourself and the Excel version you're using is appreciated, so we can better answer your questions.

The moderators make short work of posters who ignore the forum rules.

regards