Try to use CHOOSE instead of IF 4

[IJR]

Nothing drives one crazy than a series of IF inside an IF. But we usually start with Fortran and Basic programming before going into spreadsheet programming. By default, we love the IF( )

I find the CHOOSE function in Excel a very useful tool. Those who havent tried, try it and those who have used it creatively lets learn more from you

Regards

IJR

 

[hangchou2]

Couild you send a example?

 

[jproj]

Example:

insead of:

=IF(A1=1,"one",IF(A1=2,"two",IF(A1=3,"three",">three&quot))

use the "choose" function:

=Choose(A1,"one","two","three&quot

The problem with the choose statement is that (as far as I know), if you enter a number > 3 into cell A1, you get a #VALUE error in the choose cell. You can of course remidy this with a conditional statement (assuming no zero or negative numbers are to be entered):

=IF(A1<=3,Choose(A1,&quot;one&quot;,&quot;two&quot;,&quot;three&quot,&quot;>three&quot

Hope this helps.

 

[nitin36537]

Hi IRJ

I have developed this function in VBA (like select case in VBA)

Public Function FindCase(TxtStr, TxtDefaultValue, ParamArray VarAnsArray())

Dim i As Integer
FindCase = TxtDefaultValue

For i = 0 To UBound(VarAnsArray) - 1 Step 2
If TxtStr = VarAnsArray(i) Then
FindCase = VarAnsArray(i + 1)
Exit Function
End If
Next
End Function

we can use like this
=FINDCASE(A1,1,&quot;A&quot;,1,&quot;A2L&quot;,2,&quot;A2S&quot;,1,&quot;A4S&quot;,2)
IF cell A1 CONTAIN &quot;A&quot; RESULT WILL BE 1 (3rd & 4th arguments)
IF A1 CONTAIN &quot;A2L&quot; RESULT WILL BE 2(5th & 6th)
IF A1 CONTAIN &quot;A2S&quot; RESULT WILL BE 1(7th & 8th)
IF A1 CONTAIN &quot;A4S&quot; RESULT WILL BE 2(9th & 10th)
ELSE RESULT WILL BE 1 (second argument)

I tried to developed similer to select case statments in VBA

I have tried with string argument & it work OK.

Nitin Patel
India

 

[trainguy]

Thank you IJR & jproj!

I've been looking for this type of function for a LONG time.
Verrry cool.