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))

2 comments:

  1. I think the third formula is going to be particularly useful.

    =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 order to calculate a penalty for non attendance do you have any thoughts on how I could adapt this to count the number of blank cells instead?

    ReplyDelete
  2. Hi Paul:

    I have added a sheet AbsencesByPersonBetween2Dates to count the number of blank cells ... with the following formula in cell D2:
    =countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))

    Let me know if this is what you are looking for.

    ReplyDelete