Creating Tiers

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Looking for a forumla for 3 tiers.

I have 10,000 tires:

Tires 0-3200 cost $50
Tires: 3201 - 5000 cost $45
Tires: 5001 + cost $40

how do i set this up with 3 columms showing total cost of each tier. hope I
am clear.

Marc
 
Cell A1: Number of tires purchased (variable number) Lets use 10,000

The three tiers are constant with the ranges fixed as explained above.

Units 0 to 3200 costs $50 so = (3200 * 50 = 160,000) 6800 tires left
Units 3201 to 5000 cost 45 so (2000 * 45 = 90,000) 4800 Tires left
Units: 5001 + cost 40 so ( 4800 * 40 = 192,000)

Total cost for 10,000 units would be $442,000 the formula would be set up to
hand variable #'s instead of 10,000 maybe 5,000 , 2,000 100,000 etc
 
Looking for a forumla for 3 tiers.

I have 10,000 tires:

Tires 0-3200 cost $50
Tires: 3201 - 5000 cost $45
Tires: 5001 + cost $40

how do i set this up with 3 columms showing total cost of each tier. hope I
am clear.

Marc
Click to expand...

Set up a Price Table someplace on your worksheet as follows:

NumTires BaseCost CostPerTire
0 $ 0 $50
3200 $160,000 $45
5000 $241,000 $40

Then use this formula:

=VLOOKUP(TiresPuchased,PriceTbl,2)+(TiresPuchased-
VLOOKUP(TiresPuchased,PriceTbl,1))*VLOOKUP(TiresPuchased,PriceTbl,3)


--ron
 
Cell A1: Number of tires purchased (variable number) Lets use 10,000

The three tiers are constant with the ranges fixed as explained above.

Units 0 to 3200 costs $50 so = (3200 * 50 = 160,000) 6800 tires left
Units 3201 to 5000 cost 45 so (2000 * 45 = 90,000) 4800 Tires left
Units: 5001 + cost 40 so ( 4800 * 40 = 192,000)

Total cost for 10,000 units would be $442,000 the formula would be set up to
hand variable #'s instead of 10,000 maybe 5,000 , 2,000 100,000 etc
Click to expand...


Your math is wrong. 5000-3200 = 1800; not 2000
--ron
 
yeah I found your response on another message board as well. I did it a
different way but thanks for the response
 
can i attached a workbook. if so I'll show you what I did just for an FYI
Click to expand...

I usually don't like to download workbooks. But if you post the formula you
are using ...

One advantage of the Table approach has to do with ease of maintainability. It
is a trivial matter to change the size or pricing of any tier.
--ron
 
Back
Top