Nesting "If" in Excel

[Guest]

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

 

[Ragdyer]

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(B22,"Pass")>=2,"PASS","FAIL")

 

[Guest]

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

 

[Bob Phillips]

Try this


="Student "&IF(COUNTIF(B22,">=50")>1,"passed","failed")

--

HTH

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

 

[Bill Ridgeway]

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+c1>1,"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

 

[Guest]

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