Thread: Nested "If" Function

Hi!

Create a 2 column table:

LD..........DIS
AF..........REG
R............TVR
AE..........REG
etc
etc

Assume this table is in the range D1:E10

Formula:

=VLOOKUP(B1,D1:E10,2,0)

Biff

"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>I know that my "if" statement can only contain 7 nested functions, so
>here's
> my problem. I have the following table:
>
> Long Term Disability = LD Active Part-Time = AP
> Active Full Time = AF Terminated = T
> Retired = R Family Leave = LF
> Active Union Hall Extra - AE Maternity Leave = LM
> Personal Leave = LP Worker's Comp = WC
>
> The above codes are in column B. I want to put a formula in column C that
> will do the following. Look at the codes in column B and convert them to:
>
> LD = DIS AP=REG
> AF=REG T=TMG
> R=TVR LF=EXT
> AE=REG LM=EXT
> LP=EXT WC=DIS
>
> I understand how to write the formula for each one individiually, but
> since
> I have more than 7 items, I don't know how to tell it for example:
>
> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>
> How do I do the "or" part? Any help is much appreciated. Thanks.

=IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
Regards,
Alan.
"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>I know that my "if" statement can only contain 7 nested functions, so
>here's
> my problem. I have the following table:
>
> Long Term Disability = LD Active Part-Time = AP
> Active Full Time = AF Terminated = T
> Retired = R Family Leave = LF
> Active Union Hall Extra - AE Maternity Leave = LM
> Personal Leave = LP Worker's Comp = WC
>
> The above codes are in column B. I want to put a formula in column C that
> will do the following. Look at the codes in column B and convert them to:
>
> LD = DIS AP=REG
> AF=REG T=TMG
> R=TVR LF=EXT
> AE=REG LM=EXT
> LP=EXT WC=DIS
>
> I understand how to write the formula for each one individiually, but
> since
> I have more than 7 items, I don't know how to tell it for example:
>
> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>
> How do I do the "or" part? Any help is much appreciated. Thanks.

Or this will return a blank if B1 is blank, or an error message if anything
is entered which isn't in your list.

=IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
Message"))))))

Regards,
Alan.
"Alan" <alan111@ntlworld.com> wrote in message
news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
> =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
> Regards,
> Alan.
> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>I know that my "if" statement can only contain 7 nested functions, so
>>here's
>> my problem. I have the following table:
>>
>> Long Term Disability = LD Active Part-Time = AP
>> Active Full Time = AF Terminated = T
>> Retired = R Family Leave = LF
>> Active Union Hall Extra - AE Maternity Leave = LM
>> Personal Leave = LP Worker's Comp = WC
>>
>> The above codes are in column B. I want to put a formula in column C
>> that
>> will do the following. Look at the codes in column B and convert them
>> to:
>>
>> LD = DIS AP=REG
>> AF=REG T=TMG
>> R=TVR LF=EXT
>> AE=REG LM=EXT
>> LP=EXT WC=DIS
>>
>> I understand how to write the formula for each one individiually, but
>> since
>> I have more than 7 items, I don't know how to tell it for example:
>>
>> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
>> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>
>> How do I do the "or" part? Any help is much appreciated. Thanks.
>
>

Thanks Alan. That's exactly what I was looking for.

"Alan" wrote:

> Or this will return a blank if B1 is blank, or an error message if anything
> is entered which isn't in your list.
>
> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
> Message"))))))
>
> Regards,
> Alan.
> "Alan" <alan111@ntlworld.com> wrote in message
> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
> > Regards,
> > Alan.
> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
> >>I know that my "if" statement can only contain 7 nested functions, so
> >>here's
> >> my problem. I have the following table:
> >>
> >> Long Term Disability = LD Active Part-Time = AP
> >> Active Full Time = AF Terminated = T
> >> Retired = R Family Leave = LF
> >> Active Union Hall Extra - AE Maternity Leave = LM
> >> Personal Leave = LP Worker's Comp = WC
> >>
> >> The above codes are in column B. I want to put a formula in column C
> >> that
> >> will do the following. Look at the codes in column B and convert them
> >> to:
> >>
> >> LD = DIS AP=REG
> >> AF=REG T=TMG
> >> R=TVR LF=EXT
> >> AE=REG LM=EXT
> >> LP=EXT WC=DIS
> >>
> >> I understand how to write the formula for each one individiually, but
> >> since
> >> I have more than 7 items, I don't know how to tell it for example:
> >>
> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
> >>
> >> How do I do the "or" part? Any help is much appreciated. Thanks.
> >
> >
>
>
>

