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