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