Two-tier sales tax calculation

At the core, this formula relies on a single IF function. The logical test is based on this expression:

B5<=limit

When B5 (the current amount) is less than the limit (10,000), the test returns TRUE and the IF function calculates a tier 1 tax only, and returns a final result with this:

B5*tier1

However, when the amount is greater than the limit (10,000), the logical test returns TRUE. The IF function then runs an expression to calculate tax for both tier 1 and tier 2 taxes:

limit*tier1+(B5-limit)*tier2

Translation:

1. Calculate tier 1 tax by multiplying the limit (10,000) by the tier 1 tax (6%).
2. Calculate tier 2 tax by subtracting the limit from the amount, and multiplying the result by the tier 2 tax rate (10%).
3. Add tier 1 tax to tier 2 tax and return a final result.

Without named ranges

Named ranges can make formulas easier to write and read. The same formula without named ranges looks like this:

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

References to limit, tier1, and tier2 are locked to prevent changes when the formula is copied down the table.