Creating Tiers

[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

 

[Rishie]

Can you post attach a sample file so that we can understand your
question better?

 

[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

 

[Ron Rosenfeld]

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

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

 

[Ron Rosenfeld]

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

Your math is wrong. 5000-3200 = 1800; not 2000
--ron

 

[Marc]

yeah I found your response on another message board as well. I did it a
different way but thanks for the response

 

[Marc]

can i attached a workbook. if so I'll show you what I did just for an FYI

 

[Ron Rosenfeld]

can i attached a workbook. if so I'll show you what I did just for an FYI

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