Formula in Marksheet

S

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

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
 
S

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
Click to expand...


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

Salza

There is a soft hyphen (Alt+0173) hidden after the 2 in
Click to expand...


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

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
Click to expand...


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Marksheet 4
Formula - Exam Marksheet 9
Formula in Marksheet 6
Conditional Formatting Formula 2
Exam Marksheet - Grading 1
numbers, text 1
Solution to tricky problem of showing exactly Top N rows in Pivottable 0
Formula - Marks in Group 1

Top