Tiered Pricing Calculations

S

Siper1

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

Siper1

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
 
S

Siper1

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?
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

Siper1

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.
 
S

skoalnreds

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
 
S

skoalnreds

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
Click to expand...

Sorry - typo: ignore the second "=" in the formula...
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

Siper1

So I'm using the following pricing:

0 49,999 0.60
50,000 199,999 0.50
200,000 - 0.40

Is this correct then:

=MAX(MIN(49999, A1)*.6) + (MIN(MAX(A1-49999,0),A24> 199999)*0.5) +
(MAX(A1-200000,0))*0..4
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Ron Rosenfeld

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
Click to expand...

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,Tbl,2)
--ron
 
S

Siper1

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
 
R

Ron Rosenfeld

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)
Click to expand...


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
 
S

Siper1

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
 
R

Ron Rosenfeld

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:
Click to expand...

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,Tbl,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
 
R

Rick Rothstein \(MVP - VB\)

Ron Rosenfeld said:
I did not give you that formula. You've been responding to me but *Rick*
gave you that formula.
Click to expand...

And I posted a correction for the formula in the *new* thread the OP started
shortly after midnight (my local time) where he repeated the question you
just responded to.

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pricing Calculation Help 6
Tiered Cumlative Commisions By Month 1
Excel Excel 2007 - SUMPRODUCT for Tiered Pricing System 0
Tiered Pricing Formulae - is this possible? 3
Not sure how to do this... 9
"Stair-Stepped" Commissions Question. 2
Need help writing formulas for following functions - 4
Tiered Pay Scale Calculations 3

Top