Solved I can't get this nested if statement to work Excel 2007

August 30, 2012 at 04:26:30
Specs: Macintosh
What is wrong with my nested if statement?
=IF(A8<30000,((A8-30000)*.0%),(IF(30000<A8>90000,(A8-30000)*0.5%,((IF(90000<A8>150000,(A8-90000)*1%+300,((IF(150000<A8>200000,(A8-150000)*2%)+300+600))))))))

Cumulative Sliding Tax Rate is calculate based on value of house.
from 0 to 30000 no tax
from 30000 to 90000 the house value is taxed at .5%
from 90000 to 150000 the house is taxed at 1% plus the tax of the 30k to 90k portion
from 150000 to 200000+ the house is taxed at 2% plus the 30k to 90k portion and plus the 90k to 150K portion


See More: I cant get this nested if statement to work Excel 2007

Report •

#1
August 30, 2012 at 05:45:56
✔ Best Answer
This is not the correct syntax for checking 2 conditions:

IF(30000<A8>90000)

You have to check each condition by itself and logically AND them:

IF(AND(A8>30000,A8<90000)

BTW, what if the value is exactly 30000 or 90000?

You should account for that by using operators such as >= and <=.

In your case (even though the syntax is wrong) you are using the same values twice for different conditions:

30000<A8>90000
90000<A8>150000

Once you get the syntax correct, you will find that Excel is going to stop at the first TRUE condition and ignore everything after it. Excel does not check every condition and then decide which one is "more TRUE". It stops and retuns a value as soon as it finds a TRUE condition as it evaluates the formula from left to right.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 30, 2012 at 06:08:35
How would i do this? Would a table work?

Report •

#3
August 30, 2012 at 06:24:42
I told you how to do it.

You asked what was wrong with your Nested IF and I pointed out the syntax errors.

If you use the correct syntax for your Nested IF, it should work fine.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question