# nested/complicated IF help

#### bhmcpfs

##### Board Regular
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### lenze

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

lenze

#### bhmcpfs

##### Board Regular
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

##### Legend
Code:
``=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

##### Board Regular
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

##### Board Regular
Working like a charm lenze, thanks so much.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,377
Messages
5,836,914
Members
430,462
Latest member
djkiwi1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back