Tiered Pricing Calculations

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

This formula that you gave me earlier works greatt until I exceed

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

 

[Ron Rosenfeld]

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

 

[Siper1]

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

 

[Ron Rosenfeld]

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

 

[Jerry Beaucaire]

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})

 

[Jerry Beaucaire]

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