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.