using SUMPRODUCT() for commissions
Don't you just need to subtract the commission amounts due on successive
totals?
E.g. to get commission due on invice amount in L15 you'd need to subtract
the commission due on M14 from that due on M15
"Amanda" wrote:
All,
I have been trying to use SUMPRODUCT to work out commissions due on each
invoice total (in column L). The commission due on each invoice is worked
out on percentages based on a running total (in column M)
Commission Schedule
0-25k 7%
25k - 50k 8%
50k - 75k 9%
75k - 150k 10%
150k - 250k 11%
250k - 500k 12%
500k+ 15%
Column L Column M
Due Cumulative
1,171.15 ぎ 1,171.15
0.00 ぎ 1,171.15
0.00 ぎ 1,171.15
51,031.32 ぎ 52,202.47
5,188.27 ぎ 57,390.74
4,428.05 ぎ 61,818.79
1,894.26 ぎ 63,713.05
10,380.53 ぎ 74,093.58
3,066.75 ぎ 77,160.33
91,605.18 ぎ 168,765.51
74,726.05 ぎ 243,491.56
10,284.10 ぎ 253,775.66
5,298.70 ぎ 259,074.36
My problem is that my final answer works out the percentages correctly but
takes it from the cumalitive total and not the invoice amount.
Please can anyone help?
Many thanks Elaine
|