Return number as 0 if negative AND as another cell if over.

[Lmaonade]

Hi guys,

I have a spreadsheet. In one of the colomns I need the sum calculation to return 0 if it's negative and if it's over a certain number then I need it to return as that number as a maximum.

Example:

Cell A1 is 10,
Cell B1 is 12,

In Cell C1 I want to put a formula that tells me A1 minus B1. If the number is negative (Which is would be) I want it to return 0. which I'm thinking would just be as simple as =IF(A1-B1<0,0,A1-B1),But I want another rule in this cell that says if the number returned is higher than a number in D1 (Let's say for example that number is 8) Then I want the number to return as 8. Basically whatever number is in D1 I want that to be the maximum number C1 returns.

I'm sorry if I'm not explaining myself well.

Any help would be appreciated.

Thanks,
Andy

 

[FormR]

Hi, here is one option you can try:

=MIN(D1,MAX(0,A1-B1))

 

[Lmaonade]

That worked perfectly! Thank you very much!

 

[snjpverma]

you can also use this.

=MEDIAN(0,A1-B1,D1)