Nesting "If" in Excel
 

Hi, I am a student teacher using Excel 2003 for my marking. So far, I have
not had too many problems writing formulas but this one has me stumped.
To get a pass in their snap tests the students must pass 2 out of three
tests. I list them as Test 1, Test 2, and Test 3 with their names down the
side.
So I need to write a formula to test for all possible outcomes that will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1 and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If" "And"
"Or" and I can get some of them to work but not all. Would SKS please help.
Regards Chrissi

You don't mention what is the criteria in the individual cells that signify
a "pass".
Is it the text string "Pass", or is it a numerical result of the actual test
score, where a certain number must be attained to equate a "pass"?
If it IS a number, what value constitutes "pass".

Just guessing that the text "pass" will be entered into the individual
cells.

With names in Column A, and Test 1, 2, and 3 in Columns B, C, and D
respectively, try this formula in E2, and copy down as needed:

=IF(COUNTIF(B2:D2,"Pass")=2,"PASS","FAIL")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Chrissi" wrote in message
...
Hi, I am a student teacher using Excel 2003 for my marking. So far, I have
not had too many problems writing formulas but this one has me stumped.
To get a pass in their snap tests the students must pass 2 out of three
tests. I list them as Test 1, Test 2, and Test 3 with their names down the
side.
So I need to write a formula to test for all possible outcomes that will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1 and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If"
"And"
"Or" and I can get some of them to work but not all. Would SKS please
help.
Regards Chrissi

Hi Ragdyer, I'm sorry about cross-posting, I wasnt sure where to put my
problem as I am a new user as well. I have been told I neglected to put in
what the marks were. The kids have a spelling test on mon, wed and fri and I
enter them out of 100, 50 being the pass mark. They must get more than 50
twice each week to pass. Thank you your interest I will put this answer in
new user as well and not cross post again. Sorry. Chrissi

"Ragdyer" wrote:

You don't mention what is the criteria in the individual cells that signify
a "pass".
Is it the text string "Pass", or is it a numerical result of the actual test
score, where a certain number must be attained to equate a "pass"?
If it IS a number, what value constitutes "pass".

Just guessing that the text "pass" will be entered into the individual
cells.

With names in Column A, and Test 1, 2, and 3 in Columns B, C, and D
respectively, try this formula in E2, and copy down as needed:

=IF(COUNTIF(B2:D2,"Pass")=2,"PASS","FAIL")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Chrissi" wrote in message
...
Hi, I am a student teacher using Excel 2003 for my marking. So far, I have
not had too many problems writing formulas but this one has me stumped.
To get a pass in their snap tests the students must pass 2 out of three
tests. I list them as Test 1, Test 2, and Test 3 with their names down the
side.
So I need to write a formula to test for all possible outcomes that will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1 and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If"
"And"
"Or" and I can get some of them to work but not all. Would SKS please
help.
Regards Chrissi

Try this


="Student "&IF(COUNTIF(B2:D2,"=50")1,"passed","failed" )

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chrissi" wrote in message
...
Hi Ragdyer, I'm sorry about cross-posting, I wasnt sure where to put my
problem as I am a new user as well. I have been told I neglected to put in
what the marks were. The kids have a spelling test on mon, wed and fri and
I
enter them out of 100, 50 being the pass mark. They must get more than 50
twice each week to pass. Thank you your interest I will put this answer in
new user as well and not cross post again. Sorry. Chrissi

"Ragdyer" wrote:

You don't mention what is the criteria in the individual cells that
signify
a "pass".
Is it the text string "Pass", or is it a numerical result of the actual
test
score, where a certain number must be attained to equate a "pass"?
If it IS a number, what value constitutes "pass".

Just guessing that the text "pass" will be entered into the individual
cells.

With names in Column A, and Test 1, 2, and 3 in Columns B, C, and D
respectively, try this formula in E2, and copy down as needed:

=IF(COUNTIF(B2:D2,"Pass")=2,"PASS","FAIL")
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Chrissi" wrote in message
...
Hi, I am a student teacher using Excel 2003 for my marking. So far, I
have
not had too many problems writing formulas but this one has me
stumped.
To get a pass in their snap tests the students must pass 2 out of
three
tests. I list them as Test 1, Test 2, and Test 3 with their names down
the
side.
So I need to write a formula to test for all possible outcomes that
will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1
and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If"
"And"
"Or" and I can get some of them to work but not all. Would SKS please
help.
Regards Chrissi

A simple method would be to have a separate column for each test and to
express a pass as 1 and a fail as 0.

Assuming these to be columns A, B and C respectively you would need another
column with the formula -

=IF(a1+b1+c11,"Student passed","Student failed")

This is simpler than nesting IF statements with AND / ORs which can be
fraught with problems -as you've already found.

Regards.

Bill Ridgeway
Computer Solutions

"Chrissi" wrote in message
...
Hi, I am a student teacher using Excel 2003 for my marking. So far, I have
not had too many problems writing formulas but this one has me stumped.
To get a pass in their snap tests the students must pass 2 out of three
tests. I list them as Test 1, Test 2, and Test 3 with their names down the
side.
So I need to write a formula to test for all possible outcomes that will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1 and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If"
"And"
"Or" and I can get some of them to work but not all. Would SKS please
help.
Regards Chrissi

Chrissi,

If I understand you, 50 is a fail, so you would change Bob's "=50" to
"50", other wise you'll be passing 50's. Sorry kids, spell better next time!

Roy
--
(delete .nospam)




"Bob Phillips" wrote:

Try this


="Student "&IF(COUNTIF(B2:D2,"=50")1,"passed","failed" )

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chrissi" wrote in message
...
Hi Ragdyer, I'm sorry about cross-posting, I wasnt sure where to put my
problem as I am a new user as well. I have been told I neglected to put in
what the marks were. The kids have a spelling test on mon, wed and fri and
I
enter them out of 100, 50 being the pass mark. They must get more than 50
twice each week to pass. Thank you your interest I will put this answer in
new user as well and not cross post again. Sorry. Chrissi

"Ragdyer" wrote:

You don't mention what is the criteria in the individual cells that
signify
a "pass".
Is it the text string "Pass", or is it a numerical result of the actual
test
score, where a certain number must be attained to equate a "pass"?
If it IS a number, what value constitutes "pass".

Just guessing that the text "pass" will be entered into the individual
cells.

With names in Column A, and Test 1, 2, and 3 in Columns B, C, and D
respectively, try this formula in E2, and copy down as needed:

=IF(COUNTIF(B2:D2,"Pass")=2,"PASS","FAIL")
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Chrissi" wrote in message
...
Hi, I am a student teacher using Excel 2003 for my marking. So far, I
have
not had too many problems writing formulas but this one has me
stumped.
To get a pass in their snap tests the students must pass 2 out of
three
tests. I list them as Test 1, Test 2, and Test 3 with their names down
the
side.
So I need to write a formula to test for all possible outcomes that
will
result in a pass. Student can either pass Test 1 and Test 2 or Test 1
and
Test 3 or Test 2 and Test 3
I want to write the formulas so that the auto answer will be either "
Student Passed" or "Student Failed". I can understand that I need "If"
"And"
"Or" and I can get some of them to work but not all. Would SKS please
help.
Regards Chrissi