Multiple "if" arguments

marley

Board Regular
Joined
Mar 18, 2002
Messages
94
I need to have a formula that calculates a total based on a sliding scale, i.e. the cost per thousand goes down as the quantity increases. If total quantity ordered is less than 2500, the cost per 1,000 is "A", 2501-5000 the cost is "B",5001-7500 the cost is "C" and 7500+, the cost is "D". Is there an easy formula for this argument?

Thank you
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Use a VLOOKUP function to determine cost/1000...


=VLOOKUP(A2,{0,"A";2501,"B";5001,"C";7501,"D"},2)

...where A2 contains your Quantity.
This message was edited by Mark W. on 2002-04-25 11:57
 
Upvote 0
You could also use a not-too-lenghy if-statment (looking at A2 for quantity):

=(A2/1000)*(IF(A2<2500,A2*CostA,IF(A2<5000,CostB,IF(A2<7500,CostC,CostD))))

Where CostA,CostB,CostC,and CostD are your cost values per 1000 units.

Hope that helps,
Adam
 
Upvote 0
On 2002-04-25 12:02, marley wrote:
Thank you Asala42. That worked brilliantly ! :)

You might take another look at this formula...


=(A2/1000)*(IF(A2<2500,A2*CostA,IF(A2<5000,CostB,IF(A2<7500,CostC,CostD))))


Or, use...

=A2/1000*VLOOKUP(A2,{0,CostA;2501,CostB;5001,CostC;7501,CostD},2)
This message was edited by Mark W. on 2002-04-25 12:14
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top