Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

Thanks in advance.

share|improve this question

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.


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


share|improve this answer
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 – barry houdini Oct 3 '13 at 11:07
up vote 0 down vote accepted

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:

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .