Solved

Attendance spreadsheet needs a six-month rolling average

Posted on 2012-04-03
6
943 Views
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.
0
Comment
Question by:David
6 Comments
 
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.

Regards,
Zack Barresse
0
 
LVL 4

Assisted Solution

by:Grasty86
Grasty86 earned 125 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.
0
 
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 375 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.

Zack
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 51

Expert Comment

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

~bp
0
 
LVL 23

Author Comment

by:David
ID: 37812039
Sorry y'all, it appears my reply Tuesday didn't complete.  CSV attached.
Troop-50-Attendance-Roster---She.csv
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 375 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:

=COUNTIFS(tblAttendance[Name],[@Name],tblAttendance[Date],"<="&TODAY(),tblAttendance[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))

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

=COUNTIFS(tblAttendance[Name],[@Name],tblAttendance[Date],"<="&TODAY(),tblAttendance[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-182)

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.

HTH

Regards,
Zack Barresse
Troop-50-Attendance.xlsx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now