Net Commission based on 3 Tiers with Commission Cap

Hi everyone,

I am on the final stages of building my commission worksheet and I would like to thank etaf and Ace_XL for helping me out so far. I am running into one issue that I can't get past; hopefully this forum can help me complete it!

Attached is my current worksheet. The purpose of this worksheet is to take the Gross Commission for each sale and spread it across the commission tiers. When the listing is a referral (Yes in column K), the amount of Net Commission that applies to the tier is capped at $3,000 total (between this sale and all previous referrals). If it is not a referral, it follows the normal schedule.

Column H contains the main formula which determines what commission % should be applied. It takes into account the commissions that spread two tiers. Super long formula but follows the same structure. My issue is incorporating the cap into this formula. I have a cap formula in column L, but I cannot use it in column H because it is a circular reference.

If anyone can give me a hand I would be very grateful.

Thanks in advance,

Mike