how to return zero if formula returns a negative

[Dylan driscoll]

I'm currently using the following formula to return zero if M3=0.
=IF(AND(M3=0),0,(M3-S3)*(A3-0.58)).
How can I modify this to also return zero if (M3-S3)*(A3-0.58) is a negative.
Thanks

 

[Rick Rothstein]

You can use the MAX function like this...

=MAX(0,IF(AND(M3=0),0,(M3-S3)*(A3-0.58)))

but I am wondering why you have AND(M3=0) as the first argument to the IF function... why are you using AND when there is only one logical expression as its argument?

 

[Dylan driscoll]

I've only been using excel for a week and this was the first formula that returned the result I was after.
before you replied I got it to work like this =IF(AND(M3=0),0,IF(AND((M3-S3)*(A3-0.58)<0),0,(M3-S3)*(A3-0.58)))
I guess there is a shorter way to write the same
I'll go back and try to use the =max

 

[Dylan driscoll]

The reason I used AND(M3=0),0, is because when M3 is a zero the formula is multiplying a negative by a negative which is obviously a positive. In this case it means an employee being paid extra when he did not work, AND(M3=0),0, stops this from happening