Here's a small sample file that demonstrates this.

xLipper.xls 14kb

http://cjoint.com/?jhxhP57sAb

--
Biff
Microsoft Excel MVP


"Lipper" wrote in message
...
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" wrote:

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;80 00}),
(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!).


--
Biff
Microsoft Excel MVP


"Lipper" wrote in message
...
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;999 99}),
{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

"Lipper" wrote:

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!