8th Len() fails in nested ifs??

  • Thread starter Thread starter Bob Kilmer
  • Start date Start date
B

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
 
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))
 
Bob

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