## Summary

This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components. In the example shown, the formula in C5 is:

``````=IF(B5<limit,base,base+(B5-limit)*rate)
``````

Where rate (F4), base (F5), and limit (F6) are named ranges. As the formula is copied down, it calculates a tax for each amount shown in column B. If the amount is below \$1000, only the base tax applies. If the amount is greater than or equal to \$1000, the formula returns the base tax plus a 15% tax on the amount greater than \$1000.

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

``=IF(B5<limit,base,base+(B5-limit)*rate)``

## Explanation

The goal is to calculate a tax amount with both fixed and variable components according to the following logic:

1. If the amount is less than \$1000, only the base tax applies.
2. If the amount is \$1000 or greater, the result is the base tax + 15% * the amount over \$1000

This problem can be easily solved with the IF function. The formula in C5 is:

``````=IF(B5<limit,base,base+(B5-limit)*rate)
``````

Where rate (F4), base (F5), and limit (F6) are named ranges. The logical test inside the IF function checks if the amount in column B is less than the limit entered in cell F6:

``````=IF(B5<limit,
``````

If the amount in B5 is less than \$1000 (F6), the test is TRUE and IF returns \$100 (F5):

``````=IF(B5<limit,base,
``````

If the amount in B5 is not less than \$1000, the test is FALSE, and IF returns \$100 (F6) plus 15% (F4) of the amount over \$1000 (F5):

``````base+(B5-limit)*rate
``````

As the formula is copied down, the formula calculates a tax for each amount in column B.

### Without named ranges

If you prefer not to use named ranges you can use absolute references instead like this:

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

Note the B5 is still a relative reference because we want that reference to change as the formula is copied down. However, the other references are absolute because they should not change as the formula is copied.

Author

### 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.