Stair-step commissions question

S

Serg

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%
 

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

Top