(Excel-based) Spreadsheet Validation

This is an old article, resulting from a presentation I gave (quite) a few years back at an RQA (BARQA as it was then) event. I’ve not updated the content other than for layout, but have noted where the links have become out-of-date (and updated them if possible). As promised, here is the example spreadsheet. It was designed for Excel 2003, but should function correctly in other versions. Note that all the passwords are for the sake of this demonstration: ‘password’. In production use you would not make the passwords the same (nor so simple).

Example Spreadsheet (Excel 2003 spreadsheet) The MD5SUM of the example spreadsheet is b08a1eee108b61c3b8512834d3cf2717

The presentation (with the links) is also available in various formats:-

• Validating Excel-based Spreadsheets (Powerpoint)
• Validating Excel-based Spreadsheets (OpenOffice)
• Validating Excel-based Spreadsheets (PDF)

Useful links

• Professer Ray Panko’s site: Ray Panko’s site is in the process of moving: New site http://panko.shidler.hawaii.edu/
• More research to frighten you: http://arxiv.org/
• …and more figures… http://www.isaca.org/
• MD5Sum software (check with your administrator — you might already have a utility for this or for similar checksums). http://www.pc-tools.net/ This one is a command-line version The site exists but the Windows software (but not MD5 checksums) is now flagged as obsolete. (you can drag-and-drop a file onto it if you put a link onto your desktop). This is a Windows program: there is usually a similar utility available in *nix

I've been asked to put up the ‘Active Agent’ tables.

None of the test values were prefixed with ‘=’ to indicate a calculation. Changing the format of the cells to (say) number also converts any existing values, thereby changing them. If you think that this action is correct (after all, 12/3 equals 4) even though it changes the original value, you might consider why 22/22/22 doesn’t result in .04545454 (22 divided by 22 divided by 22). Remember, 12/3 might be a study code or some other data.

The test values were NOT prefixed with ‘=’ (to indicate a calculation). Shaded (yellow) boxes are the what would be expected to be the correct output (that is, either an error box is displayed or the test value is stored and displayed correctly: only 6 out of 15 are correct.