Checking a row to see who got all 'A' Grades

[dpbarry]

Hi folks..

You would know it was exam result time here in my place of work.

Not that I've got all the grades sorted out for the Principal, she is now looking for me to tally up all the students who got straight 'A' grades. I know there is going to be anotehr combination here but I'll start with the basics of straight 'A's

The screen below shows how the data is presented although I've slimmed down the headings to fit better.

How do I insert a formula to look at each row to see where based on the subjects taken, there are straight 'A's

Regards

Declan

Y13 & 14 Combined Worksheet.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Art	Art2	Biology	BS	Chemistry	D&T	Drama	Economics	E.Lit	French	Geography	History	H.Econ	ICT	ICT2	Irish	Latin	Mathematics	MathsPure	Music	Physics	Politics	Psychology	R.E	Sociology	Spanish
2									A					A											A
3									A	A		A								A
4							A		C						D					B
5												B											B	A	A
6								C				D													B	B
Sheet1

 

[Andrew Poulsom]

The formula:

=COUNTIF(A2:Z2,"A")=COUNTA(A2:Z2)

will return TRUE if all the results are grade A, otherwise FALSE. The first part of the formula counts the number of As.

 

[dpbarry]

Cheers Andrew..

Job a good one.

Now for a silly question.

How do I sum up the 'True' values

Declan

 

[dpbarry]

Ahh!! Think I've got it.

I've used the 'Countif' function to count the values that are true.

Deems to work.

Many thanks

Declan

 

[stanleydgromjr]

Delcan,

If you insert a column in column A, give it a title of "Straight A's".

In cell A2 copy the following formula:
=IF(COUNTIF(B2:AA2,"A")=26,"Straight A's","")

Then copy cell A2 down.

Have a great day,
Stan

 

[Andrew Poulsom]

Delcan,

If you insert a column in column A, give it a title of "Straight A's".

In cell A2 copy the following formula:
=IF(COUNTIF(B2:AA2,"A")=26,"Straight A's","")

Then copy cell A2 down.

Have a great day,
Stan

That's a lot of exams to take at one time. I assumed all the subjects were listed, but only a selection were taken by each pupil.

 

[dpbarry]

Hi Andrew..

I'd an idea this would happen.

Statistics, statistics and **** lies

As it is, some students only take 3 A-Levels but some others take 4

What the principal is now looking for is where a pupil has all A's or 3 A's and a B.

Is there a way of counting if a student who has 4 grades has obtained 3 A's and a B?

I've added in a column that counts the number of subjects done and because we now have a list of how many A's, B's, C's etc that a student has, I'm trying to created a some sort of a nested 'If' statement that will check to see if the number of subjects taken = 4, the number of A's taken equals 3 and the number of B's equal 1 and set that value to true so that I can then add up the number of tru values.

Regards

Declan

 

[Andrew Poulsom]

How about?

=OR(COUNTIF(A2:Z2,"A")=COUNTA(A2:Z2),AND(COUNTA(A2:Z2)=4,COUNTIF(A2:Z2,"A")=3,COUNTIF(A2:Z2,"B")=1))

 

[Charlie45]

=OR(AND(COUNTIF(A3:Z3,"A")=3,COUNTIF(A3:Z3,"B")=1),COUNTIF(A3:Z3,"A")=4)

 

[dpbarry]

Cheers folks..

I'll give it a go and see what happens.

Declan