Limitation of Nesting of Function
 

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

Limitation of Nesting of Function
 

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

Limitation of Nesting of Function
 

Thanx JMay,

Can u pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d",""))))

"JMay" wrote:

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

Limitation of Nesting of Function
 

Sorry, late for work here -- maybe someone else can jump in...


"FARAZ QURESHI" wrote:

Thanx JMay,

Can u pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d",""))))

"JMay" wrote:

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?