Help with nesting functions 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

 

[Biff]

Hi!

How is your data setup?

I list them as Test 1, Test 2, and Test 3 with their names down the side.

That's not a very good description! Be VERY specific!

How do you denote whether someone passed (or failed) a test? P? F?

Biff

 

[Ragdyer]

See the answer to your post in the newusers group.

Multi-posting is frowned upon in these groups.
All the groups are followed and read by the same people.

This eliminates the possibility of people wasting their time answering a
question that might already have been answered in another group.

 

[Guest]

Hi Biff, sorry I am new to this and didnt realise what I should specify. I
enter the marks beside each child's name out of 100 with a 50 being a pass,
under 50 being a fail. They have a spelling test each mon, wed and fri. Thank
you for your interest. Chrissi

 

[Guest]

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]

See the excellent suggestion Bob has given you in the newusers group that
complies with your updated criteria.

And FYI, to correct your terminology, what you did was "multi-post", *not*
"cross-post".
Cross-posting is *also* frowned upon, BUT ... is accepted by some, since it
publishes all answers to *all* the addressed groups, thus eliminating the
duplication of effort by the responders, since everyone can see *every*
answering post.in every group, concurrently.

 

[Ken Wright]

Cross posting isn't an issue, it's multiposting that is. Cross posting
allows you to reply to a message in one group and the answer will appear in
the others too.

As to your question, assuming you have it set up like this:-

A B C D E
1 Test1 Test2 Test3 Pass/Fail
2 Name1 36 45 55
=IF(COUNTIF(B22,">=50")>=2,"PASS","FAIL")
3 Name2 66 55 55
=IF(COUNTIF(B33,">=50")>=2,"PASS","FAIL")
4 Name3 23 76 65
=IF(COUNTIF(B44,">=50")>=2,"PASS","FAIL")
5 Name4 66 45 35
=IF(COUNTIF(B55,">=50")>=2,"PASS","FAIL")

Then Col E will give you what you want.

A better approach might aslo be to use a couple of cells to contain your
variables of 50 and 2 such that they can be easily changed if you should so
decide, eg assuming you use cell H1 to contain 50 and H2 to contain 2,
then:-

A B C D E
1 Test1 Test2 Test3 Pass/Fail
2 Name1 36 45 55
=IF(COUNTIF(B22,">="&$H$1)>=$H$2,"PASS","FAIL")
3 Name2 66 55 55
=IF(COUNTIF(B33,">="&$H$1)>=$H$2,"PASS","FAIL")
4 Name3 23 76 65
=IF(COUNTIF(B44,">="&$H$1)>=$H$2,"PASS","FAIL")
5 Name4 66 45 35
=IF(COUNTIF(B55,">="&$H$1)>=$H$2,"PASS","FAIL")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Ken Wright]

LOL - Hopefully you guessed that those formulas were supposed to be in
column E

Regards
Ken...............