BobVA

Using your latest sample data of:
B9: 5
C9: 53,731
D9: 81,808

And...this lookup table in A1:B5
A1: -1000% B1: 0
A2: 0% B2: 100
A3: 25% B3: 50
A4: 50% B4: 50

This formula resolves to $639:
E9:
=INT(SUMPRODUCT(((D9-C9)/C9=$A$1:$A$4)*INT(((MIN((D9-C9)/C9,75%)-A1:A4)*100))*(B1:B4))*B9/52)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"BobVA" wrote:



"Ron Coderre" wrote:

I understand your point...
I tried to emulate your posted formula, but I didn't have much to go on.

I'm guessing that C9 is some kind of volume? hundreds? millions? units?
Is C9 the previous amount and D9 the new amount?
What does B9 represent?

***********
Regards,
Ron

XL2002, WinXP


"BobVA" wrote:



"Ron Coderre" wrote:

Bob:

See if this helps

Build this table in A1:B4

Increase Incremental Rate
0% 100
25% 50
50% 50

Then, with values in B9, C9, and D9
E9:
=SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52)

Note: in case text wrap impacts the display, there are no spaces in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"BobVA" wrote:

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

Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94.

Hello,

D9 represents actual sales that month, C9 is Quota for the month. B9
represents the number of weeks in that given month. Not all our months have
the same number of weeks. THanks again.