(Excel-based) Spreadsheet Validation
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).
The MD5SUM of the example spreadsheet is b08a1eee108b61c3b8512834d3cf2717
The presentation (with the links) is also available
Validating Excel-based Spreadsheets (Powerpoint)
Validating Excel-based Spreadsheets (OpenOffice)
Validating Excel-based Spreadsheets (PDF)
Useful links
- Professer Ray Panko's 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 utiltiy for this or for similar checksums). http://www.pc-tools.net/ This one is a command-line version (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.
| Test Value | Integer format
(and stored value) |
General Format |
| 12-3 | 39519 | 12-Mar (displayed) 12/3/2008 (stored) |
| 12/3 | 4 | 12-Mar (displayed) 12/3/2008 (stored) |
| 12-Mar | 39519 | 12-Mar (displayed) 12/3/2008 (stored) |
| 12/3/07 | 39153 |
12/03/2007
12/3/2007 (stored) |
| 22/22/22 | 22/22/22 | 22/22/22 |
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.
| Test Value | Integer format | General Format | Number format (2dp) |
| 12-3 | "error" | 12-Mar (displayed) 12/3/2008 (stored) |
39519.00 |
| 12/3 | 4 | 12-Mar (displayed) 12/3/2008 (stored) |
4.00 |
| 12-Mar | "error" | 12-Mar (displayed) 12/3/2008 (stored) |
39519.00 |
| 12/3/07 | "error" | 12-Mar (displayed - note this is 2007) 12/3/2007 (stored) |
39153.00 |
| 22/22/22 | "error" | 22/22/22 | "error" |
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.