MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fail Result Detector...


Posted by NiuB on July 09, 2001 10:55 AM

I got a file that have range from A1:I1. In A1 is item ,
in B1 is name , in C1 is english marks, in D1 is english grade ,
in E1 is history marks, in F1 is history grades , in G1 is geograhy
marks and in H1 is georaphy grades. In I1 is the result whether the student
is fail or pass.

How can I detect the result in I1 based on the range between C1:H1 to detect whether the student fail or
pass. If there any under 39 marks , the result in I1 will show "FAIL". If the marks is 40 and above, the
result will show 'PASS'


Many thanks


Posted by Barrie Davidson on July 09, 2001 11:05 AM

Try this formula, if I understand your requirements it will work for you:
=IF(COUNTIF(C1:H1,">=40")>0,"PASS","FAIL")

Regards,
Barrie

Posted by Aladin Akyurek on July 09, 2001 11:10 AM

Barrie means of course (as I understand the case as he does):

=IF(COUNTIF(C1:H1,"<=39")=1,"FAIL","PASS")

Aladin : I got a file that have range from A1:I1. In A1 is item ,


Posted by NiuB on July 09, 2001 11:11 AM

Thanks Barrie..but its not working..
I try to change the data in cell C1 from 39
to 40..but the result still remains "FAIL"
any ideas...

Posted by Barrie Davidson on July 09, 2001 11:14 AM

Do you have your calculation option set to manual? Try F9 to recalculate your spreadsheet, I tried the formula on a sample and it worked okay for me.

Regards,
Barrie

Posted by NiuB on July 09, 2001 11:16 AM

Its also not working Aladin..I wonder why..
At first I put the marks all to 100 and when
I paste your formula, it returns to PASS.but
when i change 1 of the marks to 39 , it still
shows PASS..any ideas..what I need to do is
when there is only 1 cells shows 39 or below,
it will return FAIL..many thanks again

Barrie means of course (as I understand the case as he does): =IF(COUNTIF(C1:H1,"<=39")=1,"FAIL","PASS") Aladin


Posted by Aladin Akyurek on July 09, 2001 11:22 AM

It must be:

posted by Aladin Akyurek on July 09, 2001 at 11:19:59:

=IF(COUNTIF(C1:H1,"<=39")>=1,"FAIL","PASS")

Aladin

Something this simple... How about that, Barrie?

Posted by Barrie Davidson on July 09, 2001 11:25 AM

Re: It must be:

Aladin Something this simple... How about that, Barrie?

I think he has options set to manual calculation. All our formulas should work (did on my mock-up).

Barrie :)

Posted by NiuB on July 09, 2001 11:32 AM

Re: It must be: Sorry to say..but it still not working..lol..but thanks guy..i've already got a headache..

Posted by Aladin Akyurek on July 09, 2001 11:32 AM

Re: It must be:

Even if there is a single score below 40, the result must be FAIL. Is it not?

Aladin

Posted by NiuB on July 09, 2001 11:34 AM

Re: It must be: Yup..you are right Aladin..but its still not working..

Posted by Aladin on July 09, 2001 11:35 AM

Please bear with us...


NiuB,

Would you care to post a few rows of scores from the range C1:H1 along with expected result?

Aladin

Posted by Aladin Akyurek on July 09, 2001 11:38 AM

What is the format of C1:H1? Text? (NT)

Posted by NiuB on July 09, 2001 11:44 AM

Thanks a zillion guys..Barries right..after hit F9..its ok..but still one problem occured..inside..

=IF(COUNTIF(C2:H2,"<=39")=1,"FAIL","PASS")

with above formula, if i dont filled any marks
in c2:h2..it still shows PASS..how can it will
shows nothing if no data is entered..thanks again
and very sorry to burden you guys..you guys are
awesome..many thanks

NiuB, Would you care to post a few rows of scores from the range C1:H1 along with expected result? Aladin

Posted by Aladin Akyurek on July 09, 2001 11:50 AM

Re: Thanks a zillion guys..Barries right..after hit F9..its ok..but still one problem occured..inside..

=IF(COUNT(C2:H2)=0,"",=IF(COUNTIF(C2:H2,"<=39")>=1,"FAIL","PASS"))

NiuB: watch out for >=1 (not simply =1)

Aladin

=IF(COUNTIF(C2:H2,"<=39")=1,"FAIL","PASS") with above formula, if i dont filled any marks in c2:h2..it still shows PASS..how can it will shows nothing if no data is entered..thanks again and very sorry to burden you guys..you guys are awesome..many thanks

Posted by NiuB on July 09, 2001 12:01 PM

Re: Thanks a zillion guys..Barries right..after hit F9..its ok..but still one problem occured..inside..

Aladin..why its say the formula that you
give contain an error when I paste it..and the
cursor will blink in front of the 3rd equal
sign, the = in front of the 2nd IF..i dont know
what am i doing wrong this time..this things
driving me crazy..lol..

=IF(COUNT(C2:H2)=0,"",=IF(COUNTIF(C2:H2,"<=39")>=1,"FAIL","PASS")) NiuB: watch out for >=1 (not simply =1) Aladin

Posted by Barrie Davidson on July 09, 2001 12:07 PM

Re: Thanks a zillion guys..Barries right..after hit F9..its ok..but still one problem occured..inside..

Take out that equal sign and all will be good.

Barrie Aladin..why its say the formula that you give contain an error when I paste it..and the cursor will blink in front of the 3rd equal sign, the = in front of the 2nd IF..i dont know what am i doing wrong this time..this things driving me crazy..lol..

Posted by Aladin Akyurek on July 09, 2001 12:10 PM

Yep. The second one... (NT)

Barrie : Aladin..why its say the formula that you : give contain an error when I paste it..and the : cursor will blink in front of the 3rd equal : sign, the = in front of the 2nd IF..i dont know : what am i doing wrong this time..this things : driving me crazy..lol..

Posted by NiuB on July 09, 2001 12:12 PM

FINALLY..huh...it works perfectly..top ten..hehe..what can I say.zillion thanks to Aladin and Barrie..You guys rock..hehe...regards guys..

Barrie : Aladin..why its say the formula that you : give contain an error when I paste it..and the : cursor will blink in front of the 3rd equal : sign, the = in front of the 2nd IF..i dont know : what am i doing wrong this time..this things : driving me crazy..lol..