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

 

[jbeaucaire]

Not very elegant, but seems to work:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	B	A*	C	D	C	B	A*	D	C	1
2	C	B	C	D	D	D	A*	C	C	1
3	C	B	C	D	D	D	A*	D	C	0
4	A	C	B	C	D	E	C	D	D	1
5	A	D	A	A	A	A	A*	A	A	0
Sheet1

 

[circledchicken]

Hi,

In K2, try this:

=IF((SUMPRODUCT(--($C2={"A~*";"A";"B";"C"}))*SUMPRODUCT(--($H2={"A~*";"A";"B";"C"}))*SUMPRODUCT(--COUNTIF($B2:$J2,{"A~*";"A";"B";"C"})))>=5,1,0)

where the part in bold could be expanded for however many columns you need to use.

 

[jbeaucaire]

This K1 formula is a tiny bit better than my first one above:

=IF(AND(ISNUMBER(SEARCH(LEFT(C1,1),"ABC")),ISNUMBER(SEARCH(LEFT(H1,1),"ABC"))), --(SUM(COUNTIF($B1:$J1,{"A*","B","C"}))>=5), 0)

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	B	A*	D	D	C	D	C	D	D	0
2	C	B	C	D	D	D	A*	C	C	1
3	C	B	C	D	D	D	A*	D	C	1
4	A	C	B	C	D	E	C	D	D	1
5	A	D	A	A	A	A	A*	A	A	0
6		E					E			0
Sheet1

 

[circledchicken]

Hi,

In K2, try this:

=IF((SUMPRODUCT(--($C2={"A~*";"A";"B";"C"}))*SUMPRODUCT(--($H2={"A~*";"A";"B";"C"}))*SUMPRODUCT(--COUNTIF($B2:$J2,{"A~*";"A";"B";"C"})))>=5,1,0)

where the part in bold could be expanded for however many columns you need to use.

Oops, sorry in the above I think you wouldn't have the ~ in the SUMPRODUCTs, but should in the COUNTIF:

i.e. =IF((SUMPRODUCT(--($C2={"A*";"A";"B";"C"}))*SUMPRODUCT(--($H2={"A*";"A";"B";"C"}))*SUMPRODUCT(COUNTIF($B2:$J2,{"A~*";"A";"B";"C"})))>=5,1,0)

 

[jbeaucaire]

Did you look? I used "A*" specifically to have that work for both versions of "A". The sample sheet above shows it working correctly.... Did you try it as is?

 

[markmzz]

Another way:

<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 /></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></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></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></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></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></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></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></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></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></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></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></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></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></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">Sheet1</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">=IF(<font color="Blue">OR(<font color="Red">C1={"A*","A","B","C"}</font>)*OR(<font color="Red">H1={"A*","A","B","C"}</font>),IF(<font color="Red">SUM(<font color="Green">COUNTIF(<font color="Purple">B1:J1,{"A*","B","C"}</font>)</font>)>=5,1,0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[xenolith01]

Thanks for all these suggestions.
Sorry but I'm only just getting into combining long formulae so if any of you could take the time to break down the logic (i.e. explain what the parts of your formula are doing). I'm a bit confused by some of the '--' parts and where OR statments are being multiplied with an asterix '*'.
I'd rather not just put in the formulas without understanding them.

Thanks

 

[markmzz]

Thanks for all these suggestions.
Sorry but I'm only just getting into combining long formulae so if any of you could take the time to break down the logic (i.e. explain what the parts of your formula are doing). I'm a bit confused by some of the '--' parts and where OR statments are being multiplied with an asterix '*'.
I'd rather not just put in the formulas without understanding them.

Thanks

Look at this formulas:

=IF(OR(C1={"A*","A","B","C"})*OR(H1={"A*","A","B","C"}),IF(SUM(COUNTIF(B1:J1,{"A*","B","C"}))>=5,1,0),0)

=IF(AND(OR(C1={"A*","A","B","C"}),OR(H1={"A*","A","B","C"})),IF(SUM(COUNTIF(B1:J1,{"A*","B","C"}))>=5,1,0),0)

The asterix '*' is like a AND function.

OR(C1={"A*","A","B","C"}) - test column C

OR(H1={"A*","A","B","C"}) - test column H

SUM(COUNTIF(B1:J1,{"A*","B","C"}))>=5 - test all columns only if the tests of column C and H are true.

I hope this helps you to understand.

Markmzz

 

[circledchicken]

Did you look? I used "A*" specifically to have that work for both versions of "A". The sample sheet above shows it working correctly.... Did you try it as is?

Sorry for the confusion, I was referring to my quoted post not yours (although yours feels like a better way to do it!)