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

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

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

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

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