Thanks:  0
Likes:  0

# Thread: Multiple "if" arguments

1. 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

2. 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 ]

3. 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,

4. Thank you Asala42. That worked brilliantly !

5. 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 ]

6. Doh!

Good call, Mark. *slaps self*

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•