To calculate a sales tax with two tiers (brackets), you can use a basic IF formula. In the example shown, the formula in C5, copied down, is:


where "limit" (F6), "tier1" (F4), and "tier2" (F5) are named ranges.

Generic formula



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


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:


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:



  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:


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

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.