Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining an array to search by
I am trying to type a simple if.then formula, but would like to evaluate an
array of cells for the 2nd condition of the and statement. How do you designate this in the formula? Here is what I currently have: =CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS"))) If you have a better way to accomplish this task, any ideas would be appreciated. Thank You in advance for the help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining an array to search by
While I do not know why you have the concatinate, I assume you do
and that you want j4=G,kr=G etc for all of the cells through R4 =if(and(concatinate(J4:R4)="GGGGGGGGG", or(F2=2,F2=3),"K40-MP1.0-N01MS", "K50-MP1.0-N02MS" ) or =if(And(J4:R4=1,or(F2=2,F2=3),"K40-MP1.0-N01MS", "K50-MP1.0-N02MS" ) entered as an array formula Control-shift enter "Justlearnin" wrote: I am trying to type a simple if.then formula, but would like to evaluate an array of cells for the 2nd condition of the and statement. How do you designate this in the formula? Here is what I currently have: =CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS"))) If you have a better way to accomplish this task, any ideas would be appreciated. Thank You in advance for the help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining an array to search by
First of all, your formula is an *array* formula, and therefore requires a
CSE entry. However, it's not clear what you're looking to concatenate. There's *no* difference if F4 is either 2 or 3 ... is that correct? Does this *array* formula work for you: =IF(AND(OR(F4={2,3}),J4:R4="G"),"K40-MP1.0-N01MS"&" "&"K50-MP1.0-N02MS","") -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Justlearnin" wrote in message ... I am trying to type a simple if.then formula, but would like to evaluate an array of cells for the 2nd condition of the and statement. How do you designate this in the formula? Here is what I currently have: =CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS"))) If you have a better way to accomplish this task, any ideas would be appreciated. Thank You in advance for the help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining an array to search by
Thank you for the help. I copied this formula from some other cells that I
was using the CAT command on and forgot to remove it for this one. You are correct, 2 or 3 does not change the outcome of the answer, so your format improves the code. 1 more question: When do you execute the CSE? is that BEFORE you type in the formula or AFTER. Thanks "RagDyeR" wrote: First of all, your formula is an *array* formula, and therefore requires a CSE entry. However, it's not clear what you're looking to concatenate. There's *no* difference if F4 is either 2 or 3 ... is that correct? Does this *array* formula work for you: =IF(AND(OR(F4={2,3}),J4:R4="G"),"K40-MP1.0-N01MS"&" "&"K50-MP1.0-N02MS","") -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Justlearnin" wrote in message ... I am trying to type a simple if.then formula, but would like to evaluate an array of cells for the 2nd condition of the and statement. How do you designate this in the formula? Here is what I currently have: =CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS"))) If you have a better way to accomplish this task, any ideas would be appreciated. Thank You in advance for the help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining an array to search by
This is how I suggest you "get" formulas from these groups:
Select the formula in the post. Right click in the selection and choose "Copy". Click in the cell in your WB where you wish this formula to reside, THEN, Click in the formula bar, and immediately right click, Choose "Paste" NOW ... *examine* the formula in the formula bar. The blinking "place holder" should be *immediately* at the end of the last character in the formula. At this point, you either hit <Enter for a regular formula, or <Ctrl <Shift <Enter for an array formula. *IF* the formula is long, and takes up more then a single line in the formula bar, you must make sure that each line in the formula bar is "full", without a lot of empty space, signifying unwanted, invisible characters. If you see a great deal of empty space in each line, start backwards, and position the place holder at the end of each line and hit <Delete. This should compact the formula to *full* lines in the formula bar, at which point you hit <E or <CSE. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Justlearnin" wrote in message ... Thank you for the help. I copied this formula from some other cells that I was using the CAT command on and forgot to remove it for this one. You are correct, 2 or 3 does not change the outcome of the answer, so your format improves the code. 1 more question: When do you execute the CSE? is that BEFORE you type in the formula or AFTER. Thanks "RagDyeR" wrote: First of all, your formula is an *array* formula, and therefore requires a CSE entry. However, it's not clear what you're looking to concatenate. There's *no* difference if F4 is either 2 or 3 ... is that correct? Does this *array* formula work for you: =IF(AND(OR(F4={2,3}),J4:R4="G"),"K40-MP1.0-N01MS"&" "&"K50-MP1.0-N02MS","") -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Justlearnin" wrote in message ... I am trying to type a simple if.then formula, but would like to evaluate an array of cells for the 2nd condition of the and statement. How do you designate this in the formula? Here is what I currently have: =CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS"))) If you have a better way to accomplish this task, any ideas would be appreciated. Thank You in advance for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Search in an Array | Excel Worksheet Functions | |||
Text Search in an Array | Excel Discussion (Misc queries) | |||
How do I search an array for values in a column greater than zero | Excel Worksheet Functions | |||
Search array and return element No | Excel Worksheet Functions | |||
Search a random array of cells and return a value of "X" | Excel Worksheet Functions |