Formula in Marksheet

[Salza]

Having a problem to get a correct formula for overall passing.

Marks are keyed into the following cells for all the 26 subjects.

Cell F8 = Subject 1
Cell H8-R8 = Subjects 2-7
Cell T8-BE8 = Subject 8-26

To pass the overall exam - a student must pass FIVE subjects (Subject
1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

Passing mark for each subject is 36.

Waht is the correct formula? I used the formula below but doesn’t
work.

=IF(B8="","",IF(AND(F8>35,COUNTIF(H8:S8,">35")>=2),COUNTIF(T8:BE8,">35")>=2)=TRUE,"Pass","Fail"))))
* B8 is the student’s name.


Thank you.

 

[Pete_UK]

I think you need to do this:

=IF(B8="","",IF(AND(F8>35,COUNTIF(H8:S8,">35")>=2,COUNTIF(T8:BE8,">35")>=2­),"Pass","Fail"))

Hope this helps.

Pete

 

[Salza]

I think you need to do this:

=IF(B8="","",IF(AND(F8>35,COUNTIF(H8:S8,">35")>=2,COUNTIF(T8:BE8,">35")>=2­),"Pass","Fail"))

Hope this helps.

Pete

No, Pete. It still doesn't work. It says the formula I typed contains
an error.
Any other suggestion?

 

[Herbert Seidenberg]

There is a soft hyphen (Alt+0173) hidden after the 2 in

 

[Salza]

There is a soft hyphen (Alt+0173) hidden after the 2 in

I don't get it... What is that soft hypen for? It still doesn't work.
Same message..... It says the formula I typed contains
an error.

Can you suggest a new formula? Thank you.

 

[Herbert Seidenberg]

Advanced Excel 2007 Tables.
Pass/Fail test.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_17_10.xlsx
Pdf preview:
http://www.mediafire.com/file/kmwglu5yxtw/05_17_10.pdf

 

[Steve Dunn]

Salza,

neither Pete's, nor even your original formula contained any errors, so the
problem must lie elsewhere. However, try this and report back to us:

=IF((F8>35)*(COUNTIF(H8:R8,">35")>=2)*
(COUNTIF(T8:BE8,">35")>=2),"Pass","Fail")

BTW, I've no idea what Herbert is up to, but invisible/soft hyphens have
nothing at all to do with this, and I certainly wouldn't open either of his
files.

Steve D.

I think you need to do this:

=IF(B8="","",IF(AND(F8>35,COUNTIF(H8:S8,">35")>=2,COUNTIF(T8:BE8,">35")>=2­),"Pass","Fail"))

Hope this helps.

Pete

No, Pete. It still doesn't work. It says the formula I typed contains
an error.
Any other suggestion?