comparing two columns & then perform a calculation

[Guest]

Hi,

I am a teacher analysing attendance figures - and struggling somewhat!

Here's what I want to do . . .
I have a spreadsheet with the following columns:
A B C
D
Name (student) Year (7-11) attended (Y or N) %
attendance

I want to enter a formula somewhere else on the sheet that will;
select only students in Year 7 (by looking this up in column B), calculate
the average % attendance for these students only.

I will then edit this formula (in a different cell) to perform the same
calculation for year 8 students, then year 9 students . . . and so on.

I want to keep running totals so this won't be just a once-only calculation.
As more students get added to the list I want the attendance results to
update.

Please help me!

 

[Max]

One way ..

List in H1 across the year: 7, 8, 9, 10 ...

Put in H2:

=SUMPRODUCT(($B$2:$B$100=H1)*($C$2:$C$100="Y"))/COUNTIF($B$2:$B$100,H1)

Format H2 as percentage and copy across as required

H2 will return the %attendance for year 7, I2 returns year 8's, and so on

Adapt the ranges to suit ..

 

[Ian]

=SUMIF(B2:B100,7,D2100)/COUNTIF(B2:B100,7)