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


where limit (F6), rate1 (F4), and rate2 (F5) are named ranges. As the formula is copied down, it calculates a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts over 20,000.

Note: The named ranges are optional and for readability and convenience only. See below for a version of the formula that uses absolute references instead.

Generic formula



The goal is to calculate a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts of 20,000 or greater. This problem illustrates how to use the IF function to return different calculations. At the core, this formula uses a single IF function. The logical test is based on this expression:


When B5 (the current amount) is less than the limit in cell F6 (20,000), the test returns TRUE and the IF function applies rate1 only:


When B5 is greater than 20,000, the test returns FALSE and the IF function applies rate1 and rate2:



  1. Calculate rate1 by multiplying the limit (20,000) by 6% (F4).
  2. Calculate rate2 by subtracting the limit from the amount, and multiplying the result by 10% (F5)
  3. Add #1 and #2 together

Without named ranges

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


Tax rate calculation with two rates with absolute references

References to F4, F5, and F5 are locked to prevent changes when the formula is copied down the table. Notice B5 is still a relative reference because we want the reference to change as the formula is copied.

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.