nested/complicated IF help

[bhmcpfs]

looking to create a nested IF formula based on the following:

cost range, factor, cap
2.00-100.00, 3.5, 300.00
100.01-1000.00, 3.0, 2500.00
1000.01-2500.00, 2.5, 5000.00
2500.01-5000.00, 2.0, 7500.00

Spent most of the morning trying to get this... Getting frustrated... it's a bit complicated for me, but seems like it should be doable... Any help/guidance would be greatly appreciated.

Mac

 

[lenze]

Can you give an example of what you want??
Where does cost, factor, and cap come into play?

lenze

 

[bhmcpfs]

Sorry, should have been much clearer.

Cost is entered manually, and based on the the criteria, the price is the output.
Two examples: 1- cost = 50.00, so first set of criteria applies as it's in the 2.00-100.00 range. Cost is multiplied by factor (3.5), so price is 175.00. Ex 2- cost = 90.00, still in first set of criteria. Cost multiplied by 3.5 = 315.00, but cap is 300.00, so price should be 300.00

Hope this clears up my ambiguity.

Thanks again.

 

[lenze]

=IF(A1<=100,MIN(3.5*A1,300),IF(A1<=1000,MIN(3*A1,2500),IF(A1<=2500,MIN(2.5*A1,5000),IF(A1<=5000,MIN(2*A1,7500)))))

What if the price is over 5000??

lenze

 

[bhmcpfs]

Thanks lenze... will try your formula and post back up. I'm only halfway there (got the factors all nested, just hadn't been able to work out the variable caps).

I asked about the price over 5K (as well as under 2). Was told they didn't/have never had any. Knowing Murphy's Law, they're all but guaranteed to have one now. Figured I'd just do what was asked. If I code it one way, they'll want it another way.

 

[bhmcpfs]

Working like a charm lenze, thanks so much.

I was heading down the IF/AND route - a lot more complicated. I much prefer simple.