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." wrote in message
...
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." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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" wrote in message
...
Thats Good,
"Ms. P." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thats Good,
"Ms. P." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thats Good,
"Ms. P." wrote in message
...
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","E rror
Message"))))))

Regards,
Alan.
"Alan" wrote in message
...
=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." wrote in message
...
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.