nested/complicated IF help

bhmcpfs

Board Regular
Joined
Apr 1, 2002
Messages
106
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Can you give an example of what you want??
Where does cost, factor, and cap come into play?

lenze
 

bhmcpfs

Board Regular
Joined
Apr 1, 2002
Messages
106
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
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Apr 1, 2002
Messages
106
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
Joined
Apr 1, 2002
Messages
106
Working like a charm lenze, thanks so much.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top