Improve company productivity with a Business Account.Sign Up


Attendance spreadsheet needs a six-month rolling average

Posted on 2012-04-03
Medium Priority
Last Modified: 2012-05-05
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.
Question by:David
LVL 14

Expert Comment

by:Zack Barresse
ID: 37803577
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

Assisted Solution

Grasty86 earned 500 total points
ID: 37803600
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.
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 1500 total points
ID: 37803612
@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.

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 61

Expert Comment

by:Bill Prew
ID: 37804002
Can you share a sample of your worksheet.

LVL 23

Author Comment

ID: 37812039
Sorry y'all, it appears my reply Tuesday didn't complete.  CSV attached.
LVL 14

Accepted Solution

Zack Barresse earned 1500 total points
ID: 37813017
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question