Excel IF Statement

Question

In my Excel worksheet E7 = 159.99 and H7 = 0.00%

I am trying to use an IF statement to get the correct result which should be 5%. However, if I use it in a nested IF statement like this:

=IF(H7>=8%,20%,IF(H7>=6%,15%,IF(H7>=3%,10%,IF(H7>0%,5%,IF(AND(H7=0%,E7>0,),5%,0)))))

The result is 0%.

When I break it down to just this part: =IF(AND(H7=0,E7>0,5%) I get the desired result of 5%.

What is wrong with the way I originally did it for it to be producing 0% instead of 5%

Answer 1

In this section of your original formula, you have an extra comma in the AND function:

=IF(AND(H7=0%,E7>0,),5%,0)

The extra column is causing a third input value of nothing, which evaluates to FALSE, causing the whole AND function to return false. I.e., right now, your function is the same as:

IF(AND(H7=0%,E7>0,FALSE),5%,0)

In your broken down part, you've included 5% after the extra comma as the third argument. Any non-zero amount is treated as TRUE. So that version works because it's the same as:

IF(AND(H7=0%,E7>0,TRUE),5%,0)

You just need to remove the extra comma and third argument:

IF(AND(H7=0%,E7>0),5%,0)

So the full formula is: =IF(H7>=8%,20%,IF(H7>=6%,15%,IF(H7>=3%,10%,IF(H7>0%,5%,IF(AND(H7=0%,E7>0),5%,0)))))