8th Len() fails in nested ifs??

[Bob Kilmer]

I want a worksheet cell to obtain the result that the formula below
implies. Trouble is, when entering the formula, Excel 2002 complains
with the message "The formula you typed contains an error. For
information about fixing common formula problems, click Help", etc., and
hightlights the 8th Len. If I replace that Len() statement with 'True'
or '2 > 1', for instance, Excel is happy. Question is: why does this
fail and how might I obtain the result I seek?

=
IF(Len(A1)>1, "str1",
IF(Len(A2)>1, "str2",
IF(Len(A3)>1, "str3",
IF(Len(A4)>1, "str4",
IF(Len(A5)>1, "str5",
IF(Len(A6)>1, "str6",
IF(Len(A7)>1, "str7",
IF(Len(A8)>1, "str8",
IF(Len(A9)>1, "str9","str10")))))))))

TIA,
Bob

 

[JE McGimpsey]

Functions in XL can only be nested 7 deep.

One workaround (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

="str" & IF(ISNA(MATCH(TRUE,LEN(A1:A9)>1,FALSE)), 10,
MATCH(TRUE,LEN(A1:A9)>1,FALSE))

 

[mudraker]

Bob

Excel has a limit of 7 IF statements. Your formula excedds this whc
will be causing you problem