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

On Aug 11, 12:11*pm, skoalnreds wrote:
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

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

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

How? Do I sum in another field and make reference to A1?

"skoalnreds" wrote:

On Aug 11, 12:11 pm, skoalnreds wrote:
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

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

Replace every Units_Sold with A1 in the formula

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

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

"skoalnreds" wrote:

Replace every Units_Sold with A1 in the formula

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

"Ron Rosenfeld" wrote in message
...
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.

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

On Tue, 12 Aug 2008 01:39:59 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:


"Ron Rosenfeld" wrote in message
.. .
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.

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

Hopefully he'll be able to straighten things out and get something useful from
one of our attempts.
--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:

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

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.

Hopefully he'll be able to straighten things out and get something useful
from
one of our attempts.

The thing that gets me is the OP posted at least 3 different tier pricing
examples and, in each of them, his totals did not calculate correctly (or at
least they did not calculate to what I am sure they should have). I finally
got him to agree to what I am sure is the correct way to use his tier
structure and posted the formula for him (which he attempted to generalize
and missed a term in the process). So yes, hopefully he will get this all
straightened out before too long.

Rick

On Tue, 12 Aug 2008 02:58:10 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:


The thing that gets me is the OP posted at least 3 different tier pricing
examples and, in each of them, his totals did not calculate correctly (or at
least they did not calculate to what I am sure they should have). I finally
got him to agree to what I am sure is the correct way to use his tier
structure and posted the formula for him (which he attempted to generalize
and missed a term in the process). So yes, hopefully he will get this all
straightened out before too long.

Rick

Well, the VLOOKUP and table method I posted does seem to work correctly on his
various examples. On most of them, he used a straight $0.40 for each tier. And
once he has the table set up, to change the tier pricing he only needs to
change the values in column 3; to change the tiers, he only needs to change the
lowest value in each tier in column 1. And to change the number of tiers, he
only needs to extend the table.

He does have to set up the table correctly in the first place, though.
--ron

Thanks for taking the time to teach me how to drive! .. sorry for being such
a neophite.



"Ron Rosenfeld" wrote:

On Tue, 12 Aug 2008 02:58:10 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:


The thing that gets me is the OP posted at least 3 different tier pricing
examples and, in each of them, his totals did not calculate correctly (or at
least they did not calculate to what I am sure they should have). I finally
got him to agree to what I am sure is the correct way to use his tier
structure and posted the formula for him (which he attempted to generalize
and missed a term in the process). So yes, hopefully he will get this all
straightened out before too long.

Rick

Well, the VLOOKUP and table method I posted does seem to work correctly on his
various examples. On most of them, he used a straight $0.40 for each tier. And
once he has the table set up, to change the tier pricing he only needs to
change the values in column 3; to change the tiers, he only needs to change the
lowest value in each tier in column 1. And to change the number of tiers, he
only needs to extend the table.

He does have to set up the table correctly in the first place, though.
--ron

On Tue, 12 Aug 2008 07:31:01 -0700, Siper1
wrote:

Thanks for taking the time to teach me how to drive! .. sorry for being such
a neophite.


We were all neophytes at one time or another.

What did you eventually decide on?
--ron

My solution if the units sold are in A1 is the following array:

=SUMPRODUCT(--(A1{0,50000,500000}),A1-{0,50000,500000},{.5,-.1,-.05})


*** Sent via Developersdex http://www.developersdex.com ***

Well, I apologize. I mean, if your prices are still:
1-50000 = $1
50001-150000 = .75
150k+ = .5

...then the formula should read:

=SUMPRODUCT(--(A1{0,50000,500000}),A1-{0,50000,500000},{1,-.25,-.25})

Sorry for the error above. The last { } shows the differential as you go
through the tiers.
===============
"Actually, I *am* a rocket scientist."

*** Sent via Developersdex http://www.developersdex.com ***