Multiple "if" arguments

marley · Apr 25th, 2002, 12:32 PM

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

Mark W. · Apr 25th, 2002, 12:36 PM

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 ]

Asala42 · Apr 25th, 2002, 12:45 PM

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

marley · Apr 25th, 2002, 01:02 PM

Thank you Asala42. That worked brilliantly !

Mark W. · Apr 25th, 2002, 01:06 PM

Quote:

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 ]

Asala42 · Apr 25th, 2002, 01:08 PM

Doh!

Good call, Mark. *slaps self*

Adam

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 12:32 PM	#1
marley Board Regular Join Date: Mar 2002 Location: tucson Posts: 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

Apr 25th, 2002, 12:36 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Apr 25th, 2002, 12:45 PM	#3
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	You could also use a not-too-lenghy if-statment (looking at A2 for quantity): =(A2/1000)(IF(A2<2500,A2CostA,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

Apr 25th, 2002, 01:02 PM	#4
marley Board Regular Join Date: Mar 2002 Location: tucson Posts: 94	Thank you Asala42. That worked brilliantly !

Apr 25th, 2002, 01:08 PM	#6
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Doh! Good call, Mark. slaps self Adam