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!

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!

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!

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!

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!

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" wrote:

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!