In-house quality control of spreadsheets 5

[JAE]

All right, I'll start....

We have developed numerous spreadsheets for use in our daily calculations. Many times, the spreadsheet is developed for a one-time use on a particular project. Other times those same spreadsheets are further refined to be more generic for future use. Added formatting for clarity and some graphics added to help future users understand the input and output.

We have developed a rather informal procedure for checking the quality/accuracy of the spreadsheets. Basicaally, if someone uses them, they are expected to hand check the sheet for the first few times of use, until a level of confidence is built up.

What do others out there do to facilitate qualtity assurance in their spreadsheets? Do you have formal checking procedures? Does somebody sign off on the work?

 

[dison]

I agree with the "write your own program" method of learning the theory and methods of design.

That being said, it is rare in our office to use spreadsheets of unknown origin or even spreadsheets that others in our office have developed without taking the time-consuming step of checking every calculation (which by the time you have completed, you might as well have written your own).

However, I am very interested in having the printed output of the spreadsheets that each individual uses in a consistent format. Does anyone have any tips on creating a "standard template" for format and presentation in excel? For example, calculation header - order of calculations - format of intermediate and final results - graphics - page numbering. How does one use a standard calculation header when the row and column widths cannot be kept standard? ~dison

 

[engcomp]

PHEW - this post takes some time to read.
But it's worth the time - so many good thoughts.
1. Do your own manual checks.
2. Don't trust SS unless you know what they do.
3. Why don't you share your hardwon knowledge?
etc.

I agree with most of what's been said,
having practised it myself for forty years.
Out of this practice grew a program called MATHSERV
see

http://www.engs-comp.com/mathserv/

It answers many of the posts:
1. it allows manual checks;
2. it is as intuitive as BASIC;
3. we believe in sharing knowledge.

Please let me know what you think, Helmut engcomp@ozemail.com.au

http://www.engs-comp.com/

 

[JAE]

dison:
What we do is establish a standard boxed header which is similar to our calculation pads we use for manual calcs.

You're correct that the column widths vary with each spreadsheet but we try to correct for that by spreading the header over various numbers of columns where possible.

We include in the header the following:
Title of the spreadsheet
User name and date
Checker name and date
Project name and project number
Subject (basically an open area to describe the part of the structure involved with the particular calculation.)
Last date of editing and author

The spreadsheets then vary quite a bit between themselves as the nature of the work varies but basically we follow the same general order:

1. Input - This involves most all of the user required data. Sometimes there are miscellaneous inputs required further into the sheet as they make more intuitive sense sometimes when placed next to intermediate results. Input involves non-locked numerical or alpha input as well as some scroll-down select boxes.

2. Calculations - This is where the actual numerical crunching occurs. We have progressed more over time to showing almost every intermediate result as its easier to understand the sheets workings and limitations when you show "all your work". In the past, we were always tempted to hide the tedious crunching off the main sheet but we've stopped doing this. These numbers include descriptions of the variables and references to AISC, ACI, etc. code/spec equations or sections for easy tracking.

3. Results - It is important to be very clear in what the resulting answers are - usually boldly proclaimed in boxes or other graphics to specifically offer the results. Sometimes these results are actually placed near the top of the sheet so they can be observed while altering the input.

4. Description - usually a graphic box of paragraphs describing the sheet and what it does and more importantly WHAT IT DOESN'T DO. References to code/specs used as well as other book or guide references are included.