"How do you know your spreadsheet is right?"

[Expunge]

One way is not to follow the oversight in the demo here. at 3:30 he inserts a column, but doesn't fix the grand total or average columns.

http://office.microsoft.com/home/vid...HA102376171033

[campbell]

PDF: Goal-directed debugging of spreadsheets
http://web.engr.oregonstate.edu/~erw...ug_VLHCC05.pdf

ABSTRACT:
We present a semi-automatic debugger for spreadsheet
systems that is specifically targeted at end-user programmers.
Users can report expected values for cells that yield
incorrect results. The system then generates change suggestions
that could correct the error. Users can interactively
explore, apply, refine, or reject these change suggestions.
The computation of change suggestions is based on a formal
inference system that propagates expected values backwards
across formulas. The system is fully integrated into
Microsoft Excel and can be used to automatically detect and
correct various kinds of errors in spreadsheets. Test results
show that the system works accurately and reliably.

Easier to read website: GoalDebug

Alas, I see no links on how to get a hold of the software itself. May be they're developing it for commercial use, and don't have a full release yet. Or perhaps they see it just as an academic project.

[campbell]

So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?

[E]

Originally Posted by campbell

So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?

I think one huge problem is poor or no documentation for complex spreadsheets. This would include problems like VBA code that lacks comments or just really large, complex spreadsheets taht lack an "Instructions" tab or accompanying document that outlines their purpose and rules for updating, maintaining, and using.

Poor documentation results in the following problems/risks:

1) Significant lost productivity for future users (technical or not) as they have to spend hours to days to months trying to understand the spreadsheet.

2) Misuse due to lack of understanding of the spreadsheet.

3) Lack of use of the spreadsheet or using spreadsheet to only part of its potential.

4) Having to rebuild the spreadsheet by a future user (call it "re-invent the wheel risk").

I think this risk is particularly high in environments with high turnover. I think advanced spreadsheet users tend to design spreadsheets in a "for my eyes only" way. I'm guilty of this myself. The key is realizing that your product is near useless in the future if you don't document it or don't take the time to "teach" it to someone else.

[GadgetGeek]

Originally Posted by campbell

So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?

I have a lot of experience with dealing with the floating point/loss of precision issue. One of my first tasks at my previous employer was re-writing our illustration system (actuarial version that was used to validate ITs version). FORTRAN was the language (ITs was in COBOL). The big "issue" was we needed to be able to match exactly with what would be printed on the policy page as sometimes our runs were used for client service work (often times with legal involved). So I had to use integer math for everything and keep track of how many decimal points were in each of my numbers. Not fun. I resorted to creating an iMult and iDiv function to call to keep things sane from a readability/debugging perspective. When we did the annual dividend updates and printed new rate pages for the agents (pre laptop days ), these runs took a very long time. IT was not happy with me, until we got something worked out to run these at more opportune times.

[JMO]

Originally Posted by campbell

So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?

My vote is to cover the end-user issues. Although I personally would also like to read the technical one.

[campbell]

I'm on the EUSPRIG mailing list (it's in Yahoo groups I think.... here it is - obviously, it's not just Europeans on the group), and end user concerns was something that came up recently, in response to this article: http://www.cfo.com/article.cfm/11525407?f=insidecfo

Sloppy Spreadsheets: Readers Speak Out
Readers make some pointed additions to CFO.com's "worst practices" list.

Quick summary:
- formatting sucks, not set up for printing
- missing info: no context/title on each individual sheet, no numbering of sheets for printout (important if you'll be distributing hard copies around -- you can't refer to tab "Sheet1")
- things that look bad when reformatted because of text bleeding over into adjoining cells
- no summary of all tabs for a large file - I have this problem with some of the work I've come across.... a bunch of tabs with similar names, and no index/table of contents
- hard-coded numbers (I've mentioned this in a couple of articles)
- hiding columns/rows with sensitive information in it... and leaving that in when file is distributed to external people. The worst I've seen here is someone changed the format to white on white.... easy enough to change it to be readable. They recommend "protecting" a cell, but that just protects against people inadvertently coming across sensitive info. It is relatively easy to "break" Excel passwords, and I've done it in the past with legacy spreadsheets where the originator was no longer around.
- using a spreadsheet where a calculator will do (I use Excel as an informal calculator all the time. I don't see the problem here, unless you suck at Excel. But then if you suck at Excel with calculation, you'd probably suck with a calculator.)

[Phil]

Using the Excel row numbers as the primary key for a table (one of my department's main spreadsheets has this quality) - the table doesn't even have column headings, so I pity the fool (i.e. me) who decided to insert a row at the top to type in a description for each column, and inadvertently destroyed the spreadsheet.

Is it so difficult to put in a hard-coded column of numbers that go 1, 2, 3, 4, ... so you're independent of the position on the page?

[campbell]

Originally Posted by Phil

Using the Excel row numbers as the primary key for a table (one of my department's main spreadsheets has this quality) - the table doesn't even have column headings, so I pity the fool (i.e. me) who decided to insert a row at the top to type in a description for each column, and inadvertently destroyed the spreadsheet.

Is it so difficult to put in a hard-coded column of numbers that go 1, 2, 3, 4, ... so you're independent of the position on the page?

I actually use ROW() for this purpose.... but then I subtract off ROW(NamedColumnHeader) so that people can add in rows at the top if need be. (I like to start my row count with 0.... see here.)

[Former Actuarial]

Originally Posted by campbell

I actually use ROW() for this purpose.... but then I subtract off ROW(NamedColumnHeader) so that people can add in rows at the top if need be. (I like to start my row count with 0.... see here.)

and likewise, using dget instead of vlookup.