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!
@Ruskin_Marc @boggin @ChrisMayLA6
A mobile phone provider I was contracted to, had an Excel spreadsheet containing the details of all their transceivers along with the frequencies assigned to each one and a load of other pertinent data. As the network expanded, more and more transceivers needed to be added to this monster spreadsheet such that it took anything up to 10 minutes to open the damned thing! I was recruited to move all this data to a database where it should have been stored in the first place but met implacable resistance from all the guys working on frequency allocation even though it would have made their lives much easier if I have succeeded. In the end the rollout of frequency hopping saved the day as no specific frequency is allocated to network nodes using that technology!
@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 @boggin @ChrisMayLA6
The fault very often seems to lie with tragically unresponsive IT departments. The users don't see their IT department as helpful and willing to respond flexibly and rapidly to their needs; usually exactly the opposite! The result is that the users are tempted to bang something together using Excel rather than ask IT to do something a bit more professional.
@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 @Ruskin_Marc @Paulos_the_fog @boggin @ChrisMayLA6 ITYM "thanks to the LLMs it's easier than ever to make sheets you simply don't understand but that hand you answers that sound plausible even if they're wrong".
@ttarchala @cstross @Paulos_the_fog @boggin @ChrisMayLA6
Did you see my follow up post, which contains someone creating a full 16bit computer within Excel?
That said, just because you can, doesn't mean you should ;-)
@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.
@Paulos_the_fog @boggin @ChrisMayLA6
It would, of course, be remiss of me not to mention that if you recognise this problem in your own organisation and want to do something about it, I may be able to help.
https://www.aru.ac.uk/study/degree-apprenticeships/apprentice/our-courses/data-science
@Ruskin_Marc @boggin @ChrisMayLA6
I'd love to do one of those data apprenticeships but I'm 73 living overseas and retired so I very much doubt if it would be open to me!
@Paulos_the_fog @boggin @ChrisMayLA6 Your age would not be a barrier, to-date my oldest apprentice enjoyed his 60th birthday in the year he graduated, but, sadly, the living aborad part does put the kybosh on that plan, as funding is only available for our #apprentices whose work is >50% in England.
@Paulos_the_fog @boggin @ChrisMayLA6
The algo decided to troll me hard yesterday. If Excel is good enough to build a 16bit computer out of it's probably good enough for mission critical projects, right?
@Paulos_the_fog @boggin @ChrisMayLA6
Not mine, but raw truth.