Example of Endresult

I am working on a Google Sheets formula to distribute a commission across different tiers or levels. I need to allocate the resulting commission amount across four tiers with specific limits:

•   Tier 1: 0 - 100,000 EUR
•   Tier 2: 100,000 - 150,000 EUR (max 50,000 EUR)
•   Tier 3: 150,000 - 200,000 EUR (max 50,000 EUR)
•   Tier 4: 200,000 EUR and above

The idea is to fill each tier sequentially. For example, if the commission is 170,000 EUR, Tier 1 would take 100,000 EUR, Tier 2 would take 50,000 EUR, Tier 3 would take 20,000 EUR, and Tier 4 would be empty.

**What I’ve Tried:
**
I’ve attempted to use ARRAYFORMULA with MIN, MAX, and basic arithmetic operations to distribute the commission across these tiers. Here are the approaches I’ve tried:

1.  Basic allocation formula for each tier:
•   Tier 1: ARRAYFORMULA(MIN(100000; A2:A))
•   Tier 2: ARRAYFORMULA(MIN(50000; MAX(0; A2:A - 100000)))
•   Tier 3: ARRAYFORMULA(MIN(50000; MAX(0; A2:A - 150000)))
•   Tier 4: ARRAYFORMULA(MAX(0; A2:A - 200000))
2.  Adjusting the logic to ensure that the tiers are filled sequentially and correctly capped at their limits.

**I also tried this formula:
**

•   Tier 1: ARRAYFORMULA(MIN(100000; MAX(0; A2:A)))
•   Tier 2: ARRAYFORMULA(MIN(50000, MAX(0, A2:A - B2:B)))
•   Tier 3: ARRAYFORMULA(MIN(50000, MAX(0, A2:A - B2:B - C2:C)))
•   Tier 4: 200,000 EUR and above

However, the formulas either don’t distribute the amounts correctly across the tiers or end up not populating any values at all in the cells.

With the formulas in all column C:D in the first row I had an empty field or 50.000 € in it. The Arrayformula also didn’t work for the next rows. The first row was the one that worked.