Hello:
I'm not a researcher, but I'll share my personal experience with you
after using spreadsheets 8 hours a day for 15 years. I'll speak in
general terms and hopefully that will give you ideas on how to search
the Internet for specifics.
Generally speaking, a spreadsheet user is the same person that
'designed' the spreadsheet. I assume you're wanted documentation
because this may not be the case for your particular situation.
Speaking from experience, there is nothing worse than working in
someone elses spreadsheet. I say this because not very many people
know how to properly document a spreadsheet. They design it thinking
it will only be used by themselves.
Q1. Describe the features of a well-designed spreadsheet?
Q2. Describe checks that can be incorporated into spreadsheets to
illustrate errors?
For the most part, a spreadsheet is a mathmatical calculating (and
sometimes, decision making) software. These calculations and decision
are accomplished through the use of formulas. In order to give
integrity of the spreadsheet, all the formulas MUST be correct. This
is accomplished by checking and rechecking to make sure your formulas
are correct. A visual inspect of a simple spreadsheet may accomplish
this. I've built complex spreadsheets where there are thousands and
thousands of formulas, so this method does not work in cases like
these. You can programatically check your formulas by basically
re-writing them again and comparing the answer to your original
formula.
Example:
Cell A1 = 1
Cell B1 = 2
Cell C1 = @sum(a1..b1) [Lotus] or =sum(a1..b1) [Excel]
Here's the double check
Cell D1 = @sum(a1..b1) [Lotus] or =sum(a1..b1) [Excel]
Cell E1 = @if(a1=b1,"match","no match")
[CELL E1] If you answer in d1 matches the answer in c1 (notice, they
are the same formula), cell e1 will display the text "match", if not,
it will display "no match," which means that one of your formulas are
wrong.
Sometimes you may have what is called a "circular reference," in which
case one cell that depends on an answer is dependent on the cell
you're on. Its similar to the 'which came first, the chicken or the
egg.' The spreadsheet goes crazy trying to 'catch his own tail'.
This is another type of formula error. Most spreadsheets have a way
to 'track down' the course of the circular reference. Consult the
'help' utility on your spreadsheet.
If spreadsheets are designed by one person, but used by another, it is
a good practice to document as much as possible what the spreadsheet
is doing (i.e. macros, formulas, etc.). This makes the design phase
much longer, but the maintenance and training issues much easier.
Further, certain cells can be 'locked' from being changed. Lets say
that you have a very complicated formula that took a long time to
write and get right. You don't want some other user accidentally
deleting the formula. Most spreadsheets all you to make it so a
certain cell cannot be altered unless you intentionally turn off the
'lock' feature.
Q3. Reasons for ongoing operation and maintenance of spreadsheets?
A spreadsheet can be used over and over again by creating a template.
For example, a bank reconciliation template will always have the same
components, but is generally used for only one month's activity at a
time. The template may contain a 'tab' for deposits, another tab for
'disbursements', another tab for 'other debits and credits', etc. The
only thing that is different from month to month is data. Everything
else (the body of the template) remains the same. This is the reason
a spreadsheet can be used on an ongoing basis. You just need a good
way to programatically erase old data and enter use data, with the use
of some macros (a little more advanced feature of most spreadsheets).
Q4. Describe documentation procedures for repeated use spreadsheets?
No hard and fast rules here. Document as much as possible, so it
makes it easy for the next person using it. On a separate tab in the
spreedsheet, describe what the purpose of each component (tabs,
macros, etc.) of the spreadsheet. What do the formulas do, if
decision making formulas are written, what do they do and why. This
advice is for a company, where there is turnover and the possibility
that the designer and the user will inevitably be different. If you
are designing for your own personal use, the designer will always be
the same as the user, so the documentation is a waste of time, since
you'll know every component of the spreadsheet intimately.
I hope this information helps you a little.
Good luck. |
