Forum Discussion
Solved
Calculating Commissions with escalator clause
Hi, I am trying to calculate commissions with an escalator clause and break it out by month. In column A I have the date the deal closed. In column E I have the amount of the deal, with E29 totaling ...
See the attached workbook.
Not sure if the above is clear - my goal is to have each month show my expected commission keeping in mind the commission structure
Does the escalator start over each month? Or only at the beginning of the year?
- Hi,
the escalator starts over at the start of the new Fiscal year, which is April. Capturing that piece of that isnt essential - I can always make another box of months to capture if needed. Thank you,See the attached workbook.
- Thank you! I altered it slightly to =SUMIFS($E$3:$E$23,$A$3:$A$23,">="&DATE(2023,1,1),A3:A23,"<="&DATE(2023,1,31)) for the cumulative for only the month in question and then commission =SUMPRODUCT((B29>$F29:$F$29)*(B29-$F$28:$F$29),$H$28:$H$29)
thank you so much!
- the best I see is do a cumulative total and subtract prior amounts. BTW you mention 0.06 and 0.08 as the increased amount. I will assume 0.06 is correct. So cumulative total is:
=SUM( all months) * 0.04 + MAX( 0, SUM( all months) - 100000) * 0.02 - SUM( previous months )
OR
=SUM( all months) * 0.06 - MIN( 100000, SUM( all months) ) * 0.02 - SUM( previous months )
so "all months" would be like $E$29:$E$41 and if this is in column F then "previous months would be like $F$28:$F28 (note the $s) so when you copy down the col F range expands to always include down to 1 cell above present cell (and this assumes cell F28 is a non-value)
