Best solution for spreadsheet to track monthly usage statistics

January 27, 2009 1:58 PM

What's the optimal design for a spreadsheet to track statistics for events (performer, type of program, location, attendance) and pull out various monthly totals? (Details follow)

I can hack my way through a basic excel file and work with simple formulas, but I find I'm a bit out of my depth here. Advice would be greatly appreciated. I'm in the process of setting up a spreadsheet to track monthly statistics for my library: programs, transactions, meeting room usage, etc. Let's start by talking about programs, since I should be able to apply whatever solution I come up with to the other categories (which will have slightly different fields.)

The key fields we want to track for each program are the date, description, program type, name of the staff member responsible, location, and attendance. From this raw data, I need to be able to pull out monthly and yearly summaries, all keyed to attendance: how many patrons attended storytimes this month, how many people attended computer classes. I'd also like to pull out counts: Staff Member X had this many programs this month, for example. And then overall and yearly totals as well.

The model I have to base this on (from our HQ library) is a monthly spreadsheet--that is, there's a master spreadsheet with just headers and a new sheet is created at the beginning of each month, and that's month's stats are recorded in it. My biggest issue with this solution is that I'm not a fan of creating a new spreadsheet each month. I think the more elegant solution is to have staff members enter all the info via a form into a continuous sheet of raw data, then have another sheet with formulas showing just the sums and counts we need. Do you think my way is a smart way to do it? Would you just stick with the existing simple solution? Is there a better alternative I'm overlooking?

The biggest snag I've hit so far with my preferred solution from the previous paragraph is creating formulas that filter by date. I think Excel and google both might be able to handle this, but I haven't had success yet.

I can use either excel (2003) or google, though I'm leaning towards google b/c the web form entry is so nice. Way less opportunity for staff entering data to stray from the controlled vocabulary.

Thanks,
Mike

posted by mkny to computers & internet (9 comments total)

How about this?
Tried using Google Spreadsheets but it doesn't accept some of the Excel stuff (Filters, SUBTOTAL)

Never used this upload service before but the download worked for me. MeMail me if you can't get it.
posted by jckll at 2:39 PM on January 27

Google spreadsheets might be good for recording the data, but if you are not well-versed in formatting jutsu, it may not help you to report.

If I were you, I would use pivot tables (and they would be delicious). You can slice things every which way if it starts in a normalized format.

PM me with something a little more specific and an email address, and I can spend a minute or two showing you what it might look like and how you might present the results. It will be very easy for you to do after you see the magic.
posted by milqman at 2:49 PM on January 27

Honestly it sounds to me like you're edging into database territory. I'm sure you could do it as a spreadsheet, but I think it'd be ugly. A database would be a cleaner solution.

Of course, that presupposes you know how to build databases yourself, or can get someone else to do it for you. But given what you're talking about, I'd highly recommend a database rather than a spreadsheet.
posted by sotonohito at 2:58 PM on January 27

I kind of agree with sotonohito. You could probably manage this with a flat spreadsheet, but I can imagine this needing to become a full-blown relational database.

Take a look at Dabble DB. It's a web-hosted database service type thing. You can set it up so that other people can enter data into your DB. It's darn slick.

On the spreadsheet tip, I've actually been tweaking the spreadsheet I use as my job log (I'm using Numbers, not Excel), and one approach that you might explore would be to have one table/list where you dump all your raw data, and other tables/lists that summarize the data by month, etc. That is what's working for me.
posted by adamrice at 3:15 PM on January 27

I think your idea makes a lot of sense. Pivot tables are exactly what you're looking for.

Say you have lots of different rows in your spreadsheet that all share the same value for some field, and have different values for some other field. (e.g. lots of rows have "computer class" in the event field, and each row has the "number of patrons"). Pivot tables will condense all of those rows into a single row, combining that other value in some way. (e.g. You'll end up with one row for "computer class" and the sum of "number of patrons" or the average of "number of patrons").

I need to be able to pull out monthly and yearly summaries, all keyed to attendance: how many patrons attended storytimes this month, how many people attended computer classes. I'd also like to pull out counts: Staff Member X had this many programs this month, for example. And then overall and yearly totals as well.

Yep, pivot tables would make all this a snap.
posted by losvedir at 4:42 PM on January 27

Thirding pivot tables. They were designed for the exact scenario you're describing - performing counts and totals across a variety of date and value parameters. You'll have one sheet containing the ongoing data and another sheet with your pivot tables that read and slice the data from the first sheet.

As for controlling what gets entered into each field, you can use Data Validation to handle that. Data validation lets you impose rules on the data entered into cells, e.g. value must be between X and Y, value must in the dropdown list, value must be a date, value cannot contain over Z characters.
posted by junesix at 11:48 PM on January 27

Thanks for the responses. Here's the thing: I'm the computer guy here, so I can learn whatever new stuff I need to create a pivot table or even a database (I've fiddled with both before, though never put one into production for scratch); but the staff who are going to be inputing data and the manager who will be using the tallies aren't very computer-savvy. I'm not sure teaching them how to interact with a pivot table is within reach.

I realize forms/data validation can ease things for the entry half of things, but I'm equally concerned with keeping things simple for my boss. I'll take a look at the various proposals above for now...
posted by mkny at 10:46 AM on January 28

@cklennon: I thought about the built in Excel autofilter, but it doesn't serve my goal of having a separate summary/total sheet. I don't want the end user to have to dynamically filter what they're looking for each time.
posted by mkny at 10:50 AM on January 28

@Everyone who suggests a database: really? I didn't think it was a complex enough problem to warrant such a powerful tool. (FWIW, I'm not a big fan of Access and I don't have the resources to host a webpage. A hosted solution, like Dabble, would be ok, but obviously I need to ensure we can export our data and such.)
posted by mkny at 11:01 AM on January 28