Max number of logical test statements.

[wes616]

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:

=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...

 

[alexit]

Excel limit is seven.

http://www.cpearson.com/excel/nested.htm

 

[Kwan]

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

 

[iken]

Or you can use the vlookup or hlookup functions.

Very easy to set up and modify.

 

[joerd]

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

Cheers,
Joerd

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

 

[iken]

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?).

 

[electricpete]

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.

 

[electricpete]

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.

 

[joerd]

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.

 

[electricpete]

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.