Thats Good,
"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
> Thanks Alan. That's exactly what I was looking for.
>
> "Alan" wrote:
>
>> Or this will return a blank if B1 is blank, or an error message if
>> anything
>> is entered which isn't in your list.
>>
>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>> Message"))))))
>>
>> Regards,
>> Alan.
>> "Alan" <alan111@ntlworld.com> wrote in message
>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>> > Regards,
>> > Alan.
>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>> >>I know that my "if" statement can only contain 7 nested functions, so
>> >>here's
>> >> my problem. I have the following table:
>> >>
>> >> Long Term Disability = LD Active Part-Time = AP
>> >> Active Full Time = AF Terminated = T
>> >> Retired = R Family Leave = LF
>> >> Active Union Hall Extra - AE Maternity Leave = LM
>> >> Personal Leave = LP Worker's Comp = WC
>> >>
>> >> The above codes are in column B. I want to put a formula in column C
>> >> that
>> >> will do the following. Look at the codes in column B and convert them
>> >> to:
>> >>
>> >> LD = DIS AP=REG
>> >> AF=REG T=TMG
>> >> R=TVR LF=EXT
>> >> AE=REG LM=EXT
>> >> LP=EXT WC=DIS
>> >>
>> >> I understand how to write the formula for each one individiually, but
>> >> since
>> >> I have more than 7 items, I don't know how to tell it for example:
>> >>
>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT",
>> >> if
>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>> >>
>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>> >
>> >
>>
>>
>>

Hi!

If you're going to use that long inefficient nested IF formula, might as
well make it shorter:

