Nested IF statements

[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.

--
Regards

Roger Govier


"misstrious" <theresaluff@gmail.com> wrote in message
news:1140962173.153412.30020@t39g2000cwt.googlegroups.com...
> 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
>

[Bruno Campanini]

"misstrious" <theresaluff@gmail.com> wrote in message
news:1140962173.153412.30020@t39g2000cwt.googlegroups.com...
> 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]

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 :-)
------------------------------*------------------------------*----------------


"Bruno Campanini" <bruno.campanini@tin.it> wrote in message
news:ebl6xSuOGHA.2668@tk2msftngp13.phx.gbl...
> "misstrious" <theresaluff@gmail.com> wrote in message
> news:1140962173.153412.30020@t39g2000cwt.googlegroups.com...
>> 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" <theresaluff@gmail.com> wrote in message
news:1140962173.153412.30020@t39g2000cwt.googlegroups.com...
> 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
>

[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