Different commission rate after specific sales level obtained

Hi - I'm trying to create a commissions tracker spreadsheet for my company. Basic information - if the sales are below $4 million the rep gets 2% commission. Once the sales reach more than $4 million they receive 3% commission on the amount over $4 million.

I thought I created the formula to show under & over $4 million correctly, but I'm getting caught up for the month where the sales cross that threshold.

See attached example:

October: Sales $3,950,000 - calculating correctly for full amount at 2%
November: Sales $100,000 - should show $50K at 2% and $50K at 3%, but only showing $100K at 3%
December: Sales $100,000 - calculating correctly for full amount at 3%

How do I fix the formula so November would show $50K for both commission levels?

Thank you!

Hi How about using these formulas for all tabs (I inserted 1 more columns in B to reflect carried over balance from last month on line 18, and currenct balance in line 19:

C18 =IF(B18> 4000000,"",IF($E$14< 4000000,$C$14,IF((4000000-($E$14-$C$14))> 0,4000000-($E$14-$C$14),0)))
C19 = =IF(B19> 4000000,IF($E$14< 4000000,$C$14,IF((4000000-($E$14-$C$14))> 0,4000000-($E$14-$C$14),IF((4000000-($E$14-$C$14))<0,C14,0))),"")

I solved it but I can't add the formula to the reply... even with the formula brackets. I wonder why...

Put spaces before and after < > signs....

Thank you for trying.

Could you add the spreadsheet back as an attachment with the new formula? Or maybe just copy & paste without the = ?

Thank you! I'll try this. I'll need to add tabs for each of the other months, obviously, but it does get to the correct answer.

Check out cell B14 and see if this is sufficient for your purposes.

Kogen - that works! Thank you very much for your help!

Belinda200 - thank you for your response, as well.