=IF(B1="","",IF(OR(B1={"LD","WC"}),"DIS",IF(OR(B1={"LF","LM","LP"}),"EXT",IF(B1="T","TMG",IF(B1="R", "TVR",IF(OR(B1={"AF","AE","AP"}),"REG","Error
Message"))))))

Biff

"Alan" <alan111@ntlworld.com> wrote in message
news:euCrk$BpFHA.1416@TK2MSFTNGP09.phx.gbl...
> Thats Good,
> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
>> Thanks Alan. That's exactly what I was looking for.
>>
>> "Alan" wrote:
>>
>>> Or this will return a blank if B1 is blank, or an error message if
>>> anything
>>> is entered which isn't in your list.
>>>
>>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>>> Message"))))))
>>>
>>> Regards,
>>> Alan.
>>> "Alan" <alan111@ntlworld.com> wrote in message
>>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>>> > Regards,
>>> > Alan.
>>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>> >>I know that my "if" statement can only contain 7 nested functions, so
>>> >>here's
>>> >> my problem. I have the following table:
>>> >>
>>> >> Long Term Disability = LD Active Part-Time = AP
>>> >> Active Full Time = AF Terminated = T
>>> >> Retired = R Family Leave = LF
>>> >> Active Union Hall Extra - AE Maternity Leave = LM
>>> >> Personal Leave = LP Worker's Comp = WC
>>> >>
>>> >> The above codes are in column B. I want to put a formula in column C
>>> >> that
>>> >> will do the following. Look at the codes in column B and convert
>>> >> them
>>> >> to:
>>> >>
>>> >> LD = DIS AP=REG
>>> >> AF=REG T=TMG
>>> >> R=TVR LF=EXT
>>> >> AE=REG LM=EXT
>>> >> LP=EXT WC=DIS
>>> >>
>>> >> I understand how to write the formula for each one individiually, but
>>> >> since
>>> >> I have more than 7 items, I don't know how to tell it for example:
>>> >>
>>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then
>>> >> "EXT", if
>>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>> >>
>>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>>> >
>>> >
>>>
>>>
>>>
>
>

Hi Biff,
Why is a nested IF formula inefficient? Especially in a small file such as
Ms P. describes? Surely you cant be talking eighties to mid nineties
processor speeds and disc space? There will be no discernable difference in
speed of calculation between a nested IF or a VLOOKUP formula in a case such
as this unless you are using literally thousands of them on a modern PC.
I would personally use the VLOOKUP option that you proposed that more
experienced users such as you and I know already about, but, if it does the
job for someone who is apparently not au fait with Excel formulas then
what's the beef? The formula I posted leaves little room for expansion as
there are six IF's there already, but it works so what's the problem?
Good use of the{}'s by the way,
Regards,
Alan.
Regards,
Alan.
"Biff" <biffinpitt@comcast.net> wrote in message
news:OLVV6wEpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Hi!
>
> If you're going to use that long inefficient nested IF formula, might as
> well make it shorter:
>
> =IF(B1="","",IF(OR(B1={"LD","WC"}),"DIS",IF(OR(B1={"LF","LM","LP"}),"EXT",IF(B1="T","TMG",IF(B1="R", "TVR",IF(OR(B1={"AF","AE","AP"}),"REG","Error
> Message"))))))
>
> Biff
>
> "Alan" <alan111@ntlworld.com> wrote in message
> news:euCrk$BpFHA.1416@TK2MSFTNGP09.phx.gbl...
>> Thats Good,
>> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>> news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
>>> Thanks Alan. That's exactly what I was looking for.
>>>
>>> "Alan" wrote:
>>>
>>>> Or this will return a blank if B1 is blank, or an error message if
>>>> anything
>>>> is entered which isn't in your list.
>>>>
>>>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>>>> Message"))))))
>>>>
>>>> Regards,
>>>> Alan.
>>>> "Alan" <alan111@ntlworld.com> wrote in message
>>>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>>>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>>>> > Regards,
>>>> > Alan.
>>>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>>> >>I know that my "if" statement can only contain 7 nested functions, so
>>>> >>here's
>>>> >> my problem. I have the following table:
>>>> >>
>>>> >> Long Term Disability = LD Active Part-Time = AP
>>>> >> Active Full Time = AF Terminated = T
>>>> >> Retired = R Family Leave = LF
>>>> >> Active Union Hall Extra - AE Maternity Leave = LM
>>>> >> Personal Leave = LP Worker's Comp = WC
>>>> >>
>>>> >> The above codes are in column B. I want to put a formula in column
>>>> >> C
>>>> >> that
>>>> >> will do the following. Look at the codes in column B and convert
>>>> >> them
>>>> >> to:
>>>> >>
>>>> >> LD = DIS AP=REG
>>>> >> AF=REG T=TMG
>>>> >> R=TVR LF=EXT
>>>> >> AE=REG LM=EXT
>>>> >> LP=EXT WC=DIS
>>>> >>
>>>> >> I understand how to write the formula for each one individiually,
>>>> >> but
>>>> >> since
>>>> >> I have more than 7 items, I don't know how to tell it for example:
>>>> >>
>>>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then
>>>> >> "EXT", if
>>>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>>> >>
>>>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>>>> >
>>>> >
>>>>
>>>>
>>>>
>>
>>
>
>

Hi Alan!

No beef and no problem!

When I was a typical spreadsheet user I used to build formulas like that
myself. Back then I had no idea there were forums for this stuff so
everything I knew I taught myself. Then I found this forum and my knowledge
grew exponentially!

The great thing about spreadsheets is that there are usually many ways to
accomplish a task but usually one of those ways is the best way out of all
the choices. When I post a reply I try to suggest that best way based solely
on my experience and knowledge. There are still a lot of things I don't know
so my suggestions may not always be the best way! In fact, I participate
here so that I can learn more!

If we both agree (I think we do!) that a lookup is the best way to go about
this particular situtation then from my point of view, and as I explained
above, why not suggest that to the poster?

No beef and no problem!

Cheers

Biff

"Alan" <alan111@ntlworld.com> wrote in message
news:u2FdFKOpFHA.320@TK2MSFTNGP09.phx.gbl...
> Hi Biff,
> Why is a nested IF formula inefficient? Especially in a small file such as
> Ms P. describes? Surely you cant be talking eighties to mid nineties
> processor speeds and disc space? There will be no discernable difference
> in speed of calculation between a nested IF or a VLOOKUP formula in a case
> such as this unless you are using literally thousands of them on a modern
> PC.
> I would personally use the VLOOKUP option that you proposed that more
> experienced users such as you and I know already about, but, if it does
> the job for someone who is apparently not au fait with Excel formulas then
> what's the beef? The formula I posted leaves little room for expansion as
> there are six IF's there already, but it works so what's the problem?
> Good use of the{}'s by the way,
> Regards,
> Alan.
> Regards,
> Alan.
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:OLVV6wEpFHA.4088@TK2MSFTNGP15.phx.gbl...
>> Hi!
>>
>> If you're going to use that long inefficient nested IF formula, might as
>> well make it shorter:
>>
>> =IF(B1="","",IF(OR(B1={"LD","WC"}),"DIS",IF(OR(B1={"LF","LM","LP"}),"EXT",IF(B1="T","TMG",IF(B1="R", "TVR",IF(OR(B1={"AF","AE","AP"}),"REG","Error
>> Message"))))))
>>
>> Biff
>>
>> "Alan" <alan111@ntlworld.com> wrote in message
>> news:euCrk$BpFHA.1416@TK2MSFTNGP09.phx.gbl...
>>> Thats Good,
>>> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>> news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
>>>> Thanks Alan. That's exactly what I was looking for.
>>>>
>>>> "Alan" wrote:
>>>>
>>>>> Or this will return a blank if B1 is blank, or an error message if
>>>>> anything
>>>>> is entered which isn't in your list.
>>>>>
>>>>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>>>>> Message"))))))
>>>>>
>>>>> Regards,
>>>>> Alan.
>>>>> "Alan" <alan111@ntlworld.com> wrote in message
>>>>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>>>>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>>>>> > Regards,
>>>>> > Alan.
>>>>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>>>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>>>> >>I know that my "if" statement can only contain 7 nested functions,
>>>>> >>so
>>>>> >>here's
>>>>> >> my problem. I have the following table:
>>>>> >>
>>>>> >> Long Term Disability = LD Active Part-Time = AP
>>>>> >> Active Full Time = AF Terminated = T
>>>>> >> Retired = R Family Leave = LF
>>>>> >> Active Union Hall Extra - AE Maternity Leave = LM
>>>>> >> Personal Leave = LP Worker's Comp = WC
>>>>> >>
>>>>> >> The above codes are in column B. I want to put a formula in column
>>>>> >> C
>>>>> >> that
>>>>> >> will do the following. Look at the codes in column B and convert
>>>>> >> them
>>>>> >> to:
>>>>> >>
>>>>> >> LD = DIS AP=REG
>>>>> >> AF=REG T=TMG
>>>>> >> R=TVR LF=EXT
>>>>> >> AE=REG LM=EXT
>>>>> >> LP=EXT WC=DIS
>>>>> >>
>>>>> >> I understand how to write the formula for each one individiually,
>>>>> >> but
>>>>> >> since
>>>>> >> I have more than 7 items, I don't know how to tell it for example:
>>>>> >>
>>>>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then
>>>>> >> "EXT", if
>>>>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>>>> >>
>>>>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>>>>> >
>>>>> >
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
>

Hi!

Create a 2 column table:

LD..........DIS
AF..........REG
R............TVR
AE..........REG
etc
etc

Assume this table is in the range D1:E10

Formula:

=VLOOKUP(B1,D1:E10,2,0)

Biff

"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>I know that my "if" statement can only contain 7 nested functions, so
>here's
> my problem. I have the following table:
>
> Long Term Disability = LD Active Part-Time = AP
> Active Full Time = AF Terminated = T
> Retired = R Family Leave = LF
> Active Union Hall Extra - AE Maternity Leave = LM
> Personal Leave = LP Worker's Comp = WC
>
> The above codes are in column B. I want to put a formula in column C that
> will do the following. Look at the codes in column B and convert them to:
>
> LD = DIS AP=REG
> AF=REG T=TMG
> R=TVR LF=EXT
> AE=REG LM=EXT
> LP=EXT WC=DIS
>
> I understand how to write the formula for each one individiually, but
> since
> I have more than 7 items, I don't know how to tell it for example:
>
> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>
> How do I do the "or" part? Any help is much appreciated. Thanks.

=IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
Regards,
Alan.
"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>I know that my "if" statement can only contain 7 nested functions, so
>here's
> my problem. I have the following table:
>
> Long Term Disability = LD Active Part-Time = AP
> Active Full Time = AF Terminated = T
> Retired = R Family Leave = LF
> Active Union Hall Extra - AE Maternity Leave = LM
> Personal Leave = LP Worker's Comp = WC
>
> The above codes are in column B. I want to put a formula in column C that
> will do the following. Look at the codes in column B and convert them to:
>
> LD = DIS AP=REG
> AF=REG T=TMG
> R=TVR LF=EXT
> AE=REG LM=EXT
> LP=EXT WC=DIS
>
> I understand how to write the formula for each one individiually, but
> since
> I have more than 7 items, I don't know how to tell it for example:
>
> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>
> How do I do the "or" part? Any help is much appreciated. Thanks.

Or this will return a blank if B1 is blank, or an error message if anything
is entered which isn't in your list.

=IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
Message"))))))

Regards,
Alan.
"Alan" <alan111@ntlworld.com> wrote in message
news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
> =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
> Regards,
> Alan.
> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>I know that my "if" statement can only contain 7 nested functions, so
>>here's
>> my problem. I have the following table:
>>
>> Long Term Disability = LD Active Part-Time = AP
>> Active Full Time = AF Terminated = T
>> Retired = R Family Leave = LF
>> Active Union Hall Extra - AE Maternity Leave = LM
>> Personal Leave = LP Worker's Comp = WC
>>
>> The above codes are in column B. I want to put a formula in column C
>> that
>> will do the following. Look at the codes in column B and convert them
>> to:
>>
>> LD = DIS AP=REG
>> AF=REG T=TMG
>> R=TVR LF=EXT
>> AE=REG LM=EXT
>> LP=EXT WC=DIS
>>
>> I understand how to write the formula for each one individiually, but
>> since
>> I have more than 7 items, I don't know how to tell it for example:
>>
>> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
>> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>
>> How do I do the "or" part? Any help is much appreciated. Thanks.
>
>

Thanks Alan. That's exactly what I was looking for.

"Alan" wrote:

> Or this will return a blank if B1 is blank, or an error message if anything
> is entered which isn't in your list.
>
> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
> Message"))))))
>
> Regards,
> Alan.
> "Alan" <alan111@ntlworld.com> wrote in message
> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
> > Regards,
> > Alan.
> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
> >>I know that my "if" statement can only contain 7 nested functions, so
> >>here's
> >> my problem. I have the following table:
> >>
> >> Long Term Disability = LD Active Part-Time = AP
> >> Active Full Time = AF Terminated = T
> >> Retired = R Family Leave = LF
> >> Active Union Hall Extra - AE Maternity Leave = LM
> >> Personal Leave = LP Worker's Comp = WC
> >>
> >> The above codes are in column B. I want to put a formula in column C
> >> that
> >> will do the following. Look at the codes in column B and convert them
> >> to:
> >>
> >> LD = DIS AP=REG
> >> AF=REG T=TMG
> >> R=TVR LF=EXT
> >> AE=REG LM=EXT
> >> LP=EXT WC=DIS
> >>
> >> I understand how to write the formula for each one individiually, but
> >> since
> >> I have more than 7 items, I don't know how to tell it for example:
> >>
> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT", if
> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
> >>
> >> How do I do the "or" part? Any help is much appreciated. Thanks.
> >
> >
>
>
>

Thats Good,
"Ms. P." <MsP@discussions.microsoft.com> wrote in message
news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
> Thanks Alan. That's exactly what I was looking for.
>
> "Alan" wrote:
>
>> Or this will return a blank if B1 is blank, or an error message if
>> anything
>> is entered which isn't in your list.
>>
>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>> Message"))))))
>>
>> Regards,
>> Alan.
>> "Alan" <alan111@ntlworld.com> wrote in message
>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>> > Regards,
>> > Alan.
>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>> >>I know that my "if" statement can only contain 7 nested functions, so
>> >>here's
>> >> my problem. I have the following table:
>> >>
>> >> Long Term Disability = LD Active Part-Time = AP
>> >> Active Full Time = AF Terminated = T
>> >> Retired = R Family Leave = LF
>> >> Active Union Hall Extra - AE Maternity Leave = LM
>> >> Personal Leave = LP Worker's Comp = WC
>> >>
>> >> The above codes are in column B. I want to put a formula in column C
>> >> that
>> >> will do the following. Look at the codes in column B and convert them
>> >> to:
>> >>
>> >> LD = DIS AP=REG
>> >> AF=REG T=TMG
>> >> R=TVR LF=EXT
>> >> AE=REG LM=EXT
>> >> LP=EXT WC=DIS
>> >>
>> >> I understand how to write the formula for each one individiually, but
>> >> since
>> >> I have more than 7 items, I don't know how to tell it for example:
>> >>
>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then "EXT",
>> >> if
>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>> >>
>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>> >
>> >
>>
>>
>>

Hi!

If you're going to use that long inefficient nested IF formula, might as
well make it shorter:

=IF(B1="","",IF(OR(B1={"LD","WC"}),"DIS",IF(OR(B1={"LF","LM","LP"}),"EXT",IF(B1="T","TMG",IF(B1="R", "TVR",IF(OR(B1={"AF","AE","AP"}),"REG","Error
Message"))))))

Biff

"Alan" <alan111@ntlworld.com> wrote in message
news:euCrk$BpFHA.1416@TK2MSFTNGP09.phx.gbl...
> Thats Good,
> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
> news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
>> Thanks Alan. That's exactly what I was looking for.
>>
>> "Alan" wrote:
>>
>>> Or this will return a blank if B1 is blank, or an error message if
>>> anything
>>> is entered which isn't in your list.
>>>
>>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>>> Message"))))))
>>>
>>> Regards,
>>> Alan.
>>> "Alan" <alan111@ntlworld.com> wrote in message
>>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>>> > Regards,
>>> > Alan.
>>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>> >>I know that my "if" statement can only contain 7 nested functions, so
>>> >>here's
>>> >> my problem. I have the following table:
>>> >>
>>> >> Long Term Disability = LD Active Part-Time = AP
>>> >> Active Full Time = AF Terminated = T
>>> >> Retired = R Family Leave = LF
>>> >> Active Union Hall Extra - AE Maternity Leave = LM
>>> >> Personal Leave = LP Worker's Comp = WC
>>> >>
>>> >> The above codes are in column B. I want to put a formula in column C
>>> >> that
>>> >> will do the following. Look at the codes in column B and convert
>>> >> them
>>> >> to:
>>> >>
>>> >> LD = DIS AP=REG
>>> >> AF=REG T=TMG
>>> >> R=TVR LF=EXT
>>> >> AE=REG LM=EXT
>>> >> LP=EXT WC=DIS
>>> >>
>>> >> I understand how to write the formula for each one individiually, but
>>> >> since
>>> >> I have more than 7 items, I don't know how to tell it for example:
>>> >>
>>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then
>>> >> "EXT", if
>>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>> >>
>>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>>> >
>>> >
>>>
>>>
>>>
>
>

Hi Biff,
Why is a nested IF formula inefficient? Especially in a small file such as
Ms P. describes? Surely you cant be talking eighties to mid nineties
processor speeds and disc space? There will be no discernable difference in
speed of calculation between a nested IF or a VLOOKUP formula in a case such
as this unless you are using literally thousands of them on a modern PC.
I would personally use the VLOOKUP option that you proposed that more
experienced users such as you and I know already about, but, if it does the
job for someone who is apparently not au fait with Excel formulas then
what's the beef? The formula I posted leaves little room for expansion as
there are six IF's there already, but it works so what's the problem?
Good use of the{}'s by the way,
Regards,
Alan.
Regards,
Alan.
"Biff" <biffinpitt@comcast.net> wrote in message
news:OLVV6wEpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Hi!
>
> If you're going to use that long inefficient nested IF formula, might as
> well make it shorter:
>
> =IF(B1="","",IF(OR(B1={"LD","WC"}),"DIS",IF(OR(B1={"LF","LM","LP"}),"EXT",IF(B1="T","TMG",IF(B1="R", "TVR",IF(OR(B1={"AF","AE","AP"}),"REG","Error
> Message"))))))
>
> Biff
>
> "Alan" <alan111@ntlworld.com> wrote in message
> news:euCrk$BpFHA.1416@TK2MSFTNGP09.phx.gbl...
>> Thats Good,
>> "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>> news:36542D2A-696B-4177-B423-6D415E775E5C@microsoft.com...
>>> Thanks Alan. That's exactly what I was looking for.
>>>
>>> "Alan" wrote:
>>>
>>>> Or this will return a blank if B1 is blank, or an error message if
>>>> anything
>>>> is entered which isn't in your list.
>>>>
>>>> =IF(B1="","",IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1 ="R","TVR",IF(OR(B1="AF",B1="AE",B1="AP"),"REG","Error
>>>> Message"))))))
>>>>
>>>> Regards,
>>>> Alan.
>>>> "Alan" <alan111@ntlworld.com> wrote in message
>>>> news:unFzXW7oFHA.572@TK2MSFTNGP15.phx.gbl...
>>>> > =IF(OR(B1="LD",B1="WC"),"DIS",IF(OR(B1="LF",B1="LM",B1="LP"),"EXT",IF(B1="T","TMG",IF(B1="R","TVR"," REG"))))
>>>> > Regards,
>>>> > Alan.
>>>> > "Ms. P." <MsP@discussions.microsoft.com> wrote in message
>>>> > news:037D8818-23C6-4723-AA78-0C9B54F36322@microsoft.com...
>>>> >>I know that my "if" statement can only contain 7 nested functions, so
>>>> >>here's
>>>> >> my problem. I have the following table:
>>>> >>
>>>> >> Long Term Disability = LD Active Part-Time = AP
>>>> >> Active Full Time = AF Terminated = T
>>>> >> Retired = R Family Leave = LF
>>>> >> Active Union Hall Extra - AE Maternity Leave = LM
>>>> >> Personal Leave = LP Worker's Comp = WC
>>>> >>
>>>> >> The above codes are in column B. I want to put a formula in column
>>>> >> C
>>>> >> that
>>>> >> will do the following. Look at the codes in column B and convert
>>>> >> them
>>>> >> to:
>>>> >>
>>>> >> LD = DIS AP=REG
>>>> >> AF=REG T=TMG
>>>> >> R=TVR LF=EXT
>>>> >> AE=REG LM=EXT
>>>> >> LP=EXT WC=DIS
>>>> >>
>>>> >> I understand how to write the formula for each one individiually,
>>>> >> but
>>>> >> since
>>>> >> I have more than 7 items, I don't know how to tell it for example:
>>>> >>
>>>> >> if (B1="LD" or "WC" then "DIS", if B1="LF" or "LM" or "LP" then
>>>> >> "EXT", if
>>>> >> B1="T" then "TMG", if B1="R" then "TVR" otherwise "REG")
>>>> >>
>>>> >> How do I do the "or" part? Any help is much appreciated. Thanks.
>>>> >
>>>> >
>>>>
>>>>
>>>>
>>
>>
>
>