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?

 

[lewbbhp]

I have made numerous spreadsheets for my use. I always verify that they are correct by comparing the results with hand calculations. Because of this I don't make a spreadsheet for a one time calculation.

On occasion I have made them available to others. Before I do, I lock all cells except the input cells and password protect the spreadsheet.

 

[JAE]

We do the same lock that you do. One time calculation spreadsheets are usually developed due to trying different sizes and for different conditions throughout a project.

Do you do the hand check on your own, or do you have someone else perform the check?

 

[lewbbhp]

I do my own hand calculation.

 

[Riz]

We have also created SS for internal use. The input cells are available and unlocked. The entire sheet is password protected. The manual hand checks are in order to ascertain everything is done correctly and signing off. Excel allows for animation and we use them in the output boxes for highlighting areas of concern or failed sections/elements.
Riz

 

[JAE]

We use "NG" and "OK" next to various outputs. Riz...what kind of animation do you use and how is it created in Excel?

 

[paulomanuel]

I recommend everyone a very good book that i bought:
"A guide to Microsoft Excell for Scientists and Engineers"
Bernard V Liengme. It is a real manual. Works on math issues rather than the basic stuff. You realise of some limitations of spread-sheets, but then again its a good sign, it means that you already know more than the computer.

 

[Qshake]

For repetitive or single use programs our company mandates that each is check independently. This includes all macros and or VBA code. Also, spreadsheets must as a minimum include a sheet which explains the underlying theory upon which the spreadsheet is based and, of course, and assumptions. Spreadsheets for repetitive use have additional formating requirements so that they will fit seamlessly into our typical calculation book. Repetative spreadsheets are logged into a database and tracked for changes and updates as necessary. Once a spreadsheet has been logged in, a thorough check is no longer required; only a check for appropriate use and input.

Two books I recommend for Excel users are:

Excel 97 Annoyances from O'Reilly publishing
Using Excel from QUE

Great tips in both books.

 

[Ron]

From a policy perspective, we treat internally generated spreadsheets as if they were externally generated programs. This includes listing in a master database and requires hand calculation verification, and/or analytical comparison with commercially produced software if appropriate.

Does all of this happen every time? Not a chance. We keep trying, but every time we do a technical audit (quarterly or annually, depending on level)we invariably find spreadsheets that were produced by a staff engineer to expedite his work, and though the final product is reviewed by the appropriate gray hair, the documentation of the spreadsheet is, shall we say, "thin"!

Sort of reminds one of the documentation received with commercial software!!

 

[justanotherguy]

Long Post - careful!

I have an approach that may be useful. Perhaps this does not directly answer your question, but I believe that at the root of the question is an idea of go / no-go approach. I think you may need to expand the parameters if you are to get a useful process and useful results.

First, a reminder that quality is never inspected into anything. No one like the quality cop. If you want quality results, you need to build a process that ensures quality at every step. This takes commitment and training, but if you want to be professional, there is no other choice.

To build in software QA, take a page from the software guys. Ask them. I cannot speak to all the QA approaches, but some of them I know and use.

Use range names where possible - these become like variable names. Makes debugging easier. You can even code names with variable types if that is an issue.

Lock down non-input cells, but this is not always the best, as one of the advantage of spreadsheets is easy change and adaptability. So each sheet's last page should be a page where all changes are logged. The logging need not be too detailed, but it will warn others that the sheet is no longer vanilla. This is simple and an acceptable trade-off - with the freedom of the tool comes the responsibility of safe use. Of course, do not password protect and then refuse to give out passwords - this breeds an "us vs. them" mentality and there are password cracking utilities easily available, so passwords ensure nothing.

Use colours and formatting to identify input cells, parameter cells, calculation cells, intermediate and final result cells. This makes checking the calculations easier, and can provide an ergonomic edge. THIS IS ONE OF THE MOST SUBTLE BUT IMPORTANT STEPS! Do not underestimate it.

Make these range names, colours, and formatting standard throughout the office. Develop them democratically or autocratically, but publish them for all to see and use. People should be self-conscious whenever they are seen to be using a non-conforming SS!

Use comments liberally, especially if some values need to remain within certain ranges, etc.

If whole columns have the same formula (i.e. the references change in a constant way), one thing that can help understanding is including a text copy of every formula as an entry below the last effective entry in the column. So if you have 25 rows, use row 28 to put in the text of the formulas. Comes in handy getting a faster handle on new sheets or rarely used sheets.

