Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Nested "If" Function
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |