School Exam Grades

[xenolith01]

Hi, I need to analyse some data after recent A'Level and GCSE results day.
The formula I need to create has the following data:
10 Columns - A to J
Multiple Rows relating to each pupil. Pupil name in column A.
Columns B to J contain a mixture of the following grades:
A*, A, B, C, D, E, F ,G, U
A* means A star and therefore needs a tilde to escape the asterix - A~*
I need to calculate if each pupil (i.e each row) has achieved 5 x A*to C grades but only if column C and H have an A* to C grade. The result could simply be a 1 or 0 in another column e.g. column K.
Basically:
If cell C1&H1 contain A* to C grades and 3 other A* to C grades in any of the other cells then column K = 1 else 0.
I have only used 10 columns for this example but in reality there could be several more, therefore the formula needs to be as condensed as possible.
I've tried working along the lines of =IF(AND(COUNTIF(C1,{"A~*","A","B","C"}),COUNTIF(H1,{"A~*","A","B","C"})...........But getting lost
Hard to explain but hopefully someone gets it!!

Thanks

 

[xenolith01]

Markmzz - that's great. I didn't know the asterix could mean 'AND'. I assumed asterix was only used for multiplying and Wildcards (I've got a lot to learn!).
Cirledchicken - the 'A*' was a good point too. I had been using 'A~*' to count the number of A stars obtained in a whole column, so got a bit stuck in that way of thinking!!
Could you point me to some good links either in this website or elsewhere that has explanations of the use of asterix's etc. There's hundreds of tutorials on the web but it would be good to be pointed to some of the better ones!!

All the best

 

[markmzz]

A little small formula:

Note: VERDADEIRO = TRUE and FALSO = FALSE.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Pupil01</td><td style="text-align: center;;">E</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">G</td><td style="text-align: center;;">D</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">E</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Pupil02</td><td style="text-align: center;;">A</td><td style="text-align: center;;">H</td><td style="text-align: center;;">A</td><td style="text-align: center;;">H</td><td style="text-align: center;;">H</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A</td><td style="text-align: center;;">U</td><td style="text-align: center;;">G</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Pupil03</td><td style="text-align: center;;">F</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">C</td><td style="text-align: center;;">F</td><td style="text-align: center;;">D</td><td style="text-align: center;;">U</td><td style="text-align: center;;">A</td><td style="text-align: center;;">E</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Pupil04</td><td style="text-align: center;;">E</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">H</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">U</td><td style="text-align: center;;">A</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Pupil05</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">G</td><td style="text-align: center;;">U</td><td style="text-align: center;;">G</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">A</td><td style="text-align: center;;">G</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Pupil06</td><td style="text-align: center;;">D</td><td style="text-align: center;;">A</td><td style="text-align: center;;">E</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">F</td><td style="text-align: center;;">U</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Pupil07</td><td style="text-align: center;;">F</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A</td><td style="text-align: center;;">F</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">D</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Pupil08</td><td style="text-align: center;;">B</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">C</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Pupil09</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">C</td><td style="text-align: center;;">D</td><td style="text-align: center;;">E</td><td style="text-align: center;;">A</td><td style="text-align: center;;">D</td><td style="text-align: center;;">C</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Pupil10</td><td style="text-align: center;;">D</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">D</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">E</td><td style="text-align: center;;">F</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">U</td><td style="text-align: center;;">U</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=1*(<font color="Blue">OR(<font color="Red">C1={"A*","A","B","C"}</font>)*OR(<font color="Red">H1={"A*","A","B","C"}</font>)*SUM(<font color="Red">COUNTIF(<font color="Green">B1:J1,{"A*","B","C"}</font>)</font>)>4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L1</th><td style="text-align:left">=(<font color="Blue">OR(<font color="Red">C1={"A*","A","B","C"}</font>)*OR(<font color="Red">H1={"A*","A","B","C"}</font>)*SUM(<font color="Red">COUNTIF(<font color="Green">B1:J1,{"A*","B","C"}</font>)</font>)>4</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[xenolith01]

Thanks all of you those formulae were a great help and enabled me to get the stats I needed with a few modifications. Also helped in the brain cells dept!!
Could some one tell me how I would change this formula so that
Either column A1 or D1 can contain the correct grade in a total of 5 x A* to C grades.

I'm trying to combine both these formulae in an 'OR' to get a final yes/no if either are true. (can't seem to get brackets right with the first IF and OR!


=IF(AND(OR(A1={"A*","A","B","C"}),IF(SUM(COUNTIF(A1:E1,{"A~*","A","B","C"}))>=5,1,0)),"YES","NO")

OR

=IF(AND(OR(D1={"A*","A","B","C"}),IF(SUM(COUNTIF(A1:E1,{"A~*","A","B","C"}))>=5,1,0)),"YES","NO")


Thanks again.

 

[xenolith01]

Actually is this right?

=IF(AND(OR(A1={"A*","A","B","C"},D1={"A*","A","B","C"}),IF(SUM(COUNTIF(A1:F1,{"A~*","A","B","C"}))>=5,1,0)),"YES","NO")

Seems to test ok but I'm worried I'm missing something obvious!!

Thanks

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">G</td><td style="text-align: center;;">E</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">G</td><td style="text-align: center;;">D</td><td style="text-align: center;;">D</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A</td><td style="text-align: center;;">U</td><td style="text-align: center;;">C</td><td style="text-align: center;;">YES</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">H</td><td style="text-align: center;;">F</td><td style="text-align: center;;">E</td><td style="text-align: center;;">H</td><td style="text-align: center;;">C</td><td style="text-align: center;;">U</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">A</td><td style="text-align: center;;">E</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">A</td><td style="text-align: center;;">C</td><td style="text-align: center;;">YES</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">G</td><td style="text-align: center;;">A</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">E</td><td style="text-align: center;;">D</td><td style="text-align: center;;">A</td><td style="text-align: center;;">E</td><td style="text-align: center;;">A</td><td style="text-align: center;;">G</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">E</td><td style="text-align: center;;">C</td><td style="text-align: center;;">C</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">C</td><td style="text-align: center;;">B</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">D</td><td style="text-align: center;;">YES</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">A</td><td style="text-align: center;;">C</td><td style="text-align: center;;">D</td><td style="text-align: center;;">YES</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">D</td><td style="text-align: center;;">A</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">B</td><td style="text-align: center;;">A*</td><td style="text-align: center;;">B</td><td style="text-align: center;;">NO</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A1={"A*","A","B","C"}</font>)*OR(<font color="Red">D1={"A*","A","B","C"}</font>)*SUM(<font color="Red">COUNTIF(<font color="Green">A1:F1,{"A*","B","C"}</font>)</font>)>4,"YES","NO"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz