Nested IF in this situation

[Cossminnn]

I'm trying to make a function for a test evaluation. I have 3 columns
with Tests, one with Exam and another one with the final Score.
Now, in the final column is the Situation of the students and it should
look like this:
- if the student passed all the tests and the exam (scoring at least 5
on each), then under Situation will appear the final score.
- if the student fails one of the tests or the exam (scoring under 5),
then it should say "Rejected".
I've tried using the nested IF, but I keep getting an error ("You've
entered too many arguments for this function").
The formula I used is this:
=IF(F2>=5;F2;"Rejected";IF(B2<5;"Rejected";F2;IF(C2<5;"Rejected";F2;IF(D2<5;"Rejected";F2;IF(E2<5;"Rejected";F2)))))


+-------------------------------------------------------------------+
|Filename: Situation.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4792 |
+-------------------------------------------------------------------+

 

[Alex]

Cossminn

Maybe I have misuderstood what you are trying to achieve but from what you
have written:

If a student fails the final exam or any one test then they are 'rejected'.
Put another way for a student not to be rejected they must have passed all
the tests and the final exam?

If this is the case then this will work:

=IF(AND(C2>=5,D2>=5,E2>=5,F2>=5),F2,"REJECTED")

If a student has scored 5 or more on all tests and exams then the final
score on the exam will be displayed (F2). If a student failed any test or the
exam then they are assigned 'REJECTED'.

Does this fit your needs?

Alex

 

[Cossminnn]

Does this fit your needs?

Yes, that's it. Thanks a lot.

One more question though. I've seen on this forum and on other sites
that the formulas are written with commas (,), while all the versions
of Office I've worked with need semicolons (. Is my version
different, or that's just the way people prefer to write it?

 

[Alex]

Cossminn

As far as I am aware the ',' is standard practice. I have never used a ';'
and infact it isn't valid on my computer (i just tried a simple IF statement
using ';' and it is invalid).

Convention suggests a ',' but whatever works for you.

Finally, may I make a polite request. Once a question has been solved it is
usual that the user will click 'was this post useful' button to place a
'tick' next to the original quesry to denote that the problem is now solved.

Regards

Alex

 

[DS-NTE]

It depends on the language, in my Norwegian version of Excel I have to use
this in a worksheet:

=HVIS(OG(C2>=5;D2>=5;E2>=5;F2>=5);F2;"REJECTED")

thats because we use , as decimalseparator- (e.g.123,45 but an american will
use 123.45) and Excel dont recognise , between the arguments in a formula,
but ; works.

But if I work in VBA I hav to use . as a decimalseparator.

confused,,..;;??

knut

 

[Bob Phillips]

No, it just means that you have a continental version of Excel, where the
delimiter is a semi-colon;, whereas the rest of us (UK, USA, Australia at
least) have a comma delimiter. I think the continental version was changed
to semi-colon is because on the continent, comma is the decimal separator,
whereas we use a dot separator.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Ron Rosenfeld]

I'm trying to make a function for a test evaluation. I have 3 columns
with Tests, one with Exam and another one with the final Score.
Now, in the final column is the Situation of the students and it should
look like this:
- if the student passed all the tests and the exam (scoring at least 5
on each), then under Situation will appear the final score.
- if the student fails one of the tests or the exam (scoring under 5),
then it should say "Rejected".
I've tried using the nested IF, but I keep getting an error ("You've
entered too many arguments for this function").
The formula I used is this:
=IF(F2>=5;F2;"Rejected";IF(B2<5;"Rejected";F2;IF(C2<5;"Rejected";F2;IF(D2<5;"Rejected";F2;IF(E2<5;"Rejected";F2)))))


+-------------------------------------------------------------------+
|Filename: Situation.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4792 |
+-------------------------------------------------------------------+

=IF(COUNTIF(A22,">="&5)=4,SUM(A22),"Rejected")

You'll need to change the "," to ";" for your version of Excel.


--ron