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:
I am trying to add cells I41, J41, K41 and L 41 to this statement. Any Ideas, or see what I'm doing wrong
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.
http://www.cpearson.com/excel/nested.htm
RE: Max number of logical test statements.
Here is the link to the previous thread:
http://www
RE: Max number of logical test statements.
Very easy to set up and modify.
RE: Max number of logical test statements.
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.
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.
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.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Max number of logical test statements.
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.
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.