Yesterday I sat in a very useful session at the Summer Meeting (Informatics Institute) about tricks and tips for using Microsoft Excel(TM). Interestingly, this session occurred on the last day of the meeting and yet was well-attended. Pretty clearly, a lot of people find themselves using Excel, and, even though I have been using it for years, I learned about some things that will probably make me more efficient.
Having said that, one of the things that occurred to me as I listened to the presentation was the notion that a lot of the attendees make spreadsheets for other people to use.That being the case, I found myself wondering how the recipients of these spreadsheets find out how they are intended to be used. I suppose that, if someone was curious enough to explore a spreadsheet and look at the underlying formulas and formatting of individual cells, one might think of spreadsheets as self-documenting. Please forgive me if I express some skepticism about whether or not most of these intended users go to that effort.
Rather, I strongly suspect that people open up the spreadsheet and one of two things happens:
- They visually understand basically how the spreadsheet is supposed to work, and they enter some data into some fields to see what happens. As a result of that exercise they either see something happen that they perceive as correct, see something happen that they perceive as incorrect, or they see nothing happen.
- The cannot visually understand basically how the spreadsheet is supposed to work, close it, and never open it again.
What therefore struck me was that it was interesting to note that we do not demand of ourselves the kinds of work we demand (or should demand) of software providers in the marketplace:
documentation.Nowhere in the presentation did anyone discuss the notion that it would be helpful to dedicate a page in the spreadsheet workbook to information about how it was intended to be used and what the underlying logic of the spreadsheet might be. Nor was there any discussion of the use of field annotation for this purpose.
I understand that such documentation was not likely within the scope or the intention of the presentation. But I must confess I was disappointed that nowhere in the discussion was the notion that people might not understand not only
how the spreadsheet was designed to work but also
why it was designed to work that way. The reason I am concerned about this is that most people build spreadsheets for their own use, and they forget what assumptions they made when they put a spreadsheet together. I know I do. And if others are going to use a spreadsheet that someone else designed, it is REALLY helpful to know what assumptions went into that spreadsheet and how the author of the spreadsheet intended it to be used. Knowing why it was built the way it was also helps the user decide if it will be useful for them, as well.
So here's what I wish was included in that training. I would hope that every spreadsheet that gets developed for use by multiple people (or, frankly, one developed for personal use that might be used by others) would have an information worksheet that documents:
- What problem or problems the worksheet is designed to solve and the general approach to solving them
- What assumptions regarding the content of the spreadsheet are implicit in the design
- How the user is expected to use the spreadsheet
- If the user is to enter any data in the spreadsheet to make it work its magic, what constraints, formatting, or value ranges are necessary to make the spreadsheet work properly.
- A list of the formulas that the spreadsheet employs
- A list of lookup tables that the spreadsheet uses, where those tables are located, and what valid values of those tables need to be. This is especially critical if the spreadsheet in question references other spreadsheets that need to be available for it to work as designed.
Note that this isn't just a 'buttonology' discussion (a list of instructions on
how to use the spreadsheet); it is also a discussion of why the spreadsheet was set up the way it was and how it is intended to work. If applicable, this also therefore contains a list of known misuse statements that describe cases the spreadsheet will not handle.
Mea maxima culpa, the last spreadsheet I developed for a group I am working with didn't contain this information... but the email I sent it in did.
What do you think?
Dennis A. Tribble, Pharm.D., FASHP
Ormond Beach, FL
DATdoc@aol.com
The contents of this blog represent my opinion solely, and not necessarily the opinion of my employer or of ASHP