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