Nested IF statements

[Bruno Campanini]

In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

VBA doesn't suffer such a nesting limitation.

If [DV22] = 1 Then
MsgBox [DV22]
ElseIf [V22] = 2 Then
MsgBox [V22]
ElseIf [D2] = 3 Then
MsgBox [D2]
ElseIf [F3] = 4 And [G3]=6 Then
MsgBox [G3]
ElseIf [DV22] = 5 And [A2]=3 Or [K12]="wss" Then
..........................
End If

Bruno

 

[misstrious]

In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

Any help would be appreciated.

Thank you

 

[Roger Govier]

Hi

One way would be to set up a list of the different outcomes. Say on
sheet2, in cells A1:B22 you put a list with
Medic a+b
Home b+c etc.
Then on sheet1, you could use the formula
=IF(A2<>"Fri","",VLOOKUP(B2,Sheet2!$A$1:$B$22,2,0))
So if the day in A2 is not "Fri" then there is a null return, otherwise
it looks up the value of B2 in your table.

 

[Ken Wright]

And nor do Excel formulas when the right one is used, ie VLOOKUP

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

VBA doesn't suffer such a nesting limitation.

If [DV22] = 1 Then
MsgBox [DV22]
ElseIf [V22] = 2 Then
MsgBox [V22]
ElseIf [D2] = 3 Then
MsgBox [D2]
ElseIf [F3] = 4 And [G3]=6 Then
MsgBox [G3]
ElseIf [DV22] = 5 And [A2]=3 Or [K12]="wss" Then
.........................
End If

Bruno

 

[Ken Wright]

Just for info - You only need to post in one newsgroup - they are all read
by most of the regulars, and posting to a number of them simply fragments
responses, especially when the question has already been answered in another
group.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[misstrious]

Thank you very much for you help, this solution works well.

However, I have hit another snag

This calculation needs to be applied to a large table, the a+b formulas
don't change but the cells used in the formulas do increment.

So we have the following scenario:

Row 3 Check column A if this is a fri then check the contents of column
B, if it matches column heading on sheet 2 then perform calculation
using values contained in the column on sheet 2 row 3.

Row 4 Check column A if this is a fri then check the contents of column
B, if it matches column heading on sheet 2 then perform calculation
using values contained in the column on sheet 2 row 4.

And so on and so forth.

Forgive me if I am being stupid but I cannot see a way of using a
Lookup table to increment the cell references in the calculations, so
that they stay in step with the calculations

I hope this makes sense

Once again, thank you for your help

Theresa