# Calculate multi-rate tax in excel

I am trying to calculate the income tax in excel using a single formula for a multi-rate tax system. At the moment, the formula is referencing the cell in the row above to get the income on which to calculate the rate.

I only have a problem with the highest bracket part of the formula:

``````IF(ADDRESS(ROW()-1,COLUMN())>180000,(ADDRESS(ROW()-1,COLUMN())-180000)*0.45, 0)
``````

This formula is returning `#VALUE` when the cell above it contains the income tax number (use 100,000 as example).

So for this bracket, any value above 180,000 is taxed at income - 180,000 * 0.45

How do I fix this formula to return `0` for 100,000?

-

I'm not sure you need to make this so complicated. If your formula is in `A2` then can you not just refer directly in the formula to the cell above, e.g.

`=MAX(0,A1-180000)*0.45`

If you copy that to another cell it will still refer to the cell above. If you are concerned about rows/columns being deleted and you want to ensure that it still refers to the cell above, whatever then use `OFFSET` like this, again in A2

`=MAX(0,OFFSET(A2,-1,0)-180000)*0.45`

-
Yes, but if the OP wants to calculate something similar to the U.S. income tax, it may be best to use nested `IF`’s; e.g., `=IF(A1>high_val, formula3, IF(A1>medium_val, formula2, IF(A1>low_val, formula1, 0)))`. –  Scott Oct 1 '13 at 2:14
What @Scott said. We need to use the IF function, otherwise a +1 for suggesting just using direct cell reference. my only issue is that the formula was originally written in a separate cell and I was trying to replace the direct reference with the address using the `SUBSTITUTE` function –  Sinker Oct 3 '13 at 2:03
OK, I see .......but there are other alternatives to IF functions that might lead to shorter formulas - see this link for another way to calculate variable rates -mcgimpsey.com/excel/variablerate.html –  barry houdini Oct 3 '13 at 11:07

I worked it out. The formula is not doing what it's purported to do.

To make this formula work, I need to enclose the `ADDRESS()` sections in `INDIRECT()`.

It should then look like this:

``````IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))>180000,(INDIRECT(ADDRESS(ROW()-1,COLUMN()))-180000)*0.45, 0)
``````
-