Attendance spreadsheet needs a six-month rolling average

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.
LVL 23
DavidSenior Oracle Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zack BarresseCEOCommented:
Hello there,

A couple of questions.  What version of Excel are you using?  How is your data structured?  What data resides in which columns/rows?  Can you give us a detailed description?  As far as Google spreadsheets, why not use SkyDrive and Excel Services?  It's free, able to collaborate, and you're using actual Excel files.  Plus you'll get to preserve formatting and results, which is something Google has a long way to go yet.  I do not recommend them, but Excel Services, which is free.

As far as recommendations for a formula, it depends on how your data is structured.  If you have it normalized, you can utilize a SUMIF() or COUNTIF() (or SUMIFS or COUNTIFS) function between the person and the dates.  Another option is a PivotTable.  Again, the specific recommendations would depend on how your data i structured.

Zack Barresse
What if you use your COUNTIF, but just a little Differently. Put a Y for all the meetings that the scout was there at the meeting, and a N on all the days where there was a meeting and the kid wasnt there. Use CountIf to count the number of Ys and the number of Ns. adding them together would give you the total number of actual meetings, dividing the Ys by the total would give you the average.

As for limiting it to 6 months if you only have your COUNTIF run for 26 lines and you have 1 meeting per line, then that would give you a rolling 6 months (i.e. if you do countif lines 1-26, then the next line 2-27, 3-28, 4-29 etc.

Im just picturing it in my head, I havent actually tried it, so i apologize if this is completely way off.
Zack BarresseCEOCommented:
@Grasty86: That would work for a very static data structure, but it isn't very dynamic.  What if another meeting was entered at a different date?  Eventually it would be counted in the wrong "past 26 week" group.  Better to base it off a date in a well structured workbook, that way it wouldn't matter where you were, you would get an accurate count.

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Bill PrewIT / Software Engineering ConsultantCommented:
Can you share a sample of your worksheet.

DavidSenior Oracle Database AdministratorAuthor Commented:
Sorry y'all, it appears my reply Tuesday didn't complete.  CSV attached.
Zack BarresseCEOCommented:
I would recommend changing your data structure slightly.  For starters, keep only dates in your date column.  If there's a relationship between data, you can have them in different tables, which will make it less cluttered and easier to extract data from as well.  Since the incrementing record is based on date of meeting, I would have those going down the columns instead of over the rows.  You want your data to grow downward, not rightward.

Also, you have members and their joined dates.  While this doesn't truly belong in the same table, you should have this in a separate table.  This is called normalizing your data.  This is a VERY extensive subject, and if you have some spare time I would read about "first normalization", as it goes down from there (first, second, third, etc).

Honestly, this should really be done in a database.  It would be much easier to create the relationship and reports you need that way.  If you want to do it in a spreadsheet I would recommend using PowerPivot, which will let you take multiple data sources (Tables) and extrapolate data from it.  It's a free download from Microsoft if you have Excel 2010.  Not sure what version you're working from though.

I've attached a very quick rough draft of a file with your data, which will give you some rolling average attendance figures per person.  As you can see there are three Tables: Attendance Records, Meeting Records and Personnel Records.  Ideally these would all be on different sheets because they house different information even though they're all related.

Attendance Records is where you would put individual records which go per person.  So each meeting date somebody attends you would put the date and they're name.  The last column is a formula just to show what unit they're from.  You don't need that formula per say, but if you want to do a PivotTable on that data, that is one way to be able to add another layer (another would be the suggestion of using PowerPivot, create a relationship and you don't need a 'helper' formula column anymore).

Meeting Records is where you enter in when a meeting took place and where.  The 'Location' column isn't actually used for anything, but you could later on if you wanted to, such as you wanted to give a count of where meetings took place, i.e. how many meetings were at 'X Location', or how many meetings have we had at 'Y Location' in the last 3 months, etc.

Personnel Records is where you enter each person individually.  You can assign their name, join date, unit, and this is where the formulas were added.  I parsed them out individually to give you more information.  The first formula is a 6 month count of how many meetings they've attended in that time frame (from today, the system date of your computer), and the formula is as follows:


Open in new window

As you can see I labeled the Table names a descriptive name preceded by "tbl" to give it meaning to me in the formulas.

The next formula is the 6 month average, which takes all the meetings attended in the last 6 months (previous formula) and divides it by the total number of meetings held in that same time frame (utilizing the Meeting Records Table for that figure), and the formula is as follows:

=[@[6 Month Count]]/COUNTIFS(tblMeetings[Date],"<="&TODAY(),tblMeetings[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))

Open in new window

As you can see it takes the number from the first formula and divides by the count on the second Table.

The final formula is almost the same formula but instead of using 6 months it utilizes 26 weeks, which you specified.  Where this may give you erroneous results is it still references the 6 month count.  If you want a true 26 week average you would need to have a count of 26 weeks, which would be a formula adjusted something like this...


Open in new window

So your 26 week average formula currently looks like this ...

=[@[6 Month Count]]/COUNTIFS(tblMeetings[Date],"<="&TODAY(),tblMeetings[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-182)

Open in new window

So if you added a '26 Week Count' column with the formula given, your updated formula would look like...

=[@[26 Week Count]]/COUNTIFS(tblMeetings[Date],"<="&TODAY(),tblMeetings[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-182)

Open in new window

I hope that makes sense.  It isn't going to be much difference because it's almost the same amount of time, but it could give you erroneous results, possibly.

This is slightly long winded, so if you have questions, please ask.  There is a lot you can do with this data.  As far as making this compatible with Google docs, I don't work with Google docs because I like to keep my data integrity, which they're known for skewing the way things look.  You can keep this file in Excel Services (i.e. SkyDrive) not only for free, but 1) accessible to everyone and 2) data/format integrity, which is what I would recommend.


Zack Barresse

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.