Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting using 2 categories
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting using 2 categories
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 "Nick Horn" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting using 2 categories
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 "Nick Horn" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting categories | Excel Discussion (Misc queries) | |||
Top Five in Categories | Excel Discussion (Misc queries) | |||
2 categories | Excel Discussion (Misc queries) | |||
counting categories of text cells | Excel Discussion (Misc queries) | |||
sum over categories | Excel Discussion (Misc queries) |