Attendance spreadsheet needs a six-month rolling average
Posted on 2012-04-03
Having searched E-E and MS w/o success, I ask:
I have a scout troop for which weekly meeting attendance is recorded. A COUNTIF function captures my number of 'Y' values in the hard-coded range, which was fine for all those in my initial group.
Now I need to refine my requirements. I need to see an individual's attendance percentage for the rolling past six months, preferably with the no-meeting date columns excluded.
For the second part I'm willing to set up a separate question if the experts say so. Some individuals join at different times in the year, so a better solution is to find and track the number of weeks from their first meeting.
So, for any individual (row), I should use their total number of weeks attended (except for no meetings), divided by their total number of weeks eligible, up to a maximum of 26 weeks.
Last factor is that I prefer to use a Google spreadsheet so far a couple of people can collaborate on it.