using SUMPRODUCT() for commissions

[Amanda]

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

 

[daddylonglegs]

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

 

[Bob Phillips]

Try this

=ROUND(L2*LOOKUP(L2,{0,25000,50000,75000,150000,250},{0.07,0.08,0.09,0.1,0.1
1,0.12,0.15}),2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[joeu2004]

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%

Typically, such commission schedules mean: 7% on the first 25k, 8% on
the next 25k, etc. So if the sale were 30k, the commission would be
2150 (7%*25k plus 8%*5k). Is that what you mean?

If so, then for a SUMPRODUCT solution, you might look at
http://www.mcgimpsey.com/excel/variablerate.html .

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
[.... etc ....]
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?

It would be a lot easier to help you if you posted your formula(s). If
the table contains the sale price in A1 and cumulative sales in A2, and
your formula is fundamentally correct, as you claim, but it is based on
cumulative sales, simply change the reference from A2 to A1 in the
formula.

 

[joeu2004]

Errata....

Column L Column M
Due Cumulative

[....]
If the table contains the sale price in A1 and cumulative sales in A2

First, I meant A and __B__. Second, that should have been L and M,
since you identify those as the columns.

 

[Amanda]

Thanks Bob

I gave this a try, the formula is great but the commissions percentages are
being worked out on the invoice amount, the percentage depends on the
cumulative total.

E.g My first invoice was for 1,171.15 so the cumalitive total starts at
1,171.15 so the commission is 7%. (all is ok so far)

Commission Schedule based on Cumalative Total
0-25k 7%
25k - 50k 8%
50k - 75k 9%
75k - 150k 10%
150k - 250k 11%
250k - 500k 12%
500k+ 15%
However my second invoice is for 51,031.32. This now takes the cumulative
total to 52,202.47. I need the commision to be 23,828.85 at 7% (amount under
25k in Cumulative) 25k at 8% (25-50k), and 2,202.47 at 9%. (50k-75k)

My 3rd invoice is for 5,188.27,which now takes the cumalitive total to
57,390.74 so I need to take inv amount 5188.27 * 9% as cummalitive total is
50-75k at 9%

Hope you can help again
Regards

 

[Amanda]

Thanks Joeu

The website you gave me is very useful. My problem is that the commission
percentages depend on the cumulative total.

If the cumulative total was at 74,000 and the next inv is for 2,000. The
commission on this invoice needs to be worked out on 1,000 at 9% and 1,000
at 10%.

Hope you can be of some more help

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%

Typically, such commission schedules mean: 7% on the first 25k, 8% on
the next 25k, etc. So if the sale were 30k, the commission would be
2150 (7%*25k plus 8%*5k). Is that what you mean?

If so, then for a SUMPRODUCT solution, you might look at
http://www.mcgimpsey.com/excel/variablerate.html .

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
[.... etc ....]
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?

It would be a lot easier to help you if you posted your formula(s). If
the table contains the sale price in A1 and cumulative sales in A2, and
your formula is fundamentally correct, as you claim, but it is based on
cumulative sales, simply change the reference from A2 to A1 in the
formula.

 

[JE McGimpsey]

One way would be to have a cell that contains the Total amount invoiced,
and one that contains the commissions paid to date. Then you could use:

=ROUND(SUMPRODUCT(--(TotalInvoiced>{0,25000,50000,75000,150000,250000,
500000}), (TotalInvoiced-{0,25000,50000,75000,150000,250000,500000}),
{0.07,0.01,0.01,0.01,0.01,0.01,0.03}),2) - PreviousCommission

Another way would be to use the Current invoice and retain the total
Previous-ly invoiced to give something like:

=ROUND(SUMPRODUCT(--((Current+Previous)>{0,25000,50000,75000,150000,
250000,500000}),((Current+Previous)-{0,25000,50000,75000,150000,
250000,500000}),{0.07,0.01,0.01,0.01,0.01,0.01,0.03}),2) -
ROUND(SUMPRODUCT(--(Previous>{0,25000,50000,75000,150000,250000,500000}),
(Previous-{0,25000,50000,75000,150000,250000,500000}),{0.07,0.01,0.01,
0.01,0.01,0.01,0.03}),2)

Other combinations apply, of course.

if you put your commission schedule in a table:

J K
1 0 7%
2 25000 1%
3 50000 1%
4 75000 1%
5 150000 1%
6 250000 1%
7 500000 3%

The first formula reduces to:

=ROUND(SUMPRODUCT(--(TotalInvoiced>J1:J7),(TotalInvoiced-J1:J7),
K1:K7),2) - PreviousCommission

and the second to:

=ROUND(SUMPRODUCT(--((Current+Previous)>J1:J7),((Current+Previous)-J1:J7)
,K1:K7),2) - ROUND(SUMPRODUCT(--((Previous)>J1:J7), ((Previous)-J1:J7)
,K1:K7),2)

Thanks Joeu

The website you gave me is very useful. My problem is that the commission
percentages depend on the cumulative total.

If the cumulative total was at 74,000 and the next inv is for 2,000. The
commission on this invoice needs to be worked out on 1,000 at 9% and 1,000
at 10%.

Hope you can be of some more help

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%

Typically, such commission schedules mean: 7% on the first 25k, 8% on
the next 25k, etc. So if the sale were 30k, the commission would be
2150 (7%*25k plus 8%*5k). Is that what you mean?

If so, then for a SUMPRODUCT solution, you might look at
http://www.mcgimpsey.com/excel/variablerate.html .

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
[.... etc ....]
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?

It would be a lot easier to help you if you posted your formula(s). If
the table contains the sale price in A1 and cumulative sales in A2, and
your formula is fundamentally correct, as you claim, but it is based on
cumulative sales, simply change the reference from A2 to A1 in the
formula.