| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement with AND Statement
I have a Statement that Shawn Devonshire helped me setup.
Suppose you set up a table like this starting in cell A1 A B C Age Male Female 8 18 12 10 20 14 12 22 16 14 24 18 16 26 20 In this example I will keep the table on the same sheet as the student scores. Suppose the students are listed like this starting in cell E1: E F G H I Gender Age Left Curls Right Curls Pass/Fail Female 10 18 18 Pass Male 12 17 17 Fail Shawn assisted me in determining that the formula in cell I2 for Pass/Fail Left curls is. This is the one I am using for a single arm curl match. =IF(G2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0) ,TRUE),"Pass","Fail") I need help taking this statement to determine if I can use a "and" or "or" statement to do a Pass/Fail test on both left and right arm curls. I am not sure which statement will work best. Only if both left and right arm curls meet the standard in A1 table will a pass be given otherwise a fail will be given for the test, if either arm fails to meet the standard. Thanks Ken Quick |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement with AND Statement
You can add an OR statement then array enter** :
=IF(OR(G2:H2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$ C$1,0))),"Pass","Fail") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Ken Quick" wrote in message ... I have a Statement that Shawn Devonshire helped me setup. Suppose you set up a table like this starting in cell A1 A B C Age Male Female 8 18 12 10 20 14 12 22 16 14 24 18 16 26 20 In this example I will keep the table on the same sheet as the student scores. Suppose the students are listed like this starting in cell E1: E F G H I Gender Age Left Curls Right Curls Pass/Fail Female 10 18 18 Pass Male 12 17 17 Fail Shawn assisted me in determining that the formula in cell I2 for Pass/Fail Left curls is. This is the one I am using for a single arm curl match. =IF(G2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0) ,TRUE),"Pass","Fail") I need help taking this statement to determine if I can use a "and" or "or" statement to do a Pass/Fail test on both left and right arm curls. I am not sure which statement will work best. Only if both left and right arm curls meet the standard in A1 table will a pass be given otherwise a fail will be given for the test, if either arm fails to meet the standard. Thanks Ken Quick |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement with AND Statement
Try this:
=IF(MIN(G2:H2)=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1 :$C$1,0),TRUE),"Pass","Fail") "Ken Quick" wrote: I have a Statement that Shawn Devonshire helped me setup. Suppose you set up a table like this starting in cell A1 A B C Age Male Female 8 18 12 10 20 14 12 22 16 14 24 18 16 26 20 In this example I will keep the table on the same sheet as the student scores. Suppose the students are listed like this starting in cell E1: E F G H I Gender Age Left Curls Right Curls Pass/Fail Female 10 18 18 Pass Male 12 17 17 Fail Shawn assisted me in determining that the formula in cell I2 for Pass/Fail Left curls is. This is the one I am using for a single arm curl match. =IF(G2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0) ,TRUE),"Pass","Fail") I need help taking this statement to determine if I can use a "and" or "or" statement to do a Pass/Fail test on both left and right arm curls. I am not sure which statement will work best. Only if both left and right arm curls meet the standard in A1 table will a pass be given otherwise a fail will be given for the test, if either arm fails to meet the standard. Thanks Ken Quick |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement with AND Statement
Only if both left and right arm curls meet the standard
Ooops! Disregard my suggestion. I used "or" instead of "and". -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can add an OR statement then array enter** : =IF(OR(G2:H2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$ C$1,0))),"Pass","Fail") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Ken Quick" wrote in message ... I have a Statement that Shawn Devonshire helped me setup. Suppose you set up a table like this starting in cell A1 A B C Age Male Female 8 18 12 10 20 14 12 22 16 14 24 18 16 26 20 In this example I will keep the table on the same sheet as the student scores. Suppose the students are listed like this starting in cell E1: E F G H I Gender Age Left Curls Right Curls Pass/Fail Female 10 18 18 Pass Male 12 17 17 Fail Shawn assisted me in determining that the formula in cell I2 for Pass/Fail Left curls is. This is the one I am using for a single arm curl match. =IF(G2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0) ,TRUE),"Pass","Fail") I need help taking this statement to determine if I can use a "and" or "or" statement to do a Pass/Fail test on both left and right arm curls. I am not sure which statement will work best. Only if both left and right arm curls meet the standard in A1 table will a pass be given otherwise a fail will be given for the test, if either arm fails to meet the standard. Thanks Ken Quick |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement with AND Statement
T. Valko,
I tried this but the formula did a calculation that gave a pass when either left or right curls pass the standard. I need it to only pass when the standard is meet for both arms. Any Advice? Thanks for the assistance, Ken "T. Valko" wrote: You can add an OR statement then array enter** : =IF(OR(G2:H2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$ C$1,0))),"Pass","Fail") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Ken Quick" wrote in message ... I have a Statement that Shawn Devonshire helped me setup. Suppose you set up a table like this starting in cell A1 A B C Age Male Female 8 18 12 10 20 14 12 22 16 14 24 18 16 26 20 In this example I will keep the table on the same sheet as the student scores. Suppose the students are listed like this starting in cell E1: E F G H I Gender Age Left Curls Right Curls Pass/Fail Female 10 18 18 Pass Male 12 17 17 Fail Shawn assisted me in determining that the formula in cell I2 for Pass/Fail Left curls is. This is the one I am using for a single arm curl match. =IF(G2=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0) ,TRUE),"Pass","Fail") I need help taking this statement to determine if I can use a "and" or "or" statement to do a Pass/Fail test on both left and right arm curls. I am not sure which statement will work best. Only if both left and right arm curls meet the standard in A1 table will a pass be given otherwise a fail will be given for the test, if either arm fails to meet the standard. Thanks Ken Quick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum if statement with a left statement | Excel Discussion (Misc queries) | |||
| SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
| Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
Linear Mode
