Finance
Work out tax with brackets
=SUMPRODUCT((A2>$D$2:$D$4)*(A2-$D$2:$D$4)*$E$2:$E$4)How it works in Excel
- D2:D4 holds where each bracket starts.
- E2:E4 holds how much the rate rises at each bracket.
- Each bracket charges its extra rate on income above its start.
- Column E is the step up, not the rate itself. For rates of 10%, 20% and 30%, enter 10%, 10% and 10%.
- The same layout works for tiered commissions.
The idea in general
Only the income inside a bracket is charged that bracket's rate.
What I assumed
- The income is in A2, and the bracket table is in D2:E4.
Check the result on your own data before relying on it.