Not sure how to do this...

[Lipper]

I have multiple products that have multiple price tiers based on volume
ranges. For example, Product A may have the following:

1-100 units @ $10/unit
101-200 units @ $9.50/unit
201-400 units @ $9.00/unit

etc.

I need to be able to enter one number for the total volume of units I intend
to purchase per year and then calculate the average price per unit for the
year. Using the example above, if my annual requirement is 325 units I would
be charged $10/unit for the first 100 units, $9.50/unit for the next 100
units and $9.00/unit for the remaining 125 units.

What is the best way to calculate this?

Thanks in advance for any help!

 

[Chip Pearson]

I wrote some formulas a long time ago regarding this very problem. See
the Progressive Pricing section at
http://www.cpearson.com/excel/pricing.htm.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jacob Skaria]

If you can arrage your product list table as below; you can use the below
formula...Try this example with data in A15

Col A Col B Col C Col D
Product >= 1 >= 101 >= 201
Product A 10 9.5 9
Product B 8 7.5 7
Product C 6 5.5 5
Product D 4 3.5 3

Cell A8 = Product Name (string) = 'Product A'
Cell B8 = Units (numeric) = 325
In cell C8 try the below formula

=IF(B7<101,B7*VLOOKUP(A7,A15,2,0),100*VLOOKUP(A7,A15,2,0))+IF(B7>100,IF(B7<201,(B7-100)*VLOOKUP(A7,A15,3,0),100*VLOOKUP(A7,A15,3,0)+(B7-200)*VLOOKUP(A7,A15,4,0)),0)

If this post helps click Yes

 

[T. Valko]

Try this...

Create this table:

...........D..........E..........F
1........0..........10........=E1
2........100......9.5.......=E2-E1
3........200......9..........=E3-E2

A1 = total units = 325

=SUMPRODUCT(--(A1>D13),(A1-D13),F1:F3)

The technique is described here:

http://mcgimpsey.com/excel/variablerate.html

 

[Lipper]

Thank you for the helpful suggestions. The products I'm dealing with have 11
tiers for price, so I decided to try what appeared to be the simplest method.
Unfortunately, I can't get it to provide the correct answer!

Here is my fx:
=SUMPRODUCT(--(A1>{100;300;500;800;1100;1500;5000;6000;7000;8000;99999}),
(A1-{100;300;500;800;1100;1500;5000;6000;7000;8000;99999}),
{21.64;-0.43;-0.41;-0.22;-0.59;-0.39;-1.34;-0.66;-0.17;-0.18;-0.17})

This is the actual data I'm trying to build the fx to:

Volume Range Price/Unit
1-100 $21.64
101-300 $21.21
301-500 $20.80
501-800 $20.58
801-1100 $19.99
1101-1500 $19.60
1501-5000 $18.26
5001-6000 $17.60
6001-7000 $17.43
7001-8000 $17.25

8001 $17.08

The fx above gives me a total cost of $18,926 for 1000 units. Manually
calculating the price, it should be $20,738 ($20.74 / unit average) -
(100*21.64)+(200*$21.21)+(200*$20.80)+(300*$20.58)+(200*$19.99).

Where did I go wrong?

Lipper

 

[T. Valko]

Where did I go wrong?

You left out the first interval boundary which is 0.

=SUMPRODUCT(--(A1>{0;100;300;500;800;1100;1500;5000;6000;7000;8000}),
(A1-{0;100;300;500;800;1100;1500;5000;6000;7000;8000}),
{21.64;-0.43;-0.41;-0.22;-0.59;-0.39;-1.34;-0.66;-0.17;-0.18;-0.17})

I would create the table! When you use a table it's a lot easier to make
changes to the table than it is to edit the formula if /when the price
structure changes. (and we know prices always change!).

 

[Lipper]

It worked!

I tried to build a table and then reference cells in the table inside the
formula, but it wouldn't work for me. Do I need to enclose the cell
reference with "" or some other method?

Thanks very much for your help so far. If I can't get the cell reference
figured out it still saves me a ton of time.

Lipper

 

[T. Valko]

Here's a small sample file that demonstrates this.

xLipper.xls 14kb

http://cjoint.com/?jhxhP57sAb

 

[Lipper]

That did the trick!

I wish I could explain to you the amount of work you saved myself and my
team on this! I'm almost embarrased at how simple the fx is. Thanks again -
you've made alot of folks happy today!

Lipper

 

[T. Valko]

You're welcome. Thanks for the feedback!