Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
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(C 2<5;"Rejected";F2;IF(D2<5;"Rejected";F2;IF(E2<5;"R ejected";F2))))) +-------------------------------------------------------------------+ |Filename: Situation.zip | |Download: http://www.excelforum.com/attachment.php?postid=4792 | +-------------------------------------------------------------------+ -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
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" wrote: 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(C 2<5;"Rejected";F2;IF(D2<5;"Rejected";F2;IF(E2<5;"R ejected";F2))))) +-------------------------------------------------------------------+ |Filename: Situation.zip | |Download: http://www.excelforum.com/attachment.php?postid=4792 | +-------------------------------------------------------------------+ -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
Alex Wrote: 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? -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
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 "Cossminnn" wrote: Alex Wrote: 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? -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
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 "Cossminnn" skrev i melding ... Alex Wrote: 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? -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
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) "Cossminnn" wrote in message ... Alex Wrote: 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? -- Cossminnn ------------------------------------------------------------------------ Cossminnn's Profile: http://www.excelforum.com/member.php...o&userid=34309 View this thread: http://www.excelforum.com/showthread...hreadid=543926 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF in this situation
On Sat, 20 May 2006 01:31:21 -0500, Cossminnn
wrote: 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(A2:D2,"="&5)=4,SUM(A2:D2),"Rejected") You'll need to change the "," to ";" for your version of Excel. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exceeding the limit for Nested IFs | Excel Worksheet Functions | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions | |||
€śUse of more then 7 nested if statement€ť | Excel Worksheet Functions |