Sumproduct Tiered Commission Payment
Hi,
I have used sumproduct formula to calculate tiered commission payments and all works correctly, except if you exceed the threshold on the first deal.
Assuming the threshold is 1,050,000 at 70% and your first deal is 2,000,000 then the payment should be 1,400,000, however the sumproduct formula applied calculates 1,205,000. Can't figure out why?
I can't see anywhere where the amount of R1,400,000 is defined.
Does this help?
=IF(SUM(C$7:C7)>MAX($A$19:$A$23),1400000, ...
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
may be
e7=LOOKUP(D7,$A$19:$A$23,D$19:D$23)*D7-SUM(E$6:E6)
Try this and copy towards down
if not show your expected result from E7:E9
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Yet another approach: E7 and copy down:
Formula:
=INDEX($D$19:$D$23,MATCH(D7,$A$19:$A$23,1))*C7
Best Regards,
Kaper
Hi Kaper,
thank you - the index corrects the first formula, however if I then change the commission amount further tiered payments are not calculated correctly further down. See attached and result it should bring.
e7=SUMPRODUCT(($D7>$A$27:$A$31)*($D7-$A$27:$A$31)*($E$27:$E$31))-SUM(E$6:E6)
Try this and copy towards down
hi,
that's the original formula used but does not work if you exceed threshold on first deal.
See attached:
in H7
=SUMPRODUCT(($D7>=$A$19:$A$23)*($D7-$A$19:$A$23),$E$19:$E$23)
Copy down
this is CUMULATIVE commission
in I7
=H7-H6
Copy down
Last edited by JohnTopley; 04-05-2018 at 10:02 AM.
Or in E7
=SUMPRODUCT(($D7>=$A$19:$A$23)*($D7-$A$19:$A$23),$E$19:$E$23)-SUM($E$6:$E6)
Copy down
Last edited by JohnTopley; 04-05-2018 at 08:13 AM.
Try this in G6 then drag down
=SUM($C$7:$C7)*INDEX($D$19:$D$23,MATCH(SUM($C$7:$C7),$A$19:$A$23,1))-SUM(E$6:E6)
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi All,
thanks for all the inputs but have tried the various suggestions but its still not working correctly. Certain formulas correct the first row problem I initially queried but then the resultant other outcomes are incorrect, being too high based on the tier or some negative values come out.
see the attached file and show your expected result, and clarify the question if commission amount is not equal in booth the cases
So what is different in these ....
Last edited by JohnTopley; 04-06-2018 at 03:26 AM.
Hi, see attached using sumproduct formula for 2 possible scenarios comparing formula to desired result.
please check your attachment, I think It have mistakes, please recheck again
Your logic is inconsistent and does not agree with the first sample posted.
You start by producing a CUMULATIVE value in F THEN choose to have the Individual (row) contribution for the last two lines.
If you look at the file I posted in #13, your answers are a mixture of results in column H and Column I.
sorry - gremlins.
Your calculation in F18 is wrong because you have paid 60% commission on the TOTAL amount of 960703.73 rather than on the Tiering.
60% should only have paid on 10703.73 i.e the amount above the 950000 threshold.
I am trying to determine a customers usage from charges on a bill. Below is the charged rate for different amounts of units the customer can purchase on a monthly basis. Please let me know if you can suggest a formula to take charged amount(A1) to usage amount. Thank you.
0-450 units at $.1134
450-900 units at $.153
900+ at $.162825
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
@estandefer: See https://www.excelforum.com/excel-gen...ce-amount.html
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks