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

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

"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

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.

--
Regards,

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 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

"Biff" wrote:

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

"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

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:

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.

--
Regards,

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

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Chrissi" wrote in message
...
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:

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.

--
Regards,

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

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(B2:D2,"=50")=2,"PASS","FAIL")
3 Name2 66 55 55
=IF(COUNTIF(B3:D3,"=50")=2,"PASS","FAIL")
4 Name3 23 76 65
=IF(COUNTIF(B4:D4,"=50")=2,"PASS","FAIL")
5 Name4 66 45 35
=IF(COUNTIF(B5:D5,"=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(B2:D2,"="&$H$1)=$H$2,"PASS","FAIL")
3 Name2 66 55 55
=IF(COUNTIF(B3:D3,"="&$H$1)=$H$2,"PASS","FAIL")
4 Name3 23 76 65
=IF(COUNTIF(B4:D4,"="&$H$1)=$H$2,"PASS","FAIL")
5 Name4 66 45 35
=IF(COUNTIF(B5:D5,"="&$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 :-)
------------------------------*------------------------------*----------------


"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

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

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