Ultimately, if you develop SS as if for clients, you have a good process. Now I know that all sheets will not fit into this process - some will be scratch pads for quick what-if's, etc. But if someone uses a SS repeatedly, then they should make it conform. Trust me, the incremental time to format a sheet properly is only 5 to 10% of the total sheet development time, but the time and error-cost savings are more than worth it, as is the peace of mind. Formating becomes automatic.

You can e-mail me - I cannot release any of the sheets I have developed, buy I can give you more info if needed.

 

[webwizard]

Spreadsheets can be great for simple calculations or even many complex ones as any computer based work is much more legible than writing by hand, also it is very easy to save your work for use another time.

The real downside of using spreadsheet is that they were developed for maths not for structural calculations, therefore it is very easy to make errors.

A peice of software I find works well is TEDDS, it is a professional calculation pad for engineers that runs in Microsoft Word. It has 1000's of pre-written calculations that every engineer could possibly need to use, if it's not there then you can write your own and store them away in the library. The web site address is

http://www.tedds.com.

hope this helps

 

[Mala]

Webwizard, I differ with your contention that spreadsheet in not basically meant for structural calculations...

To cite examples :

1.A colleague of mine has developed a complete design module for a prestressed slab-girder bridge deck system on Excel. If this is not structural design (in terms of both volume and complexity) then what is?

2. I am able to generate scaled engg. drawings with full annotations and dimensioning using Excel's own features (drawing objects and x-y scatter charts)

U see XL is very versatile - the limitations we set to its capabilities exist in our minds, not in the software.

Just DARE TO IMAGINE - it's a beautiful world out there...

 

[Structuralist]

I have been compiling various productivity spreadsheets from the structural engineering community and posting them on my own professional bulletin board discussion forum (Software productivity forum). The most elaborate of our spreadsheets is called Multi-Lat which was written after the 1997 UBC was adopted and is capable of performing both flexible and rigid diaphragm analysis for wood structures up to four or five stories in height. The spreadsheet was co-authored by David Merrick, SE and myself (Dennis Wish, PE) and then donated to the professional community for anyone who cares to download it.
The spreadsheet has some bugs and we are just starting to have other engineers who appreciate the value in the work that was done, add their efforts to the spreadsheet by correcting some of the errors and expanding the capabilities of the spreadsheet.
I coined the term - Evolutionware - to mean software that evolves by the efforts of those who use it and then is returned to the community where others may benifit.

Some of the posts I have read in this thead indicate that there is a great deal of templates out there that engineers can find and use for their benfit, but I've been searching for over two years and find that the availability of templates (Spreadsheets, Mathcad, TEDDS etc.,) is really slim pickings when you discover that much of what is available is not written for practical applications. For example, there is are a great many Mathcad templates for structural engineering - most written by the same person who creates his templates for education rather than code compliance and practical applications.

Until recently, there has been little (contrary to the one post on this thread) in the way of template for TEDDS - one of the tools available to Structural Engineers that has much more potential in practical application because of the ease in which conditional functions can be used (have you ever tried to find the minimum of three forumulas in Excel) and its ability to create reference tables.

The software productivity forum on my service (the Structuralist.Net) has the greatest traffic flow and downloads than any other forum of my site. Still, only a few (less than 10) professionals contribute to the library as the majority of professionals believe that their templates represent their competitive edge.

While this may have been true a few years ago, it is simply not the fact today. So why are so many professionals protecting their in-house creations rather than sharing them with the professional community in hopes of having their work develop into bigger and better tools by other creative developerso of spreadsheets, Mathcad or TEDDS programing skills?

I would think that the answer lies mostly in user greed or fear of losing that edge that does not exist. Furthermore, many of users of our tools fear retrobution for bugs or problems with the tools that they donate - or possibly the tool represents the quality of the work that they produce and this fosters criticism (hopefully constructive).

There are those of use who enjoy creating these tools and will continue to provide them without charge to the professional community. As long as a few members of the profession are willing to lend a hand, the number of templates will not only increase, but evolve. This includes creating a smoother user interface, error checking trial problems and even documentation that will help those without programing skills use the software.

Finally, and this has been one of my prime motivations, is that most software is beyond the affodability of small offices and independents who work on projects that do not have the same profitability that large offices obtain. The small office and independent engineer generally does not have the budget to afford the high cost of software and also does not have the skills to create the tools.

Some of us see this changing and decided to help, we have been accumulating low cost or free software that can equipt a small office or independent to compete. We have posted Sun Microsystems Star Office a low cost (no cost if you are willing to download from Sun) replacement for Microsoft Office Professional (and compatible) as well as a growing library of spreadsheets, Mathcad, TEDDS and CAD templates. Software developers such as CSC (TEDDS) have been very helpful in assisting our growth although TEDDS is still one forum that needs donations of templates.

