# Solved: Excel 2007 - Nested "IF" formula using "OR" Function

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### mamcelr

I have a nested "IF" statement that inlcudes the "OR" function because I have more than 7 logical tests. One of the logical tests looks for two fields to be true in order to return the "value if true". If either or both fields are false, then the "value if false" is returned, in this case zero. When the value of the first field is correct, but the value of the second field is not correct, zero is returned, which is the "value if false" and a correct return.

However, when both fields are false, the formula is returning the word "FALSE" instead of zero.

The attached file shows these returns and the formulas. Cells F2-7 are corrrect returns (highlighted in green), while cells F8-11 are not correct returns (highlighted in red).

Any ideas on how to return the "value if false" (zero) in cells F8-11 would be appreciated.

#### Attachments

• 50.7 KB Views: 140

#### Garf13LD

Just insert a coma before the last bracket.
In fact, you don't need the first IF to establish the 2nd IF.

#### Attachments

• 39.9 KB Views: 105

#### mamcelr

Wow . . . I guess I really am a rookie at this. Thanks for the quick reply. I like the new formula, but I'm not sure what it is? It's not an "IF" formula. What should I research to learn more about this syntax? Are there any limits to the number of logical tests?

Thanks again, I'll mark this post resolved!

#### Zack Barresse

Hi there!

In 2003 you are limited to 7 nested IF() functions, while in 2007 and later you have a limit of 64 nests.

You can get away with only one, and probably a much faster formula (of course there are a hundred ways to skin the cat)...

Code:
``=IF(OR(ISNUMBER((B2=65)*MATCH(C2,{710,720,730},0)),ISNUMBER(MATCH(B2,{21,22,23,51,52,53},0))),D2,0)``
HTH

#### mamcelr

Zack . . . thank you. This works too. I really appreciate the input!

#### Garf13LD

AND can be substituted by *
OR can be substituted by +

e.g.
AND(1=1,2=2) = (1=1)*(2=2)
OR (1=1,2=1) = (1=1)+(2=1)

So all your test conditions can be represented by 1 or 0.
1 = TRUE, 0 = FALSE
Any value *1 = value, any value *0 = 0.

Also if there is no need to nest your conditions, keep them separate.
This way you can have more than 7 conditions.
e.g.
=if(a1=1,if(b1=2,2,0),if(a1=2,if(b1=3,3,0)))
=if(a1=1,if(b1=2,2,0),0)+if(a1=2,if(b1=3,3,0),0)

As you can see, you don't need to test if a1=1 before you test if a1=2.
Since a1=2 will never be =1, these 2 conditions can be split into 2 allowing for more layers.
I have "nested" 13 conditions using this method.

One last thing, minimise the use of brackets.
if((a1=b1),1,0) can be just if(a1=b1,1,0)
this is to reduce confusion when your formula get too long.

#### mamcelr

Garf13ld . . . thank you again. This is great to know. Appreciate the follow-up!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.