Tiered Pricing Calculations

  • Thread starter Thread starter Siper1
  • Start date Start date
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
Click to expand...

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

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

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

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})
 
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."
 
Back
Top