"Stair-Stepped" Commissions Question.

[sergio.mercado]

I am to begin calculating rep commissions in the coming months based
on a "tiered" or "step" method. The rep commissions will be based on
YTD sales and their commissions % will increase as they surpasse four
different "steps"--0%-100%, 100.01-125%, 125.01-150% & >150% of YTD
sales. I have attempted multiple variations using the IF formula and
can't get this thing to work out how I want it, specifically the
problem I am having is that I cannot calculate commissions for someone
who makes their first goal ($0-$138,000) at that % (20%), and then
calculate future sales at the next step ($138,00.01-206,999.99 @ 25%)
and so on without calculating a total YTD figure at one commissions
%. Has anyone encountered a similar problem and is their an easy
formula I can use to calculate this? Here is the copy of the formula
I was using:

=IF(U2<V2,U2*AA2,IF(U2<W2,U2*AB2,IF(U2<X2,U2*AC2,IF(U2<Y2,U2*AD2))))
where
U2=YTD Sales
V2=$138,000
W2=$172,500
X2=$207,000
Y2=$207,014
AA2=30%
AB=25%
AC=27%
AD=30%

Here is an example of a few columns:

1st goal @ 20% 2nd goal @ 25% 3rd goal @
27% 4th goal @30%

 

[JE McGimpsey]

See answers in other groups.

Please don't post the same question to multiple groups. It tends to
fragment your answers, and potentially wastes the time of those
answering questions that have already been answered.

 

[Guest]

perhaps this will help
http://mcgimpsey.com/excel/variablerate.html