=IF(amt<limit,amt*tax_rate,(amt-limit)*tax_rate+base_tax)

Related formulas

Basic Tax Rate calculation with VLOOKUP

This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components.

In the example shown, the formula in C5 is:

=IF(B5<$F$6,B5*$F$4,(B5-$F$6)*$F$4+$F$5)

If amount is less than $1000, tax is amount * 20%. If amount is greater than or equal to $1000, tax is $200 + 20% of amount minus 1000.

The core of this formula is a single IF statement that checks the amount in column B against the base limit:

=IF(B5<$F$6

If TRUE, the formula simply multiplies amount by tax rate:

B5*$F$4

If FALSE, the formula applies the tax rate to the difference between the amount and the limit, then adds the base tax:

(B5-$F$6)*$F$4+$F$5

=IF(amt<limit,amt*tax_rate,(amt-limit)*tax_rate+base_tax)