If you are really concerned about creating a Free Cybrary of user created tools that have the opportunity to evolve by the help of other knowledgeble professionals then I urge everyone to find a forum posting these tools and support them by uploading your tools. On the Structuralist all developers are given full credit for their work and allowed to interact with those who download and evaluate the templates. In time, our TEDDS library will grow (I am currently evaluating TEDDS 5.0 for a software review in January or February and will donate any template that I create.

Finally, these tools are more than productivity accessories for our businesses, they provide other professionals with challenging and informative tools to learn from. I've learned a great deal from the creation of spreadsheets that have been posted on our site and isn't this the goal that most engineers seek?

Despite the reports of many tools, the usability of the tools is, for the most part, slim and often out of date or archaic (such as older Basic routines). The goal is to build upon the newer technology and share what we create without fear of losing what no longer exists - the competitive edge.

I would be pleased to discuss this futher with others as I would like to hear the arguments that support protection of in-house created tools. Remember that most of these tools are provided As-Is and it is up to the user to assure themselves of the accuracy of the tools and their compliance to current local code requirements.

regards,
The Structuralist The Structuralist

http://www.structuralist.net

http://www.structuralist.net/cgi-local/yabb/YaBB.cgi

 

[JAE]

Dear Structuralist.

Which takes more time: writing a spreadsheet (or other product), or downloading a spreadsheet and then doing a complete check of every cell, cross-reference, formula, etc.?

I believe that I learn an exceedingly greater amount by writing my own products. It forces me to work through the entire logic in a code provision or design method. I must learn the process first to write the software. To me, this is vastly superior to that of an engineer downloading a product and then spending hours trying to work through another's logic flow to verify the same.

As a professional, you should, ethically, exhaustively check and cross-check software before you use it on a project. With larger programs, you don't always have access to the source code so you must go through numerous designs or tutorials, checking the output against hand calcs or other software programs.

I don't have a problem with your thesis about creating a cybrary. For myself, I just don't see the benefit of downloading another's spreadsheet vs. doing my own. They usually don't take an enormous amount of time to create and you have confidence in what's inside the "black box".

 

[Structuralist]

Jae,
I have absolutly no argument with your reply - in fact, I agree with you entirely. I do have a few comments to show how a cybrary can be effective and productive and I offer these to you:
1. The majority of professional engineers do not have the ability to write their own routines (spreadsheets, TEDDS, MathCAD or VBA tools). These engineers historically work via manual calculations or rely upon commercial software such as Enercalc, StruCalc or a number of other libraries of utilities. Many drop by our cybrary and download smaller utilities that they will use.

2. No software should be used without an understanding of the principles of mechanics or the building code that the program professes to comply with. Still, a few minutes comparing the results of a user supplied spreadsheet with a user created hand calculation will generally give the user some confidence in the accuracy of the template.

3. Again, nothing replaces an engineers intuition and as I advise my students (I am a part-time adjunct faculty at the local community college - Elementary Structures), you must develop an intuitive feeling for the performance of specific materials and be able to recognize when a result is outside the range or reasonable solutions - in other words, you must be able to identify when your results are in error or inaccurate.

4. Most of us have become dependent on commercially available software to practice our trade. From my experience as a professional engineer, reviewer of structural engineering software and closely in touch with many of the developers of software that you might use, the quality control and Beta testing guidelines are not what we expect. Because the industry is small and the relative sampling of structural engineering beta testers is minute in comparison to other products used over a wide demographics, the accuracy of the software is often improved by reports from users as to inaccuracies and problem results. Once a software has established itself, the number of problems and bugs (I differentiate between the two) decreases as long as there are no major revisions to the basic code. However, I have seen many commercial packages fresh out of the gate which lack the quality control that we find in other commercial products. The importance to this is that an error which is not caught can be found during construction of a structural system.

5. Years ago I wrote a large spreadsheet program for seismic retrofit of unreinforced masonry buildings. This was at a time when a method known as the ABK (named after the authors) was being developed into the City of Los Angeles Division 68 (and later Division 88) and the city of Los Angeles RGA (later to be the UCBC Appendix Chapter 1). The beta testing was actually performed during plan-check's with Los Angeles Plan Review engineers who scrutinized the results and offered hours of debate as to the interpretation of the code expressed within the spreadsheet.
When the plan checkers were finally satisfied that the intent of the program matched their interpretation of the proposed code, confidence levels in the software soared. This did not neglect potential formula errors any more than a human being can make a mistake in his or her math. The intuition I mentioned above is about the only way to isolate mathmatics mistakes (computational or manually derived) to isolate these bugs.
In the end, the software yielded more accurate results on a consistant basis than manual calculations. This is the goal of any software.

Fortunately, there are few large spreadsheets and templates available for download - the majority are one page basic calculations that an engineer can verify in a short period of time. If the majority of tools are small enough to verify results by the user, then the number of times that the same template is reused, reduces the verification and learning process until it becomes insignificant and more productive than a hand analysis.

6. I won't speak for other sources of downloads (although I have found that those available on MathSoft's users forums are very reliable) but on our site, we offer two software related forums - one where the user can download the template and a sister site where users can discuss the software, debate the intent of the design code, and bring to the template developer ideas and constructive criticism to help produce a more productive tools with greater accuracy.
I don't want to give the impression that this is a tried and tested method of making tools available to professionals as we have not been around long enough to prove this. However, I can offer a professional opinion that we provide the resources to scrutinize the templates that are made available more so than offering a link to a download page that offers no more support than you can click on.
The idea behind a cybrary is not simply to upload anything that the server will store, but for users to help develop these tools and evolve them into reliable resources. If the spreadsheet has potential but also has a few problems, it is generally stated in download information. This is true of Multi-Lat - the large spreadsheet designed to the 97 UBC full compliance provisions for wood frame lateral design (multi-story). We are aware of the problems and make sure that the user who downloads the spreadsheet (or other template) is also aware of the deficiency.

