Counting using 2 categories

[Nick Horn]

I have a spread shett which is recording lesson observation data e.g.

Subject Grade
Art 3
Maths 1
Art 4
Maths 2
Art 2

I want to get the spreadsheet to avaerage the grades for each subject
seprately e.g. Art 2
Maths 1.5
Does anyone have any ideas what formula(s) I should use.

Many thanks

Nick Horn

 

[Peo Sjoblom]

A couple of ways, assume subjects in A2:A10 and grades in B2:B10


=AVERAGE(IF(A2:A10="Art",B2:B10))


entered with ctrl + shift & enter


or

=SUMIF(A2:A10,"Art",B2:B10)/COUNTIF(A2:A10,"Art")


since you want to be able to change subjects easily replace the word "Art"
with a cell where you put the different
subjects


=AVERAGE(IF(A2:A10=E1,B2:B10))


and


=SUMIF(A2:A10,E1,B2:B10)/COUNTIF(A2:A10,E1)


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

You can use this for "Art"...

=SUMPRODUCT((A$2:A$100="Art")*(B$2:B$100))/COUNTIF(A$2:A$100,"Art")

and, by changing the 2 occurrences of "Art" to "Maths", get the average for
that subject instead. You could also put the subject name in a cell, say C1,
and get the average using that reference instead...

=SUMPRODUCT((A$2:A$100=C1)*(B$2:B$100))/COUNTIF(A$2:A$100,C1)

Rick