Forum Discussion
SUMPRODUCT - Tiered Commissions - Two Categories of Commissions
16000/0.3=53333.33*.3 + 32666.67*.2
Where do all these numbers come from?? How do they relate to the data in that row?
Gross Commission Company Share of Commission Manually Calculated Cumulative
85,000.00 22,533.33 96,533.33
Hi,
Thank you for your reply.
Yes.
When I see that the next commission will bring Cumulative Company commissions above the next tier, in this case, $90,000, and because I do not have a formula to help me calculate the commission, I must do it manually.
In this case, 16000/0.3=53333.33*.3 + 32666.67*.2 ...
I see from the chart that Cumulative Company Commissions have already reached $74,000, so another $16,000 will bring Cumulative Company Commissions to the next tier, $90,000. I can calculate how much of the current commission of $85,000 is used up at the below $90,000 (1st) tier by dividing $16,000 by the percentage for the 1st tier, 30%, which equals $53,333,33. Therefore, the remaining commission to be split between the company and the salesperson at the 2nd tier is $31,666.67 ($32,666.67 was a miscalc). The 2nd tier rate is 20%, and 20% of 31,666.67 equals $6,333.33. Adding together the 1st tier Company share of $16,000 to the 2nd tier Company share of $6,333.33 gives a total Company share of $22,333.33 (not 22,533.33 in the original post... my miscalc).
I hope this is the logic you are looking for.
The tiers will only crossover twice per year, once at $90,000 and once at $150,000. The fact there are two categories in addition to two tiers really trips me up. I've tried using "if" statements and it works up to the first tier, but when it gets to the second tier the if statements get too complex for me.
Thanks,
Michael
