Create tier list that outputs a quantity

[dw970906]

I am hoping someone might be able to help. I am trying to create a tier list but need quantities to output properly.

For example, I have 3 tiers:
500 (0-500)
1000 (501-1000)
5000 (1001-5000)

If I have an input # of 500, the 500 tier will be quantity of 1. However, if I have an input # of 900, I would like the tier of 1000 to have a quantity of 1 instead of 2, 500 tier. The reason I want to round up to 1, 1000 is due to the price.

for example, 1, 500 tier will cost $65,000 and 1, 1000 tier will cost $91,000. So having the quantity to go to 1, 1000 instead of 2, 500 is more cost effective (for the input # of 900).

Essentially, I would like the quantity of tiers to optimize so that the cost is beneficial for a customer. other examples:

input # is 2800
tiers are:
500 = 0 quantity
1000 = 3 quantity
5000 = 0 quantity

input # is 6500
500 = 1 quantity
1000 = 1 quantity
5000 = 1 quantity

Thank you

 

[Crystalyzer]

The Solver Add-in handles this like a knife through butter.

 

[dw970906]

The Solver Add-in handles this like a knife through butter.

Thanks for the tip. Could you provide an example on how to use this for getting quantities? I haven't used solver before. I want to add this to a template for others to input a number and the outputs are quantities.

 

[dw970906]

I have the formulas working but they are rounding giving me 2 of the lowers tier at 500 vs 1 of 1000. Can anyone help?

for 4501 input number, I want 1 5000 tier, but I am getting 4, 1000 tiers and 2 500 tiers.

Calculate 5000 using: =if(and(K8>4501,K8 < 5000),5000,K8)
# of 5000 =int((K10)/A5)
remaining =K10-K11*5000
calculate 1000 using =if(and(K12>501 , K12<1000),1000,K12)
# of 1000 =if( K13 < 0,0,ROUNDDOWN(K13/1000) )
remaining =K12-K14*1000
# of 500 =if( K15 < 0,0,ROUNDUP(K15/500) )