I even have an Excel spreadsheet that helps me solve Wordle.
Is it Excel-specific though? I've made these kinds of errors with Excel (though not with stakes this high). I've also made them a lot when doing math on paper. And I've also made them in C++, Common Lisp, Matlab, Python, R and JavaScript. Now, with those other tools, it's easier to spot an error in a formula on review - but in Excel, it's easier to spot the intermediary results being off, so it's a wash.
I think the thing to learn is to be more careful, to sanity-check intermediary calculations; these kinds of errors are about being momentarily confused, and will happen regardless of the tool you use.
I am very far from being an Excel guru, very far, but one thing I have found useful is having everything come out to an intermediate result, and calculate against those intermediate results. If you see weird numbers in the middle, then end result is likely wrong, figure out why your intermediate results are messed up. If you want to be fancy, you can even error bound your intermediates if you know your valid ranges.
For instance, you intuitively start with just a column of numbers, and then when you forget what they are you move the column down one and put a title above it, etc. Working this way I often end up with formulas that are off by one or two cells even though things should be adjusted, and it's hard to audit without mousing over the formulas and just looking.
If I'm trying to write a tool for others to use I'll do something more formal like putting the data on its own sheets and naming them. That helps a bit, but means that all my data is hidden on different sheets and I can't get a holistic view of the problem. And to be consistent in my formulas I'm tempted to use this model even for data that doesn't need it (not an array, for instance). And it still doesn't help for array-bounds type problems.
I think everyone has slightly different workflows and this has no real solid answer. There’s always some trade offs. Just working in excel a lot helps, if you’re working on varying levels of complexity. I do it as a job and often helping others so I have a lot of exposure to different problems and input data types and even the desired outputs.
The intuitive example you mentioned never happens to me for example because of 2 things; 1) I intuitively leave space to add a header, it’s such a common thing to do, my intuition knows to account for it up front 2) if for some reason I ignored #1, I know how to move things without breaking the formula references and when to employ different approaches to that problem. Things like how copy and cut differ, Inserting a row above row 1, etc. One of my little hacks is avoiding referencing a range like A1:C5 and instead will make it A:C if it’s just a basic table of data. Your file may have a ton of references to this table of data once it’s built out and adding a row of data then requires some manual housekeeping which introduces an opportunity for a bug to occur. With my approach, I can add or subtract data or rows and non of the range references need to change. (Someone May point out that you could expand the table range for 6 rows in a way that the other references would expand, it’s true but I don’t design my files for that, because adding a row may have break something else to the right of column C).
A lot of the way i use and setup the data within a spreadsheet is in anticipation or avoidance of future issues. This only comes with experience. Which is kind of my answer to your more complex example. When developing for others, you’re usually trying to hide the data and complexity of things and expose only the useful bits for that end user. It’s not perfect and is very annoying that I have to hide and unhide things constantly to inspect the functionality. But that’s just the way it is. I’ve done some things like written little macros that hide all of the background stuff if a keyword is in the file name (imagine having a dev and prod version of the file, if prod is in the file name when saving all the cleanup and hiding of things happen. That’s what I distribute. The dev version is the same and where I work. When I save that file prod is not in the file name so everything in that macro doesn’t execute.)
For a while Excel was messing up basic statistics functions too. Wouldn't be surprised if there was something else not quite right in there.
Either way you’re likely trusting that the underlying code is error free. But most people aren’t checking their python imports. They may or may not be aware of the problems floats can introduce. So on.
I think how confident you feel with excel will depend of what you do with it and how you were taught to use it. Years of consulting basically drilled into me that all my tables should be built as if they were going to be delivered to a client who will need to understand them at some point. If you lack structure however, it quickly devolves into chaos.
I did not believe Excel was the right tool for the job. As you might expect, I had high levels of stress until they announced a settlement right before the trial was set to begin. I later realized that I should have done everything elsewhere to confirm that my Excel results were correct.
https://www.forbes.com/sites/salesforce/2014/09/13/sorry-spr...
"Considering the complexity of Excel spreadsheets and the lack of mastery by many users, we have come to the title of this post: more than 80% of the Excel spreadsheets have errors. Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas."
There was some research on Excel's impact on finance with so many errors, but I couldn't find the citation.
Example, put these two formulas in excel:
=(4/3 - 1)*3 - 1
=((4/3 - 1)*3 - 1)
=(4/3 - 1)*3 - 1 = 0
=((4/3 - 1)*3 - 1) = -2.22045E-16
I think the comparable item would be something like VSCode.
In what industry?
Where/how does Excel knowledge factor into this?
Well, we managed a lot of data, mostly (but not only) CSV files. It's very useful to learn a few text manipulation functions in Excel when creating CSV files with sequential and or repetitive content (think TV episodes, etc). Or creating several flavors of CSV for each platform. Yes, a database backend with smart exporting functions might work well, but sometimes fast beats perfect, especially for one-off jobs.
Uploading thousands of videos into YouTube was much quicker with one or two CSV files. By learning some basic Excel, everyone was able to minimize errors and maximize output.
What else could we do with Excel? We could export XML files of our video edits from Premiere/FinalCut Pro, run them through a script into Excel and immediately get a report showing all the editing errors that still needed fixing (we had to edit the videos in a very particular way). This alone saved sooooo much time. Interestingly enough, we were also able to identify individual editors by the mistakes they made (it seems each one had a particular quirk).
I also ran the entire digitizing project in an Excel file, complete with burn charts and velocity calculations.
Over the years, I've received calls from every one of my employees, now on with their lives in other jobs, and one thing they're always grateful for are the Excel lessons.
And once you learn the logic behind building Excel functions and spreadsheets it opens your mind to other uses or more programming skills.
It's much easier to teach someone the power of a few choice Excel functions than to teach them Python from scratch. Plus you can see their eyes light up immediately. Fun times.