Conditional formulas

Both tiers can be joined in a single formula. If more than two tiers are involved, the formula gets complicated, and it's time to bring in a tax table/lookup table.

Here's a formula that calculates taxes accoring to the rules you have presented. The amounts to be taxed are in Column A.

MIN(A2,9325)×0.1+MAX(A2−9325,0)×0.15

Regards,

Barry

One way to do this kind of "tiered" or "bracket" calculation is like this:

The formula in F31, filled down, is:

=MAX(0,(MIN(9325,E31)−0))*0.1+MAX(0,(MIN(15000,E31)−9325))*0.15

This looks complicated, but it's just a repetition of the same pattern for each bracket.

If you have more brackets then you simply expand this formula by adding another + and the same pattern. The pattern for each term after the + is MAX(0,(MIN(<upper end of bracket>,E31)−<lower end of bracket>))*<rate for that bracket>. That way you can have many brackets in one easily edited formula without resorting to a lookup table.

The figures in red in the screenshot are the top end of each bracket, and green the lower end.

Here I assumed arbitrarily that 15000 was the upper end of the second bracket. You would want to replace that with the actual value.

SG