Exceljet

Quick, clean, and to the point

Two-tier sales tax calculation

Excel formula: Two-tier sales tax calculation
Generic formula 
=IF(A1<=limit,A1*tier1,limit*tier1+(A1-limit)*tier2)
Explanation 

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:

=IF(B5<=limit,B5*tier1,limit*tier1+(B5-limit)*tier2)

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

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.