| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a value if a range (not a cell) contains a certain v
In following an audit program steps a condition may result as "OK", "N",
"NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certain v
One way:
=IF(COUNTIF(A1:G1,"Y")0,"Fail","PASS") HTH, Paul "Clem" wrote in message ... In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certain v
Try this:
H2: =IF(COUNTIF(A2:D2,"Y"),"FAIL","PASS") Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Clem" wrote: In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certain v
=IF(COUNTIF(A1:G1,"Y")0,"FAIL","PASS")
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Clem" wrote in message ... In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certain v
i just wonder what happen @ "N" or "NA" since it may not mean "OK"...
"Clem" wrote: In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certa
Good question. I didn't set up the criteria, but am evolving the meaning of
these categories. "N" means the test was not done or no exception found, so the answer should not be "FAIL", and "NA" means there were no instances of the condition to test, so we again would not choose "FAIL," and again "PASS" is appropriate. It's not exactly intuitive, I agree. Thanks for asking. clem "rml" wrote: i just wonder what happen @ "N" or "NA" since it may not mean "OK"... "Clem" wrote: In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return a value if a range (not a cell) contains a certa
Yah Clem...since you are doing an AUDIT PROGRAM....the way mostly this is
done is thru a pessimistic rather than optimistic approach.....You may have a complete non-test "N" yet the result is already pre-defined as PASS...It may be striking for an auditor...hope u got the real point... "rml" wrote: i just wonder what happen @ "N" or "NA" since it may not mean "OK"... "Clem" wrote: In following an audit program steps a condition may result as "OK", "N", "NA", or "Y". I have a table with areas down the left and tests across the top: A B C D H Test1 Test2 Test3 etc. PASS/FAIL 1 Area1 OK OK Y FAIL 2 Area2 OK OK OK PASS etc I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look through the row to the left and return "FAIL" if there is any cell with "Y" in it, else "PASS". How do I get the range as the criteria range, not just one cell? I can get it to work if I say IF(A1="Y","FAIL","PASS"), but not IF(A1:G1="Y","FAIL","PASS"). So, I need the test to iterate through a range for the given condition, and return the value I seek if ever it encounters the trigger criteria. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
| Match function...random search? | Excel Worksheet Functions | |||
| Can I use formulas that return cell range ref. in charts X series | Charts and Charting in Excel | |||
| Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Linear Mode
