Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I combine IF and OR?
I am very new to Excel (I've only used it a few times before this). I
have already figured out (mostly by accident) how to get my worksheet to give me the following: One cell is for input of the company Division numbers. When a number is entered, the employee number and manager name of that Division appear in other cells. If nothing is entered, I want it to remain blank. My current formula for six of the employee numbers and the "blank" is: =IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701, "40323",IF(K53=801,"40323",IF(K53=601,"99253", IF(K53=401,"41723",IF(K53=""," "))))))) Since I can only nest seven things, I can't get the seventh or eighth manager into the formula and still keep the blank option. However, currently three of the managers are each overseeing two or three Divisions each, so I'd like to add an OR statement in there. I'd like it to say that if K53 has either 301 or 901 to put employee 11517, if K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685 to put employee 49325, if K53 is 662 to put employee 05498, and keep the rest of the IF statements as they are. Division 662 is a special exceptions thing, though, so rarely used and can be left out if need be. I don't know how to set up the formula though. I tried doing just one of the managers and thought I had it, but keep getting "FALSE" when I test it. Can any more experienced person help me get a formula that works (with or without the 662 guy). Thanks. |
#2
|
|||
|
|||
Try to use the VLOOKUP function instead of a bunch of IF's.
ana_15825 - ExcelForums.com wrote: I am very new to Excel (I've only used it a few times before this). I have already figured out (mostly by accident) how to get my worksheet to give me the following: One cell is for input of the company Division numbers. When a number is entered, the employee number and manager name of that Division appear in other cells. If nothing is entered, I want it to remain blank. My current formula for six of the employee numbers and the "blank" is: =IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701, "40323",IF(K53=801,"40323",IF(K53=601,"99253", IF(K53=401,"41723",IF(K53=""," "))))))) Since I can only nest seven things, I can't get the seventh or eighth manager into the formula and still keep the blank option. However, currently three of the managers are each overseeing two or three Divisions each, so I'd like to add an OR statement in there. I'd like it to say that if K53 has either 301 or 901 to put employee 11517, if K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685 to put employee 49325, if K53 is 662 to put employee 05498, and keep the rest of the IF statements as they are. Division 662 is a special exceptions thing, though, so rarely used and can be left out if need be. I don't know how to set up the formula though. I tried doing just one of the managers and thought I had it, but keep getting "FALSE" when I test it. Can any more experienced person help me get a formula that works (with or without the 662 guy). Thanks. |
#3
|
|||
|
|||
VLOOKUP won't work. It's a form, not a table, K53 is not nearby.
Thanks for answering. |
#4
|
|||
|
|||
The OR statement can be combined with the IF statement as
follows.........adjust to suit......... =IF(OR(K53=301,K53=901),"11517",IF(OR(K53=701,K53= 801),"40323", etc etc....... Vaya con Dios, Chuck, CABGx3 "ana_15825 - ExcelForums.com" wrote in message ... I am very new to Excel (I've only used it a few times before this). I have already figured out (mostly by accident) how to get my worksheet to give me the following: One cell is for input of the company Division numbers. When a number is entered, the employee number and manager name of that Division appear in other cells. If nothing is entered, I want it to remain blank. My current formula for six of the employee numbers and the "blank" is: =IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701, "40323",IF(K53=801,"40323" ,IF(K53=601,"99253", IF(K53=401,"41723",IF(K53=""," "))))))) Since I can only nest seven things, I can't get the seventh or eighth manager into the formula and still keep the blank option. However, currently three of the managers are each overseeing two or three Divisions each, so I'd like to add an OR statement in there. I'd like it to say that if K53 has either 301 or 901 to put employee 11517, if K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685 to put employee 49325, if K53 is 662 to put employee 05498, and keep the rest of the IF statements as they are. Division 662 is a special exceptions thing, though, so rarely used and can be left out if need be. I don't know how to set up the formula though. I tried doing just one of the managers and thought I had it, but keep getting "FALSE" when I test it. Can any more experienced person help me get a formula that works (with or without the 662 guy). Thanks. |
#5
|
|||
|
|||
"ana_15825 - ExcelForums.com" wrote
VLOOKUP won't work. It's a form, not a table, K53 is not nearby But I seem to be able to get what Aladin suggested to work? <g In Sheet2, the list below was created in A1:B10 (Numbers in col B were entered as text with a leading apostrophe, following your specimen data in the orig. post) 301 11517 901 11517 701 40323 801 40323 601 99253 401 41723 501 49325 503 49325 685 49325 662 05498 etc (Note that col A above need not be sorted as we're going for an exact match in the VLOOKUP) In Sheet1, if we put in say, B2 (can be any cell other than K53): =IF(K53="","",VLOOKUP(K53,Sheet2!A:B,2,0)) B2 seems to return the correct corresponding text number from col B in Sheet2 for the input in K53. And if K53 is cleared, a blank: "" will be returned (Think returning a blank: "" is better than returning a space: " ", to achieve the same visual effect). If there's no match for the input in K53, you'll get an #N/A error. If we wanted a blank: "" to be returned for non-matching inputs in K53 (which could also cover the instance of K53 being cleared), we could put instead in B2: =IF(ISNA(VLOOKUP(K53,Sheet2!A:B,2,0)),"",VLOOKUP(K 53,Sheet2!A:B,2,0)) IMO, the suggested VLOOKUP approach does seem a neater way to do it (we could simply extend the reference table in Sheet2 to suit future additions, for example, w/o having to change the formula) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Here's a sample file with the implemented construct:
http://www.savefile.com/files/9843022 File: ana_15825_wksht.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Can even be a little shorter if you use array constants:
=IF(K53="","",IF(OR(K53={301,901}),11517,IF(OR(K53 ={701,801}),40323,IF(K53=6 01,99253,IF(K53=401,41723))))) I left the employee numbers as numbers by eliminating the quotes. If necessary, you can add them back. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CLR" wrote in message ... The OR statement can be combined with the IF statement as follows.........adjust to suit......... =IF(OR(K53=301,K53=901),"11517",IF(OR(K53=701,K53= 801),"40323", etc etc....... Vaya con Dios, Chuck, CABGx3 "ana_15825 - ExcelForums.com" wrote in message ... I am very new to Excel (I've only used it a few times before this). I have already figured out (mostly by accident) how to get my worksheet to give me the following: One cell is for input of the company Division numbers. When a number is entered, the employee number and manager name of that Division appear in other cells. If nothing is entered, I want it to remain blank. My current formula for six of the employee numbers and the "blank" is: =IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701, "40323",IF(K53=801,"40323" ,IF(K53=601,"99253", IF(K53=401,"41723",IF(K53=""," "))))))) Since I can only nest seven things, I can't get the seventh or eighth manager into the formula and still keep the blank option. However, currently three of the managers are each overseeing two or three Divisions each, so I'd like to add an OR statement in there. I'd like it to say that if K53 has either 301 or 901 to put employee 11517, if K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685 to put employee 49325, if K53 is 662 to put employee 05498, and keep the rest of the IF statements as they are. Division 662 is a special exceptions thing, though, so rarely used and can be left out if need be. I don't know how to set up the formula though. I tried doing just one of the managers and thought I had it, but keep getting "FALSE" when I test it. Can any more experienced person help me get a formula that works (with or without the 662 guy). Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel combine tables from multiple worksheets in to one sheet? | Excel Worksheet Functions | |||
How do you combine two columns to one? | Excel Discussion (Misc queries) | |||
Combine two formulas in one cell | Excel Discussion (Misc queries) | |||
How can I combine 3 separate mailing list? | New Users to Excel | |||
how to combine multiple files in ms excel | Excel Discussion (Misc queries) |