Friday, February 4, 2011

yogi_Class Attendance Statistics



Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com




In sheet AttendanceByGivenDate, forula in cell B2 is:
=transpose(filter(SourceData!A:A,filter(SourceData!A:F,SourceData!A$1:F$1=A2)))

In sheet AttendanceByGivenName, formula in cell B2 is:
=ArrayFormula(if(index(SourceData!B:F,match(A2,SourceData!A:A,0),0),SourceData!B$1:F$1,"No"))

In sheet ClassesByPersonBetween2Dates, formula in cell D2 is:
=count(if(SourceData!B$1:F$1>=B2,if(SourceData!B$1:F$1<=C2,index(SourceData!B:F,match(A2,SourceData!A:A,0),0),"")))

In sheet AbsencesByPersonBetween2Dates, formula in cell D2 is:
=countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))