Hi Bob,

The correct formula depends how the rep is paid. For example, a rep could be
paid:
A
$100/point on all points over quota
$150/point on all points over quota if the points exceed the quota by
more than 25
$150/point on all points over quota if the points exceed the quota by
more than 50
to a maximum of 75 points
, or
B
$100/point for the 1st 25% over quota, plus
$150/point for the 2nd 25% over quota, plus
$200/point for the 3rd 25% over quota

Using your data, the Sales/Quota figure is 1.522548...

Leaving aside the weeks part of the calculation, if the rep is paid on the
basis of A, the result would be:
$100*52.248 = $5,225.48 +
$50*52.248 = $2,612.74 +
$50*52.248 = $2,612.74 +
= $10,450.95
Alternatively, if the rep is paid on the basis of B, the result would be:
$100*25.00 = $2,500.00 +
$150*25.00 = $3,750.00 +
$200*2.2548 = $450.96
= $6,700.96

Introducing the weeks part of the calculation for 5 weeks gives $1,004.90
and $644.32 for A and B, respectively. Your description fits A, but your
calculation comes close to fitting B.

For A, the formula is:
=MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1)
*50,0.75*200)*100*B9/52

For B, the formula is:
=((D9/C90)*MIN((D9/C9-1),0.25)*100+(D9/C91.25)*MIN((D9/C9-1.25),0.25)*150+
(D9/C91.5)*MIN((D9/C9-1.5),0.25)*200)*100*B9/52

Add whatever rounding you need.

Cheers


"BobVA" wrote in message
...


"macropod" wrote:

Hi Bob,

Well, without knowing exactly how all your cell references relate to the
intended outcome, it's hard to say where things are going wrong. For
example, I have no idea what the '-1' in your '(D9/C9-1)' formulae that
I've
replicated is meant to achieve - all I can tell that it deducts 1 from
the
D9/C9 calculation.

A table with some sample data and expected results might help. Until
your
last post to Ron, we didn't even have data descriptions.

Cheers


"BobVA" wrote in message
...


"macropod" wrote:

Hi Bob,

It looks like I overlooked a couple of elements from your original
formulae.
Try:


=MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1)
*50,1.75*200)/(B9/52)
or


=MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1)
*50,350)/(B9/52)


Cheers


"BobVA" wrote in message
...


"macropod" wrote:

Hi Bob,

Your original post indicated you wanted the payees to get $250
per
point
for
1-25%, an additional $50 per point for 26-50% and an additional
$50
per
point for 51-75%. So, why would you change the two 50s to 150
and
200?
That
would result in the payees getting $250 per point for 1-25%, an
additional
$150 per point for 26-50% (i.e. $250 per point) and an
additional
$200
per
point for 51-75% (i.e. $450 per point).

As for capping, try:



=MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1.
75*200)
or



=MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35
0)

Cheers


"BobVA" wrote in message
...


"macropod" wrote:

Hi Bob,

You could also try:


=(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50

Cheers


"BobVA" wrote in message
...
Hello,

Almost done with this but I'm stumped on the last
remaining
formula.
The
way this compensation plan will work is if reps are
between
0-25%
growth
they
will get paid $100 per point, 26-50% $150 per point,
51-75%
$200
per
point.
Growth is calculated monthly vs. a monthly #. My problem
is
that
if
the
rep
is at 40% they get the first 25% at $100 and the next 15%
at
$150.
My
formula is an either or thing. The following are my
formulas
for
0-25%,
26-50%,51-75% respectively:

=IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52)
=IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52)
=IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52)


Any suggestions?

Thanks,
Bob




I changed the (2) 50s to 150 and 200 and the numbers are
getting
really
close.
One thing though the plan is capped at 75% growth. By the
looks
of
this
that is not accounted for, is it? Thanks for your suggestion.
Almost
there.


Hello again. Before I go on I appreciate the help. The
following
is my
original post:
Almost done with this but I'm stumped on the last remaining
formula.
The
way this compensation plan will work is if reps are between 0-25%
growth
they
will get paid $100 per point, 26-50% $150 per point, 51-75% $200
per
point.
Growth is calculated monthly vs. a monthly #. My problem is that
if
the
rep
is at 40% they get the first 25% at $100 and the next 15% at $150.
My
formula is an either or thing. The following are my formulas for
0-25%,
26-50%,51-75% respectively:

=IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52)
=IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52)
=IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52)

Not sure if that makes things any clearer. The second formula you
sent
generates an answer but it's not quit right. Not paying people
enough.
I'll
play around with it a bit more and see what I can come up with.
Thanks
again.


Hello. I think you are back where I started. I think the formula
you
just gave me pays everyone at one level instead of splitting it up among
the
different payouts. Example; based on a 5 week month someone at 52% over
plan would get $240 for the 0-25 range and $360 for the 26-50 range.
and
$38 for the 51-75 range. This formula generated $1,086. I'm assuming
like
my original formula it is paying everyone at the highest teer only.


Hello again. You guys are troopers! The -1 in the original formula was
to pay folks on .25, .5, or .75. I.E. using the numbers that follow the
answer is 1.52 so I subtracted 1 to get to .52 and then run the calculation.
Not sure how much data you want but the "real" numbers I am using is 5 for
B9, 53,731 for C9, and 81,808 for D9. The "correct" answer using a
calculator would be $638, $240 for the 0-25 range and $360 for the 26-50
range, and $38 for the 51-75 range.