where “limit” (F6), “tier1” (F4), and “tier2” (F5) are named ranges. 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: Translation:
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
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.
