How can I calculate total pricing based on multiple priicing & usage tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

Is your $250,000 unit example calculation correct??? I would have expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

What I'm trying to do is set up a formula where I can put in the total amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,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?


"Siper1" wrote:

What I'm trying to do is set up a formula where I can put in the total amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

Repeating the same exact example that I said "I don't understand how you got
your numbers for it" will not make me understand it any more. My question to
you is HOW did you get those dollar figures from 250,000 units. It looks
like you divvied it up 50,000 to the first tier, 100,000 to the second tier
and 100,000 to the third tier. What I don't understand is why you didn't
divvy it up 50,000 to the first tier, 150,000 to the second tier and 50,000
to the third tier. What rule are you following that prevents you from using
150,000 of the 250,000 units for that second tier? And if for some reason
you can't use the 150,000, why aren't you using 149,999 then?

Rick


"Siper1" wrote in message
...
What I'm trying to do is set up a formula where I can put in the total
amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have
expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price
schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

Does it really matter what the tier ranges are? Does it make it easier if
it's done one way or another (I don't know)? I have 3 pricing tiers, need to
utilize waterfall pricing (which steps), and am lost as to where to begin.


"Rick Rothstein (MVP - VB)" wrote:

Repeating the same exact example that I said "I don't understand how you got
your numbers for it" will not make me understand it any more. My question to
you is HOW did you get those dollar figures from 250,000 units. It looks
like you divvied it up 50,000 to the first tier, 100,000 to the second tier
and 100,000 to the third tier. What I don't understand is why you didn't
divvy it up 50,000 to the first tier, 150,000 to the second tier and 50,000
to the third tier. What rule are you following that prevents you from using
150,000 of the 250,000 units for that second tier? And if for some reason
you can't use the 150,000, why aren't you using 149,999 then?

Rick


"Siper1" wrote in message
...
What I'm trying to do is set up a formula where I can put in the total
amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have
expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price
schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

If you have the total # units in A1 (I named the range A1 as
Units_Sold), then put the following in any cell (except A1):

=MAX(MIN(50000, units_sold)*1,0) + (MIN(MAX(units_sold-50000,0),
100000)*0.75) + =(MAX(units_sold-150000,0))*0.5

Of course it matters how the units are distributed through the tiers... the
cost changes depending on how the units are distributed (look at the cost
you got for your example compared to the cost I got in my counter-example
from my first reply in this thread). There must be some rule you follow to
allocate the units to the tiers... I would think you would apply the maximum
that can "fit" in the tier before you move on to the next tier (which is
what I showed in my counter-example). However, your examples do not do this;
hence, my question as to how you are deciding how many units to apply per
tier.

Rick


"Siper1" wrote in message
...
Does it really matter what the tier ranges are? Does it make it easier if
it's done one way or another (I don't know)? I have 3 pricing tiers, need
to
utilize waterfall pricing (which steps), and am lost as to where to begin.


"Rick Rothstein (MVP - VB)" wrote:

Repeating the same exact example that I said "I don't understand how you
got
your numbers for it" will not make me understand it any more. My question
to
you is HOW did you get those dollar figures from 250,000 units. It looks
like you divvied it up 50,000 to the first tier, 100,000 to the second
tier
and 100,000 to the third tier. What I don't understand is why you didn't
divvy it up 50,000 to the first tier, 150,000 to the second tier and
50,000
to the third tier. What rule are you following that prevents you from
using
150,000 of the 250,000 units for that second tier? And if for some reason
you can't use the 150,000, why aren't you using 149,999 then?

Rick


"Siper1" wrote in message
...
What I'm trying to do is set up a formula where I can put in the total
amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have
expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price
schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing &
usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

Agreed .. then how do I construct a worksheet for that?

"Rick Rothstein (MVP - VB)" wrote:

Repeating the same exact example that I said "I don't understand how you got
your numbers for it" will not make me understand it any more. My question to
you is HOW did you get those dollar figures from 250,000 units. It looks
like you divvied it up 50,000 to the first tier, 100,000 to the second tier
and 100,000 to the third tier. What I don't understand is why you didn't
divvy it up 50,000 to the first tier, 150,000 to the second tier and 50,000
to the third tier. What rule are you following that prevents you from using
150,000 of the 250,000 units for that second tier? And if for some reason
you can't use the 150,000, why aren't you using 149,999 then?

