Spreadsheet blunders aren't just frustrating personal inconveniences. They can have serious consequences. And in the last few years alone, there have been a myriad of spreadsheet horror stories.
In August 2023, the Police Service of Northern Ireland apologized for a data leak of "monumental proportions" when a spreadsheet that contained statistics on the number of officers it had and their rank was shared online in response to a freedom of information request.
There was a second overlooked tab on the spreadsheet that contained the personal details of 10,000 serving police officers.
[...] In 2021, Crypto.com, an online provider of cryptocurrency, accidentally transferred $10.5 million (£8.3 million) instead of $100 into the account of an Australian customer due to an incorrect number being entered on a spreadsheet.
The clerk who processed the refund for the Australian customer had wrongly entered her bank account number in the refund field in a spreadsheet. It was seven months before the mistake was spotted. The recipient attempted to flee to Malaysia but was stopped at an Australian airport carrying a large amount of cash.
Industry studies show that 90 percent of spreadsheets containing more than 150 rows have at least one major mistake. This is understandable because spreadsheet errors are easy to make but difficult to spot. My own research has shown that inspecting the spreadsheet's code is the most effective way of debugging them, but this approach still only catches between 60 and 80 percent of all errors.
[...] To break the cycle of repeated spreadsheet errors, there are several things organisations can do. First, introducing standardization would help to minimize confusion and mistakes. For example, this would mean consistent formatting, naming conventions, and data structures across spreadsheets.
Second, improving training is crucial. Equipping users with the knowledge and skills to build robust and accurate spreadsheets could help them identify and avoid pitfalls.
Finally, fostering a culture of critical thinking toward spreadsheets is vital. This would mean encouraging users to continually question calculations, validate their data sources, and double-check their work.
(Score: 4, Interesting) by JoeMerchant on Wednesday January 31, @06:05PM
For 15+ years, my company has acknowledged that spreadsheets are "software" and as such need the same use case assessments (including data privacy), validations, version control, training, etc. as any other in-house developed software.
>In 2021, Crypto.com, an online provider of cryptocurrency, accidentally transferred $10.5 million (£8.3 million) instead of $100
Yeah, then there was Mt. Gox, IIRC it was a playing card trading platform adapted for use trading BTC... fun times, anybody who didn't see that problem coming paid some "common sense tuition" if they had BTC on deposit at the fall. If you're going to trust inexperienced strangers located all over the globe to handle your banking transactions... my advice would be to consider carefully the risk/reward ratio.
>It was seven months before the mistake was spotted. The recipient attempted to flee to Malaysia but was stopped at an Australian airport carrying a large amount of cash.
My second paycheck from my first "real job" they fat-fingered 800 hours worked into the system instead of 80. Unfortunately, they caught the mistake before the direct-deposit transfer was even made, but due to the nature of the system there was no way they could stop it so they got me to approve a transfer back to them... I briefly considered attempting an early exit strategy, after all, I had barely worked there 3 weeks and here comes an $8000 deposit... I decided that if it had been $80,000 I would have tried a little harder to think of a way to grab and go (my first home, purchased a couple of years later, sold to me for $80,000).
>this would mean consistent formatting, naming conventions, and data structures across spreadsheets.
YEah, good_luck with thatOne... especially the data structures.
🌻🌻 [google.com]
(Score: 5, Funny) by gznork26 on Wednesday January 31, @06:26PM (3 children)
Having had to clean both spreadsheets and databases in the course of my jobs, I found that a common lazy 'trick' that some users try is solving the problem of a missing spreadsheet column or database form field by putting their important data into a spot that wasn't being used for anything else. For example, if there are places to put multiple line addresses, but not all are needed for a given record, I've found notes entered there, which screws up any addressing code. I usually approached suspect data files like those as an adventure, because you never know what you might find stuffed into them.
And for an example of such bad data being fed into a program, I once tested a program that had no validity check on data fields before operating on them. Simply logging in with an excessively long password wiped its database, and it got worse after a successful login. Amateurs.
Khipu were Turing complete.
(Score: 5, Interesting) by krishnoid on Thursday February 01, @12:27AM (2 children)
From Isaac Asimov's Foundation:
And this was written in the 1970s. It looks like we're at the "poor repairs" -- or as you said, validity check -- stage of software technology development. We're not at the point of restricting data transfers, but I guess the question is whether we'll move towards better data integrity and security, or away from it over time.
Rust is a good approach towards this goal, since it prevents compilation of at least some unsafe code constructs. Maybe that's what it'll take, domain-knowledgeable people recommending "building codes" for software.
(Score: 1, Insightful) by Anonymous Coward on Thursday February 01, @03:47AM
(Score: 4, Interesting) by turgid on Thursday February 01, @08:17AM
I really do need to learn a bit of Rust. However, my gut feeling is that making compilers more and more complicated isn't a great way forward. I think a language that lets you build up your own safety infrastructure appropriate to the problems you have to solve might be a better approach. There are pros and cons, though.
Does anyone actually design their programs these days before they start hacking?
I refuse to engage in a battle of wits with an unarmed opponent [wikipedia.org].
(Score: 3, Interesting) by Anonymous Coward on Wednesday January 31, @07:19PM (2 children)
Not exactly sure how this could work, but do any spreadsheets offer some kind of debug or "view codes" mode? I'm imagining every cell doubling in height, with the lower part of each cell showing the first part of the formula or other code in the cell...so you don't have to mouse over every damn cell to see where it came from. Maybe color coded, data all has the same background, etc.
Any other wild ideas?
I started with VisiCalc on an Apple ][+ and several others since then. However, I've never (thankfully) tried to make or debug any really big spreadsheets with any complexity in the coding.
(Score: 3, Interesting) by VLM on Wednesday January 31, @10:23PM (1 child)
Maybe start in Excel with tapping control-backtick a couple times and comment on needing more or less than that.
This a a pretty standard problem with "productivity software" that has so many features that most people don't know the concept of a feature exists so they can't search for a feature so they can't search for how to use a feature so they can't use a feature, even if its really helpful.
"But AI will fix it all" uh huh...
Note that you can really mess with coworkers who leave their PC unlocked by control-backtick a spreadsheet they've left open. Its not as funny as sending emails "from them" or changing their monitor, mouse, or desktop background image, but its still kind of fun.
This was our standard practical joke "back in the day" for unlocked PCs with open spreadsheets; its possible excel has changed in the last decade or so. Another common one was slightly changing the text colors. People will not tolerate immediately going to pale yellow text on a white background or extreme nonsense like that, but you can change colors one shade per day and they won't notice for some time.
If excel has removed control-backtick or control-tilde or whatever your localization calls it, "back in the day" that used to toggle formula vs data mode. Most people think excel only has a data mode. Not so; formula mode is (was) quite handy sometimes.
(Score: 1, Insightful) by Anonymous Coward on Thursday February 01, @03:48AM
No idea if it still works in Excel, but I just tried ctrl-` in Libre Office Calc and it works as you describe -- Thanks!
Not quite what I had in mind for a debug mode, but the quick flip back and forth is pretty good. I'd really like to see the data and formulas at the same time, and maybe some color to make it easy to spot cells that differ from their neighbor (when the intent was to make them like their neighbor).
(Score: 3, Insightful) by Anonymous Coward on Wednesday January 31, @07:25PM (2 children)
Spreadsheets are not a forms entry system.
IBM and all the other forms-based field screen transaction systems
figured this out half a century ago.
(Score: 4, Insightful) by VLM on Wednesday January 31, @10:43PM (1 child)
Those who don't know CICS are doomed to reinvent CICS, poorly.
Those crazy enough to be running turnkey MVS (if you have to ask, this dark knowledge is not for you mere mortals) can at some effort install a runtime system creatively named KICKS.
http://www.kicksfortso.com/ [kicksfortso.com]
My limited understanding is KICKS is like running CICS in single user interpreted mode; which is good enough for development but you wouldn't want to run a million terminal bank on it. You can recompile and run the same code under CICS and then it'll work at a million terminal bank. Also CICS is a licensed unavailable IBM product and KICKS is FOSS-ish. Also CICS concurrent transaction processing supposedly scales to a million simultaneous terminals, whereas KICKS, as I understand it, scales to precisely one single user, which makes the middleware concurrency logic somewhat simpler LOL. Which is why KICKS is free and IBM charges seven figures for CICS.
If you did "weirdo mainframe shit" like I did in the early 90s, you'll get some mixture of flashbacks and PTSD if you look at the screenshots on the above website. I hadn't seen CICS code in maybe 30 years when someone recently showed me KICKS. Whoa.
The sad part is I'd rather work on CICS in 2024 than work in modern webdev and its doing fundamentally the same thing its just CICS is somewhat less brain damaging.
Moshix has a nice video a decade ago about installing KICKS on the MVS turnkey in Hercules; AFAIK not much has changed since.
If the previous made no sense, well, there be dragons enter at your own risk... But if you actually know what CICS is you'll get a belly laugh out of this post and trip down memory alley.
(Score: 1) by pTamok on Thursday February 01, @10:41AM
On the other side of the fence, for character-oriented terminals, there was VAX FMS (VAX Forms Management System), and a later product, DECFORMS [vmssoftware.com]
Extract from VAX FMS manual, 1984 -
VAX FMS could use a back-end transaction processing system, but did not have to. I consulted on a system that used VAX FMS, a standard programming language (for the time) and the built in VMS Record Management System to run applications that easily outperformed the available databases using SQL. But the water has gone under that bridge, reached the ocean, evaporated, come down as rain and gone under the bridge several times since then. The amount of processing power available to developers since then has increased by orders of magnitude, and we still produce systems with slow, unhelpful (G)UIs. I'm not sure that generating poor-quality applications faster is the right goal to have.
By the way, I am in full agreement that CICS was, and is, very impressive. There's no way I'd say DEC could produce a competitor at the high-end. The VAX 9000 [wikipedia.org] was one of the many failed projects that killed DEC (the organisation that employed me had one, and my role was closely associated with it).
(Score: 4, Insightful) by darkfeline on Wednesday January 31, @08:24PM (1 child)
Spreadsheets are a graphical functional programming language+IDE with a low barrier of entry, yet a pretty bad UI overall. So bugs in user code are to be expected.
Perhaps there should be some additional separation between the presentation/data and logic layers, or more UI allowances to reflect data flow.
Join the SDF Public Access UNIX System today!
(Score: 2) by JoeMerchant on Wednesday January 31, @11:08PM
>Perhaps there should be some additional separation between the presentation/data and logic layers, or more UI allowances to reflect data flow.
All you are doing with that is making it less accessible to the general user base, restricting yourself to programmers who can understand the abstractions. It is along the same lines as requiring everything to be in C++, because very few programmers even admit to understanding how to code in C++ and the ones that do tend to be above average as compared to the whole pool of people who call themselves programmers.
Yes, quality would improve, but you'd still have the middle managers using the "easy to program" spreadsheets for their critical tasks.
🌻🌻 [google.com]
(Score: 5, Insightful) by VLM on Wednesday January 31, @09:43PM (2 children)
The real problem is lack of ability to think.
I've run into plenty of managers whom see spreadsheets as a presentation technology, this is a "word processor for columns" where you calculate your data sometimes using a calculator and then manually enter it so its formatted nicely.
There are people using spreadsheets who don't understand "by-value" vs "by-reference". I don't mean detailed syntax and procedural issues and troubleshooting strategies (although they do need those) I mean the basic concept of the definition of them.
The idea of a function, also sometimes lost on those people.
The idea of storage vs presentation being separate... "how do we store that number with commas and red if its negative" and they think I'm swindling them if I use a format instead of cut and pasting a string or worse a graphic.
The fear that a civilization that can't use a card catalog or spreadsheet in an intelligible manner is somehow going to know how to use AI to accomplish anything is pretty funny. Human programmers have been managing their managers since the vacuum tube era but AIs don't push back. Imagine the dumbest human manager you ever worked with trying to get an AI to do anything... without human shields to protect them anymore I suspect most of the people losing jobs due to AI are going to be in management not lower levels. We will see in the near future...
(Score: 4, Touché) by stormreaver on Wednesday January 31, @10:36PM
Hell, I've met supposed programmers won't don't understand that distinction.
(Score: 2) by turgid on Thursday February 01, @08:10AM
The problem with these AIs is going to be like the outsourcing problem times ten. The managers, not knowing what they don't know, are going to turn to the AIs to get work done formerly by engineers. Not only will the managers not understand what to ask the AIs, what they are asking when they do ask but AIs are weak-minded fools who hallucinate and change their minds when fed nonsense. A couple of sentences of management-speak and misunderstanding will have them generating utter dross which the managers will accept unquestioningly. "Draw a red square with only three sides using green pens."
Society is in for a big fall soon due to this. Mark my words.
Like erstwhile mighty engineering companies who outsourced their engineering and failed, there's going to be another huge change in the way companies work and it's going to hurt even worse than last time.
I refuse to engage in a battle of wits with an unarmed opponent [wikipedia.org].
(Score: 4, Insightful) by VLM on Wednesday January 31, @10:08PM (5 children)
I can outdo this, some time ago, all my coworkers received some corporate communications in a famous word processor format where the execs (well, their AA anyway) censored some "sensitive/private" data by deletion and never considered anyone could be competent enough at using that word processor to have the idea to smack the "undo" key combo a couple times. Its possible at that time, that no one above grunt level at that company, or at least no one in the executive suite, was experienced enough with word processors to know the concept of "undo" existed as a technology in word processors. Undo exists in spreadsheets, CAD programs, graphics art programs, some unrendered video project formats... its fun, sometimes informative, sometimes hilarious.
(Score: 2, Funny) by Anonymous Coward on Thursday February 01, @03:58AM (1 child)
Along these lines, I just received some tabular data in a several page pdf. Some of the data items were missing (blank spaces), because those parts of the data set haven't been finalized yet. However, searching for a number (that appears in several places) also found & hi-lited a blank space.
It appears that the creator of that pdf changed the color of the text in that spot to white (on white), but didn't delete the number before converting to pdf...
(Score: 3, Funny) by cereal_burpist on Thursday February 01, @07:57AM
Similar to people who think they are redacting a PDF by placing solid black rectangles over some text.
https://en.wikipedia.org/wiki/Redaction#Computer_media_and_files [wikipedia.org]
(Score: 1, Insightful) by Anonymous Coward on Thursday February 01, @05:52AM (2 children)
In fact if it does so nowadays, it would probably be considered a serious bug.
(Score: 2) by turgid on Thursday February 01, @08:12AM (1 child)
Nowadays, yes, but this was a famous mis-feature in older versions of Microsoft Word.
I refuse to engage in a battle of wits with an unarmed opponent [wikipedia.org].
(Score: 1, Interesting) by Anonymous Coward on Thursday February 01, @09:16PM
Yep. I remember wondering why a Word file was so big, for just a couple of pages of text. So I opened it in EMACS to see what was in there. In the "scrap area" there was most of a completely different document that must have been opened first (perhaps to copy the format or other style elements) and then the user thought they deleted all of that other document.
Wasn't there a way to tell older Word to clean up a file and get rid of all the deleted text? Not obvious, but buried in a menu?