Stair-step commissions question
 

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,I F(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%
$138,000(<=100%): $172,500(100.01<=125%): $207,000 (125.01<=150%)
=150%

Stair-step commissions question
 

This page of J.E. McGimpsey should show you how to do what you want:

http://www.mcgimpsey.com/excel/variablerate.html

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Serg" wrote in message
oups.com...
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,I F(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%
$138,000(<=100%): $172,500(100.01<=125%): $207,000 (125.01<=150%)
=150%

Stair-step commissions question
 

It is not necessary to post the same question multiple times.

"Serg" wrote:

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,I F(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%
$138,000(<=100%): $172,500(100.01<=125%): $207,000 (125.01<=150%)
=150%