7. Finally, the templates we offer are not protected. While this may make them vulnerable to potential problems by changes that users make, they are intended to help those who do not have the skills to create their own tools learn how. With a few exceptions, even Multi-lat (the largest spreadsheet we have available) uses only conditional statments and does not resort to VBA (Visual Basic) in its design. As one of the creators of the spreadsheet, it was my intent to write a tool that could be used on other spreadsheet programs (although you may lose some formating of cells) but most importantly, to show that even complicated design tools can be created using a few basic functions such as Lookup, Index and If conditions. There is very little more than this and once the timid user experiments with a spreadsheet and learns these basics, the world of potential opens up to them.

Jae, I don't think that any of the points I made conflict with your comments. It would be irresponsible of any professional to blindly use a software template without understanding the design and without the professional intuition required to identify when an output is inaccurate. But the one argument I would give is that you take it for granted that every professional has the knowledge, opportunity and time to create their own automated tools. In reality, those who do represent the smallest percentage of professionals in practice today.

Sorry for such a long response, but as you can see, I have given this idea much thought and believe that we can do something constructive and helpful for our peers who do not have the skills to create the necessary tools (or the financial resources to purchase many of them) so as to keep them competitive in practice and to encourage their education in creating these tools.

Regards,
Dennis S. Wish, PE The Structuralist

http://www.structuralist.net

http://www.structuralist.net/cgi-local/yabb/YaBB.cgi

 

[JAE]

Dennis: Appreciate you comments above. I don't disagree with anything you've said, directly. I'm just a little hesitent when I see young engineers grab spreadsheets and just trust them totally without at least some care, checking, understanding, etc. I haven't seen this a lot within the firms I've worked, but get the sense from others, and from some posts here in eng-tips, that this does happen.

I know I'd rather have my engineers develop their own as opposed to using outside sources as I believe they learn the design methods better. This is how many universities are teaching these days....requiring students to write programs that perform the subject tasks.

 

[Structuralist]

Again, no argument. There is simply no reasonable way to control the use of a tool and to prevent it from being misused. As long as engineering software is available, non-professionals will purchase it with the belief that they can absorb the cost normally paid to their consultant and increase their profits. There is a certain arrogance in this as it does not occur often - but it does occur none-the-less.
I don't want to start an argument on this list, but let's face it, unless an Architect actually invests the time to study and understand the principles of mechanics and the intent of the structural sections of the building code (and for those who do, most seek the credibility of the licensing in the process) then they should not practice enginering. However, in low risk regions of the US (and how many really low risk areas are there that are not affected by known design defects to high wind or flood) Architects and designers are confident that they can design structural systems for residential buildings. Unless the design is very simplistic, I don't believe they can design a structure that will perform as well as you or I can. They lack the intuition and understanding of the materials and how they perform as a system.
Still, many of these pseudo-engineers believe they have the skills to input values into a computer program and use the results to design the structural system of their projects. Yet how many times have you (and I) returned to the calculations we produced to re-evaluate the performance of the design - whether it is a visible line of deflection in an exposed beam, the drift of a portion of a building in relationship to another section, a change in the length of a drag-strut or revision to the number of nails installed, stiffness adjustments to shearwalls in adjacenet lines of resistance and many other important issues that the pseudo-engineer will never think of once the booklet of calculations is printed out from his or her initial run.
While I have my foot close to my mouth, let me go the rest of the way to suggest that many Architects, Designers or Builders have little faith or regard in the design of structural systems and feel that the analysis is nothing more than a formality needed to obtain a permit.

