Tiered Monthly Commission on YTD Sales Formula

Hi All,

I just joined the forum and appreciate any help you might be able to offer! We pay salespeople monthly on a tiered commission structure as a percentage of total sales YTD. I tried to solve with the appropriate IF statement but it doesn't prorate correctly for the month that the salesperson exceeds the commission tier. In my attached example the salesperson's sales exceed the tier of $60,000 in August and the formula incorrectly calculates all of that months commission at the higher 45% rather than prorating 4,000 at 40% and 4,000 at 45%, resulting in "monthly pay" of $3,600 rather than the correct $3,400. Any suggestions on how to correct?

The IF statement I used: =IF(I9<$F$2,I8*$G$2,(I8*$G$3))

Excel_Commission_SNIP.jpg

Thanks!
Chris