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

Thread Starter
Joined
Oct 24, 2011
Messages
39
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

Joined
Apr 17, 2012
Messages
455
Just insert a coma before the last bracket.
In fact, you don't need the first IF to establish the 2nd IF.
 

Attachments

mamcelr

Thread Starter
Joined
Oct 24, 2011
Messages
39
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!
 
Joined
Jul 25, 2004
Messages
5,458
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

Thread Starter
Joined
Oct 24, 2011
Messages
39
Zack . . . thank you. This works too. I really appreciate the input!
 
Joined
Apr 17, 2012
Messages
455
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

Thread Starter
Joined
Oct 24, 2011
Messages
39
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top