Rick


"Siper1" wrote in message
...
What I'm trying to do is set up a formula where I can put in the total
amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have
expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price
schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing & usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

Try this formula (assuming your quantity is in A1)...

=1*MIN(--A1,50000)+0.75*MIN(MAX(A1-50000,0),150000)+0.5*MAX(A1-200000,0)

Rick


"Siper1" wrote in message
...
Agreed .. then how do I construct a worksheet for that?

"Rick Rothstein (MVP - VB)" wrote:

Repeating the same exact example that I said "I don't understand how you
got
your numbers for it" will not make me understand it any more. My question
to
you is HOW did you get those dollar figures from 250,000 units. It looks
like you divvied it up 50,000 to the first tier, 100,000 to the second
tier
and 100,000 to the third tier. What I don't understand is why you didn't
divvy it up 50,000 to the first tier, 150,000 to the second tier and
50,000
to the third tier. What rule are you following that prevents you from
using
150,000 of the 250,000 units for that second tier? And if for some reason
you can't use the 150,000, why aren't you using 149,999 then?

Rick


"Siper1" wrote in message
...
What I'm trying to do is set up a formula where I can put in the total
amount
of units used into 1 cell and then have the cost calculated based on
waterfall pricing.

Same example

250,000 Units

0-50,000 ($1) = $50,000
50,001 -150,000 ($.75) =$75,000
151,000+ ($.50) = $50,000

Total = $175,000



"Rick Rothstein (MVP - VB)" wrote:

Is your $250,000 unit example calculation correct??? I would have
expected
this

50000 = $50000
150000 = $112500
50000 = $25000
===== ======
250000 = $187500

If I am not correct, please explain in more detail how your price
schedule
is applied.

Rick


"Siper1" wrote in message
...
How can I calculate total pricing based on multiple priicing &
usage
tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

On Sun, 10 Aug 2008 20:22:01 -0700, Siper1
wrote:

How can I calculate total pricing based on multiple priicing & usage tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

This seems to be the "standard" tax bracket problem.

Set up a table with three columns as follows:

Units Base Price
- 0 $1.00
50,000 $50,000.00 $0.75
150,000 $125,000.00 $0.50

I NAME'd it 'tbl'.

Then, with your "units" in A1, use this formula:

=(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)+VLOOKUP(A1,Tb l,2)
--ron

That worked until I got to the higest tier. Then the calculations were off.
It was calculating a price that was too high. (540K units should = $216,000.
With this formula = $232,000)

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

D19 = Monthly Usuage

To test I made all the tiers the same price ($.40)

Need

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

Tested

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


"Ron Rosenfeld" wrote:

On Sun, 10 Aug 2008 20:22:01 -0700, Siper1
wrote:

How can I calculate total pricing based on multiple priicing & usage tiers
(IF Then or AND):

Usage - Price
0-50,000 = $1
50,001 - 150,000 = $.75
150,001+ = $.50

Example:

250,000 units

Need to automate the following:

50,000 = $50,000
50,001 - 150,000 = $75,000
150,001+ = $50,000

Total Due = $175,000

This seems to be the "standard" tax bracket problem.

Set up a table with three columns as follows:

Units Base Price
- 0 $1.00
50,000 $50,000.00 $0.75
150,000 $125,000.00 $0.50

I NAME'd it 'tbl'.

Then, with your "units" in A1, use this formula:

=(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)+VLOOKUP(A1,Tb l,2)
--ron

On Mon, 11 Aug 2008 11:36:01 -0700, Siper1
wrote:

That worked until I got to the higest tier. Then the calculations were off.
It was calculating a price that was too high. (540K units should = $216,000.
With this formula = $232,000)


Then it is not clear how you are doing the calculations.

Here is how my "table" method does it:

For $540,000 I get a result of $320,000 done as follows:

Setup:

0-50,000 $1 per unit
50,001-150,000 $0.75 per unit
150,001+ $0.50 per unit

540,000 units

First 50,000 -- (1*50,000) = $50,000
Next 100,000 -- (0.75*100,000) = $75,000
Last 390,000 -- (0.50*390,000) = $195,000

