calculating a cumulative graded commissions scale

[btardo01]

I am trying to prepare a worksheet that will calculate my monthly commissions, which are dependant upon a cumulative graded scale. So for the first 25,000 in dollars paid, the commissions would be at 5%. The second 10,000 in dollars paid, the commissions would be at 3.5%. For the next 15,000 in dollars paid, the commissions would be at 2.75% and all dollars after that would be at 2% commissions. So, in the below example, the first month generates 35,000 of monthly dollars, which would results in 25,000*5% + 10,000*3.5%. The second month would be 15,000*2.75% + 20,000*.2%. All other months would then be at 2%.

Another example would be if the monthly dollars paid were 20,000.....the first month's comm would be 20,000*5%. The second month's commissions would be 5,000*5% + 10,000*3.5% + 5,000*2.75%. The third month would be 10,000*2.75% + 10,000*2%. All other months would then be at the 2%.

Ideally, I would like one formula and could avoid "helper columns" but I can't figure out how to keep cumulative totals and multiple the ramainders by the appropriate %.


[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Annual Dollars paid[/TD]
[TD]420,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Monthly Dollars Paid[/TD]
[TD]Monthly Comm[/TD]
[/TR]
[TR]
[TD]Month1[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month2[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month3[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month4[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month5[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month6[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month7[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month8[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month9[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month10[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month11[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month12[/TD]
[TD]35,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Fazza]

hello. It would help if you posted the expected solutions. How about this?

=5%*MAX(25000,SUM($B$3:B4))+3.5%*MAX(0,SUM($B$3:B4)-35000)+2.75%*MAX(0,SUM($B$3:B4)-50000)+2%*MAX(0,SUM($B$3:B4)-50000)

 

[Fazza]

Re-looking at this, I see the last two terms of what I posted are basically the same. So I'm expecting that will NOT do exactly what you want. I got something wrong. Still the general idea might be OK: I've a mountain of work just arrived so won't get back to this, sorry. Please can you modify as required. regards