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 %.

Annual Dollars paid	420,000
	Monthly Dollars Paid	Monthly Comm
Month1	35,000
Month2	35,000
Month3	35,000
Month4	35,000
Month5	35,000
Month6	35,000
Month7	35,000
Month8	35,000
Month9	35,000
Month10	35,000
Month11	35,000
Month12	35,000

<TBODY>
</TBODY>

 

[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