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.