IF, MIN, MAX assistance

[neatfeatguy]

I've been working too much and everything is running together. I got 1 equation to work flawlessly, but the second is throwing me off and it's just not working 100% correct.

Here's the one that works. It requires information to be pulled from 1 cell to get the answer:
=IF(D2<21,0,(TRUNC((MIN(D2,50)*0.001+MAX(MIN(D2-70,50),0)*0.001/2+MAX(MIN(D2-120,80),0)*0.001/4),3)))


I can't get the one that's giving me troubles to work 100%, it requires information from 2 cells and the equation needs to work like such:
IF (cell < 30) = 0
31 thru 130 = *.001/2
131 thru 200 = *.001/4
results must truncate after the 3rd decimal spot (thousandths) and regardless of the amounts entered into the cells, the max result that can be returned is .067

30 thru 130 (100 max) * .001/2) = .05
131 thru 200 = (70 max)*.001/4 = .017
The max result can only be .067

Here's what I've come to ending up with, and it's not quite working correctly:
=IF(D5<31,0,(TRUNC((MIN((D5+D3)-30,100)*0.001/2+MAX(MIN((D5+D3)-130),0)*0.001/4),3)))

Entering data above 200 total combined points should still only return the max result of .067. Like for the working equation (first one posted), the max return result is .095 and any number entered in above 200 still only returns the max result of .095.

What am I missing in the second one to keep the max result from being exceeded? I've attached the worksheet I've been playing in. Hopefully someone can help me out here and hopefully I've explained this well enough to follow.

Oh, and if there's an easier way to do this, I'm all ears.

testing.xlsx

Thanks.

[Ace_XL]

Without looking at your sheet, just try

=MIN(0.67,IF(D5<31,0,(TRUNC((MIN((D5+D3)-30,100)*0.001/2+MAX(MIN((D5+D3)-130),0)*0.001/4),3))))

[neatfeatguy]

I gave it a try, but it still returns the same results. Once D5+D2 go over 200, the result keeps increasing instead of being stopped at the cap.

I'm going to give my bran a rest from all the numbers and play with it some more later.

Thanks for the suggestion, just didn't work for me.

[ChemistB]

Here's what I come up with

=MIN(0.067,D5*LOOKUP(D5, {0,31,131}, {0,0.0005,0.00025}))

Update to truncate at 0.001

=MIN(0.067,TRUNC(D5*LOOKUP(D5, {0,31,131}, {0,0.0005,0.00025}), 3))

[thebellguy]

This stops it at .05 which is what would happen if both minimums were met (100, 0)

Please Login or Register  to view this content.

I would personally do it like this:

Please Login or Register  to view this content.

[neatfeatguy]

This stops it at .05 which is what would happen if both minimums were met (100, 0)

Please Login or Register  to view this content.

I would personally do it like this:

Please Login or Register  to view this content.

Nice. This is easier to follow and does the job. Thank you!