Around the world we rely on #spreadsheets to manage projects & assemble/analyse data... so the news that research suggests 90% of spreadsheets over 150 lines contain one major error.
Not only that, while easy to make, such errors are hard(er) to find.
As so often, what seems like a technical issue of little major consequence, as Simon Thorne (Cardiff MU) explores here actually they can be the root of all sorts of significant problems.
We need to be clearer about them!
@ChrisMayLA6 Computer Programmer here. I've seen millions written off when spreadsheets are used as proxies for proper accounting practices. I've seen valuable analysts waste half their hours every month cutting-and-pasting data b'twn sheets. I've seen development teams struggle to place complex, fragile, error-prone formulas into Excel in VBA all because the project leads wouldn't tell the users to sit down when they demanded a spreadsheet. Training? Standardisation? Don't make me laugh.
The claims dept of a well known insurance company had a guy working in the department who was a bit of an amateur wizard with Excel and had produced more than 100 spreadsheets for various purposes, for his dept. Unfortunately, the functioning of the claims dept had come to be totally dependent on these damned spreadsheets which were undocumented and developed to no proper standards. Finally, the day came when the developer reached retiring age and the IT dept were asked to take over all these spreadsheets for maintenance and update. The IT dept said "sure - that will be £25,000 per spreadsheet" which came to substantially more than the whole IT budget (software and hardware) for that dept!
@Paulos_the_fog @boggin @ChrisMayLA6
Not mine, but raw truth.
@Ruskin_Marc @Paulos_the_fog @boggin @ChrisMayLA6 I am certain that "Excel wizards" regularly re-invent SQL by accident, using their own brain-damaged syntax derived from VBA and minus most of the useful bits that have been added since 1987.
There should be a feature in Excel whereby if a spreadsheet user is building something with more than 50 rows, two sheets, and macros and refuses to let them until they've taken an introductory course on relational databases.
@cstross @Ruskin_Marc @Paulos_the_fog @boggin @ChrisMayLA6 Today's Excel (and Google Sheets) are not your grandfather's Lotus 1-2-3. It has support for tables, dimensional operations, and for the last couple years it even has lambda calculus, making it functional and Turing-complete! No need for VBA at all.
With a bit of discipline, it's entirely possible to make robust, maintainable spreadsheets. And thanks to the LLMs it's easier than ever.
@ttarchala @cstross @Ruskin_Marc @boggin @ChrisMayLA6
The key phrase in your post is
"With a bit of discipline, it's entirely possible to make robust, maintainable spreadsheets."
The problem is usually that the spreadsheets created by users, as opposed to IT professionals, are normally undocumented, amateurishly designed, implemented to no particular standards and are thus virtually unmaintainable.