Adding up those amounts comes to $320,000

If you are using the different Tiering that you posted in this last message,
then I get $219,000; not $216,000

This would be the table setup for your last tiering:

0 0 $0.50
50,000 $ 25,000.00 $0.40
500,000 $205,000.00 $0.35

It is only if the table is set up to use $0.40 for each tier that I get
$216,000 for 540K units.

The table then looks like:

0 0 $0.40
50,000 $ 20,000.00 $0.40
500,000 $200,000.00 $0.40

Perhaps you are not setting up the columns properly.

The first column is the BOTTOM of each tier.

The second column represents the amount paid for the units in the adjacent
first column. If 'tbl' is in H2:J4 then

H2: 0
H3: =I2+J2*(H3-H2)

then H3 is "filled-down" to H4

H4: =I3+J3*(H4-H3)

The third column is the factor for the amount OVER the base of that tier.
--ron

This formula that you gave me earlier 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 this but I need it to cover multiple
periods and want to show the results on a single spreadsheet:

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

I appreciate the help and would be even more lost without it. Please
disregard my earlier tiers and use the tiers below: This is how the table is
set up on my spreadsheet -columns & rows.

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





"Ron Rosenfeld" wrote:

On Mon, 11 Aug 2008 11:36:01 -0700, Siper1
wrote:

That worked until I got to the higest tier. Then the calculations were off.
It was calculating a price that was too high. (540K units should = $216,000.
With this formula = $232,000)


Then it is not clear how you are doing the calculations.

Here is how my "table" method does it:

For $540,000 I get a result of $320,000 done as follows:

Setup:

0-50,000 $1 per unit
50,001-150,000 $0.75 per unit
150,001+ $0.50 per unit

540,000 units

First 50,000 -- (1*50,000) = $50,000
Next 100,000 -- (0.75*100,000) = $75,000
Last 390,000 -- (0.50*390,000) = $195,000

Adding up those amounts comes to $320,000

If you are using the different Tiering that you posted in this last message,
then I get $219,000; not $216,000

This would be the table setup for your last tiering:

0 0 $0.50
50,000 $ 25,000.00 $0.40
500,000 $205,000.00 $0.35

It is only if the table is set up to use $0.40 for each tier that I get
$216,000 for 540K units.

The table then looks like:

0 0 $0.40
50,000 $ 20,000.00 $0.40
500,000 $200,000.00 $0.40

Perhaps you are not setting up the columns properly.

The first column is the BOTTOM of each tier.

The second column represents the amount paid for the units in the adjacent
first column. If 'tbl' is in H2:J4 then

H2: 0
H3: =I2+J2*(H3-H2)

then H3 is "filled-down" to H4

H4: =I3+J3*(H4-H3)

The third column is the factor for the amount OVER the base of that tier.
--ron

On Mon, 11 Aug 2008 16:50:01 -0700, Siper1
wrote:

This formula that you gave me earlier 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 this but I need it to cover multiple
periods and want to show the results on a single spreadsheet:

I did not give you that formula. You've been responding to me but *Rick* gave
you that formula.

The formula *I* gave you, along with instructions for how to set up the Tier
table, was:


======================
Set up a table with three columns as follows:

Bottom of Base Amt Price
Tier
0 0.00 $1.00
50,000 $ 50,000.00 $0.75
150,000 $125,000.00 $0.50

I NAME'd it 'tbl'.

Then, with your "units" in A1, use this formula:

=(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)+VLOOKUP(A1,Tb l,2)
=============================

Bottom of Tier is obvious.

Base Amount is the total amount that would be paid if you had units that were
at the Bottom of Tier.

So for the 1st tier it is zero.

The second column represents the amount paid for the units in the adjacent
first column. If 'tbl' is in H2:J4 then

H2: 0
H3: =I2+J2*(H3-H2)

then H3 is "filled-down" to H4

H4: =I3+J3*(H4-H3)
----------------------------------

In order for the formula *I* gave you to work, you must set up the table
properly.

For your latest table, I also posted an example of what the table would look
like:

0 0 $0.50
50,000 $ 25,000.00 $0.40
500,000 $205,000.00 $0.35

---------------------------------------

--ron