Re: Not sure how to do this...

Here's a small sample file that demonstrates this.

xLipper.xls 14kb

http://cjoint.com/?jhxhP57sAb

--
Biff
Microsoft Excel MVP


"Lipper" <Lipper@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D63E1158-3CF1-4160-B5F4-A8FA052AA005@xxxxxxxxxxxxxxxx
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;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!).


--
Biff
Microsoft Excel MVP


"Lipper" <Lipper@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B5EF896-DBB3-4C06-A059-FC70FE599295@xxxxxxxxxxxxxxxx
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

"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!





.

Relevant Pages

  • Re: Not sure how to do this...
    ... I tried to build a table and then reference cells in the table inside the ... If I can't get the cell reference ... changes to the table than it is to edit the formula if /when the price ... The fx above gives me a total cost of $18,926 for 1000 units. ...
    (microsoft.public.excel.misc)
  • Re: vlookup lookup_value text
    ... Microsoft Excel MVP ... I know it would be better to just reference a cell containing this, ... A1 = 18" Bead Diameter ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Update Sheet1
    ... you need to put the function in a new cell because it needs to ... reference both the old price and the new price. ... problem is that I do not have space for another column in Sheet 1, ... Reference Number Description Price/Eur New Price/Eur ...
    (microsoft.public.excel.programming)
  • Re: Not sure how to do this...
    ... I tried to build a table and then reference cells in the table inside the ... If I can't get the cell reference ... changes to the table than it is to edit the formula if /when the price ... The fx above gives me a total cost of $18,926 for 1000 units. ...
    (microsoft.public.excel.misc)
  • Re: Not sure how to do this...
    ... Microsoft Excel MVP ... I tried to build a table and then reference cells in the table inside ... Do I need to enclose the cell ... changes to the table than it is to edit the formula if /when the price ...
    (microsoft.public.excel.misc)
Quantcast