The goal is to calculate a tax amount with both fixed and variable components according to the following logic:
- If the amount is less than $1000, only the base tax applies.
- 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:
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 the amount in B5 is less than $1000 (F6), the test is TRUE and IF returns $100 (F5):
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):
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:
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.