Re: if statements and nesting


"Harlan Grove" <hrlngrv@xxxxxxx> wrote in message
news:1143019007.711632.297730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
RUSS BARTOLI wrote...
Build a table and use a lookup function.
...

Not always the best workaround. It'd be awkward to convert the
following into a lookup.

=IF(B2=a,1,IF(C3=b,2,IF(D4=c,3,IF(E5=d,4,IF(F6=e,5,IF(G7=f,6,IF(H8=g,7,IF(I9=h,8,
IF(J10=i,9,IF(K11=j,10,""))))))))))

It'd take something like

=LOOKUP(2,1/({1;0;0;0;0;0;0;0;0;0;0}+(K11=j)*{0;1;0;0;0;0;0;0;0;0;0}
+(J10=i)*{0;0;1;0;0;0;0;0;0;0;0}+(I9=h)*{0;0;0;1;0;0;0;0;0;0;0}
+(H8=g)*{0;0;0;0;1;0;0;0;0;0;0}+(G7=f)*{0;0;0;0;0;1;0;0;0;0;0}
+(F6=e)*{0;0;0;0;0;0;1;0;0;0;0}+(E5=d)*{0;0;0;0;0;0;0;1;0;0;0}
+(D4=c)*{0;0;0;0;0;0;0;0;1;0;0}+(C3=b)*{0;0;0;0;0;0;0;0;0;1;0}
+(B2=a)*{0;0;0;0;0;0;0;0;0;0;1}),{"";10;9;8;7;6;5;4;3;2;1})

In this case CHOOSE would be better.

=CHOOSE(1+INT(LOG(1024*(B2=a)+512*(C3=b)+256*(D4=c)+128*(E5=d)
+64*(F6=e)+32*(G7=f)+16*(H8=g)+8*(I9=h)+4*(J10=i)+2*(K11=j)+1,2)),
"",10,9,8,7,6,5,4,3,2,1)

In ordinary programming, five or more successive if statements usually are a
sign of bad design, as they are inflexible and error-prone. For the problem
stated, I would prefer a VBA function, such as:

Function Getvalue()
Dim Found As Boolean, i As Integer, c As Byte
Getvalue = ""
Found = False: i = 2: c = Asc("a")
While Not Found And i <> 10
If Cells(i, i) = Chr(c) Then
Found = True: Getvalue = i - 1
Else
i = i + 1: c = c + 1
End If
Wend
End Function

My general advice is:

If you reach the limits of your tool, try to simplify your problem or look
for a better tool


.