How can set up Excel to calculate total pricing based on multiple pricing &
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

Say the units are entered into A1

Do I build the following table?

Tier (Start) Tier (Cap) Price
0 50,000 $1
50,001 150,000 $.75
151,001 1,000,000 $.5

What formula do I put in A1?


"T. Valko" wrote:

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

Try this:

A1 = 250000

Table:

................D..........E..........F.....
1.............0..........1.........=E1
2.....50000........0.75.....=E2-E1
3...150000........0.50.....=E3-E2

Formula:

=SUMPRODUCT(--(A1D1:D3),A1-D1:D3,F1:F3)

Result = 175000

Note that the last tier is open ended. Anything 150000 is calculated at
0.50.

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
Say the units are entered into A1

Do I build the following table?

Tier (Start) Tier (Cap) Price
0 50,000 $1
50,001 150,000 $.75
151,001 1,000,000 $.5

What formula do I put in A1?


"T. Valko" wrote:

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to
deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple
pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

When I tried that it didn't tier the prices. It added them up as 1 price.

I need to calculate them as individual tiers and then add them together.

Thoughts?

"T. Valko" wrote:

Try this:

A1 = 250000

Table:

................D..........E..........F.....
1.............0..........1.........=E1
2.....50000........0.75.....=E2-E1
3...150000........0.50.....=E3-E2

Formula:

=SUMPRODUCT(--(A1D1:D3),A1-D1:D3,F1:F3)

Result = 175000

Note that the last tier is open ended. Anything 150000 is calculated at
0.50.

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
Say the units are entered into A1

Do I build the following table?

Tier (Start) Tier (Cap) Price
0 50,000 $1
50,001 150,000 $.75
151,001 1,000,000 $.5

What formula do I put in A1?


"T. Valko" wrote:

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to
deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple
pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

Here's a small sample file that demonstrates this:

Price tiers.xls 14kb

http://cjoint.com/?ilxdsTVzGk

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
When I tried that it didn't tier the prices. It added them up as 1 price.

I need to calculate them as individual tiers and then add them together.

Thoughts?

"T. Valko" wrote:

Try this:

A1 = 250000

Table:

................D..........E..........F.....
1.............0..........1.........=E1
2.....50000........0.75.....=E2-E1
3...150000........0.50.....=E3-E2

Formula:

=SUMPRODUCT(--(A1D1:D3),A1-D1:D3,F1:F3)

Result = 175000

Note that the last tier is open ended. Anything 150000 is calculated at
0.50.

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
Say the units are entered into A1

Do I build the following table?

Tier (Start) Tier (Cap) Price
0 50,000 $1
50,001 150,000 $.75
151,001 1,000,000 $.5

What formula do I put in A1?


"T. Valko" wrote:

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to
deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple
pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000

The following formula works greatt until I exceed 500,000
units (ie. 480,000 units = $192,00)

=D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0)

J3 = 540,000

I could use something as simple as the link you sent me but I need it to
cover multiple periods and want to show the results on a single spreadsheet:

http://cjoint.com/data/ilxdsTVzGk.htm

Actual:

A B C D
Price
19 Tier 1 0 49,999 0.50
20 Tier 2 50,000 499,999 0.40
21 Tier 3 500,000 0.35


Tiers used as baseline to validate formula is correct (Should = $216,000)

A B C D
Price
19 Tier 1 0 49,999 0.40
20 Tier 2 50,000 499,999 0.40
21 Tier 3 500,000 0.40


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

Price tiers.xls 14kb

http://cjoint.com/?ilxdsTVzGk

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
When I tried that it didn't tier the prices. It added them up as 1 price.

I need to calculate them as individual tiers and then add them together.

Thoughts?

"T. Valko" wrote:

Try this:

A1 = 250000

Table:

................D..........E..........F.....
1.............0..........1.........=E1
2.....50000........0.75.....=E2-E1
3...150000........0.50.....=E3-E2

Formula:

=SUMPRODUCT(--(A1D1:D3),A1-D1:D3,F1:F3)

Result = 175000

Note that the last tier is open ended. Anything 150000 is calculated at
0.50.

--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
Say the units are entered into A1

Do I build the following table?

Tier (Start) Tier (Cap) Price
0 50,000 $1
50,001 150,000 $.75
151,001 1,000,000 $.5

What formula do I put in A1?


"T. Valko" wrote:

Try this:

A1 = units = 250,000

=SUMPRODUCT(--(A1{0;50000;150000}),(A1-{0;50000;150000}),{0.6;-0.1;-0.1})

Probably better if you create a little table. That way it's easier to
deal
with price changes/ level changes.

See this:

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


--
Biff
Microsoft Excel MVP


"Siper1" wrote in message
...
How can set up Excel to calculate total pricing based on multiple
pricing
&
usage tiers:

Usage - Price
0-50,000 = $.60
50,001 - 150,000 = $.50
150,001+ = $.40

Example:

250,000 units

Need to automate the following:

50,000 = $30,000
50,001 - 150,000 = $50,000
150,001+ = $40,000

Total Due = $120,000