×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Max number of logical test statements.

Max number of logical test statements.

Max number of logical test statements.

(OP)
I am trying to set up a string of logical test statements to read a number of cell, and depending on which cell has a value in it, return another value. When I do this, it works for 7 Statements, then fails. Has anyone had this issue before?

Here is my Statement:

Quote:


=IF(ISBLANK(B41),IF(ISBLANK(C41),IF(ISBLANK(D41),IF(ISBLANK(E41),IF(ISBLANK(F41),IF(ISBLANK(G41), IF(ISBLANK(H41),  " -- ",H41),G41),F41),E41),D41),C41),B41)

I am trying to add cells I41, J41, K41 and L 41 to this statement. Any Ideas, or see what I'm doing wrong

Wes C.
------------------------------
When they broke open molecules, they found they were only stuffed with atoms. But when they broke open atoms, they found them stuffed with explosions...

RE: Max number of logical test statements.

There is a previous thread that will address this for you.  Excel limits the number of if statements to seven.  I use visual basic if I have more than a couple.  VB makes it easier for me to follow the logic anyway.

Here is the link to the previous thread:
http://www.eng-tips.com/viewthread.cfm?qid=133731&page=5

RE: Max number of logical test statements.

Or you can use the vlookup or hlookup functions.

Very easy to set up and modify.

RE: Max number of logical test statements.

So comes to your rescue Office 12 (to be released), which increases the nested if statements to 64 levels! purpleface

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Max number of logical test statements.

Why would anyone want to nest 64 statements using the "if" ufnction, when all can be done using the simple "vlookup" and a table from which to reference.

This way any future changes are easily made, and anyone else looking at the working of the workbook, can see with ease what you are trying to achieve (Have you tried to follow someone's logic in a function using seven nested if functions without getting lost?).

RE: Max number of logical test statements.

I agree vba is the way to go.

To flesh out the lookup idea.

You're looking for the first non-blank cell in the range B41 to H41

Put in B40 =+ISBLANK(B41) and copy it accross row 40

The lookup function to find first non-zero entry in B41:H41 would be

=HLOOKUP(FALSE,B40:H41,2,FALSE)

In the original problem, it was a requirement that if all of the cells are blank you need to return "--".  Hlookup returns NA if it doesn't find the target (FALSE), so the following equation with ISNA (to test for all blank) will give the desired output (although there may be easier ways)
=+IF(ISNA(HLOOKUP(FALSE,B40:H41,2,FALSE)),"--",HLOOKUP(FALSE,B40:H41,2,FALSE))

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Max number of logical test statements.

The extra code IF(ISNA etc is not necessary if you are happy to use #NA as your indicator of no data found rather than "--"

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Max number of logical test statements.

You're quite right, iken. I must have only read the subject line of the post. I have newer even tried to use 7 nested if's, let alone 64.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Max number of logical test statements.

I was browsing excel sites and saw a page devoted to ways to get around the 7 nested if's limit
http://www.j-walk.com/ss/excel/usertips/tip080.htm


They of course talk about lookup and vba as we discussed here.  They also mention:
* Using defined names
* using concatenate which allows up to 30 levels.
* Using boolean multiplication/addition

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources