Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have inherited a horrible spreadsheet with 4 years of data about when people participated in a given activity, by month. Each person would likely participate once every 18 months or so, but obviously there are variations. It's currently laid out with a single cell for each person/month combination, so there are 48 columns representing the last 4 years and 150 rows representing the people. When the person participates the background color of the cell for the month they participate is colored black. That's the only representation.

Rather than update the spreadsheet for 2011, I would like to get a new representation. I'll likely have to manually migrate the existing data to the new system, but that's an unrelated problem. Assuming I have a list of dates for each person, how can I visualize the data so that I can quickly assess the following:

  • Who hasn't participated in a long time
  • How long has it been since Person X participated
  • How often has Person X participated

Writing a custom application to handle this isn't an option since I'll have to hand it off to someone in the next 1-4 years and they likely won't have the skills to maintain it. Because of that I'd like to keep it in a spreadsheet that I can host on google docs, or at worst maintain in open office and email around to the "need to knows."

share|improve this question
    
Is using a Google/Open Office spreadsheet a requirement? This sounds like honestly some kind of database might be better at first blush - I'm sure there's some SQL guy responding already haha. (I'm also curious what the hell this 'participation' actually is because it sounds bizarre wrt the timing, heh.) –  Shinrai Dec 14 '10 at 19:18
    
This is for my church, and when I'm done with my assignment I'll be handing this tracking over to the next person with the assignment, so a custom database application really isn't the best option due to the maintenance aspect of it. Most people can update a spreadsheet, but not many could update a database application. I don't want to host it on the web, and I don't want to have to help them get it installed on their own systems either. Hopefully that helps explain the constraints in the question. –  digitaljoel Dec 14 '10 at 19:51
add comment

1 Answer

I would setup your spreadsheet like a database (as @Shinrai suggested) with "participated date", "person", and perhaps "type of activity" as columns.

person   date_participated  activity_type
Joe      2010-12-04         cow tipping
Jimmy    2010-12-04         surfing
John     2010-12-16         compost toilet maintenence
Joe      2011-07-01         cow tipping
John     2011-07-03         compost toilet maintenence 
Sam      2011-09-30         surfing

(John's a trooper) Now the rest you could do with pivot tables and charts. You could also use the limited database-esque functions in Excel or roll your own. You can make charts of individuals participation, tables with date last participated (or months since last participation), and counts of total numbers of participation.

share|improve this answer
    
That's where I was leaning with it, with perhaps a slightly different data model. The difficulty is in how to show the data, in a single chart that's easy to read, so that I can quickly figure out the three criteria listed in the question. I've got some ideas that are similar to this. Thanks for the answer. –  digitaljoel Dec 15 '10 at 22:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.