|
using SUMPRODUCT() for commissions
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 |
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 |
using SUMPRODUCT() for commissions
Try this
=ROUND(L2*LOOKUP(L2,{0,25000,50000,75000,150000,25 0},{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) "Amanda" wrote in message ... 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 |
using SUMPRODUCT() for commissions
Amanda wrote:
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. |
using SUMPRODUCT() for commissions
Errata....
I wrote: Amanda wrote: 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. |
using SUMPRODUCT() for commissions
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 "Bob Phillips" wrote: Try this =ROUND(L2*LOOKUP(L2,{0,25000,50000,75000,150000,25 0},{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) "Amanda" wrote in message ... 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 |
using SUMPRODUCT() for commissions
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 " wrote: Amanda wrote: 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. |
using SUMPRODUCT() for commissions
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.0 3}),2) - ROUND(SUMPRODUCT(--(Previous{0,25000,50000,75000,150000,250000,50000 0}), (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(--(TotalInvoicedJ1: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) In article , Amanda wrote: 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 " wrote: Amanda wrote: 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. |
| All times are GMT +1. The time now is 11:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com