On the other hand, professionals in high risk regions have more faith in engineering. In nearly twenty years of private practice I see this with Architects in high seismic or wind regions, but NOT with contractors or builders who believe that a few extra nails makes their product hazard proof. These are the people who are the first to complain of over-design by the engineer.

My point is that as long as the Architect who believes that structural engineering for small residential and commercial projects is nothing more than a formality will be most likely to purchase a piece of software with the intent of pocketing consultants fee by doing the work himself and in the process increasing his profits without the owners knowledge. If this had not happened to me on a number of occaisions, I would not be so quick to open my mouth (I am still not claiming that this is the rule, but rather the exception to the rule).

We shouldn't prevent the creation of productive tools because we fear their misuse. This is something that we can not control any more than trying to warn the public against the person who takes one drafting course and then goes into private practice designing homes. The law allows them to do this and we have little recourse.

One recourse we do have is to make our Internet infrastructure open to the public so that we as professionals in the building industry are in a position to educate the public who controls the pocketbook to have the entire picture of the problems within the industry so that they can make informed choices.

I know that this is a major goal of my website and discussion forum and hope the same is true of the Tecumseh Group's goals as well. While the building industry grows farther apart in philosophy (protecting conventional prescriptive construction on one side while creating more restrictive design methods for irregular structures) we indirecly provide enticement for those who seek profit without regard or faith to engineered performance.

Again, I am running off at the fingers. My point is only "software doesn't kill people, unqualified software users do!" (hows that for an analogy). Reform the industry, make prescriptive construction meet the minimum standards required of engineered solutions and prevent non-professionals from designing building structures will do much more to protect the public than developing a software that can be abused.

Dennis The Structuralist

http://www.structuralist.net

http://www.structuralist.net/cgi-local/yabb/YaBB.cgi

 

[narenr]

JAE and Dennis are having a nice discussion about use of spreadsheets prepared by others. Both are right in their discussions.

In all computer programs, garbage in is garbage out. The user needs to be qualified engineer in the particular field, free spreadsheets or software in wrong hands could be disastrous.

Spreadsheets are not full time software, you see what's happening inside unlike a commercial software. Spreadsheets well prepared gives a proper sequence of calculation steps which the user can easily validate and feel the numbers.

True anyone would be hesitant to use spreadsheet prepared by others. On the contrary, a properly documented spreadsheet is the most wanted tool for engineering calculations since it is easy to customise and revise to suit the requirement.

JAE has a good point, right from my school days, I learnt most things when I wrote my own programs. Again using ready avialable tools should not be discouraged.


Narendranath R
narenr@narendranath.itgo.com

http://www.narendranath.itgo.com

Pipeline engineering is made easy with state of the art computer software, visit

http://www.narendranath.itgo.com.

 

[bylar]

To Structuralist
You appear to be a little long winded and a little premature!
I went to your site and (well)????
Email me when you are up and running

 

[Structuralist]

Bylar,
There is no question that I am long winded - a friend once wrote publically that I tended to be verbose. I am a fast typist and although this isn't an excuse, I find that it is easier to explain myself fully rather than leave questions as to my intent.
As to your comment on my website, I appreciate the visit, but what you saw is current a re-construction of the site and it is at least two months away from completion. I've posted it only to get other familiar with the new navigation features. If you logged on last night you would have found more information and changes in appearance.
Don't judge the lack of information on the site as yet. There is plenty of discussion topics still on my old UltraBoard and YaBB Gold 1 forums that have yet to be tranfered to the new YaBB SE forum. To be specific, I have changed web hosting services. The new web host will not allow what is termed a flat-file discussion forum such as Ultraboard or YaBB Gold. They will only allow a newer bulletin board written in PHP and using MySQL database formats. While this means little to those on this forum, it is required because these new forums use less bandwidth and resources which this web-host requires to stay affordable.
Finally, I have not connected the links to the Spreadsheet downloads yet. It is on my todo list, but I probably will not have the time until this next weekend to do it as I must earn a living.
I would not normally mention it here (as I don't think it is allowed) but you raised the question and I felt compelled to reply - I did not have a private e-mail to reach you.
I hope I haven't offended anyone in the process. The Structuralist

http://www.structuralist.net

http://www.structuralist.net/cgi-local/yabb/YaBB.cgi