Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers 2

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

share|improve this answer
1  
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
1  
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
add comment
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:

IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))>180000,(INDIRECT(ADDRESS(ROW()-1,COLUMN()))-180000)*0.45, 0)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.