Excel never works for math, at any scale. It's a constant trashfire of "looks fine" until it shows a result someone doesn't want, and then an error is found that changes results and the cycle repeats.
Excel is nice for visualizing and browsing data you already have, and informally searching and sorting for hypothesis generation.
My 4th order Runge-Kutta ODE solver I did in an afternoon in Excel the other day, because I didn't want to pirate Matlab to do a homework assignment, begs to differ :). Not to mention, the entire financial industry and many (most?) small businesses.
Lots of people use Excel for various types of math, and it works fine, if you know what you're doing. Excel not being idiot-proof doesn't mean it's not usable.
And that's all without writing a single line of VBA.
(We can have a separate discussion about some of the newer Excel features, like automatically suggesting what kind of statistical analyses to perform on your data - I consider this to be a potential future source of serious fuckups, as it allows people to easily transform data using methods, whose assumptions and implications they do not understand.)
> "looks fine" until it shows a result someone doesn't want, and then an error is found that changes results and the cycle repeats
That's a feature, though. Excel is interactive, which means you get ample opportunity to do sanity checks on results (both partial and final) as you work on your sheet, as well as immediate feedback on corrections. In "properly written" IT systems, this is rarely the case - you end up discovering a problem further down the chain, and have to figure out which component did something wrong, and why.
That IS the dumpster fire. Trust me, the reason I know is that I used to be that guy who thought Excel was a great tool.
I built derivatives spreadsheets, backoffice spreadsheets, trading systems with realtime data, all sorts of crap in Excel.
Really, it's Stockholm Syndrome. People who previously had zero computing power at their disposal think they've found the hammer that solves all problems when they're introduced to Excel, because now they can calculate lots of numbers.
They just get blinded by the revelation that they can now calculate "anything" and are happy to pay whatever the cost is in terms of future maintenance, ease of understanding, etc.
> Excel not being idiot-proof doesn't mean it's not usable.
This is absolutely true, I'd be able to do much better with Excel now than earlier. The problem is Dunning Krueger. There are too many people who think they can code once they're able to get a bit of Excel going, and they don't know that they can't. Not trying to be condescending, I've been there myself. It's just that you get a lot of "coding is a thing I have to do in order to get to some target", and so people think that once they've finally bashed out their spreadsheet, they've figured it all out.
You bring up some very good points. But to those, I have a question: what is the alternative? Both now (for UK healthcare), and in general?
The "people who previously had zero computing power at their disposal" may be wrong in thinking "they've found the hammer that solves all problems" - but there's literally no other hammer available for them. They're not programmers, they won't write their own software (nor would they be allowed to). Any other option involves so much organizational overhead - both initial and ongoing - that it's a non-starter.
I agree that people routinely use Excel way beyond their own skills. But I haven't heard of any viable alternative.
One part is knowing how important correctness actually is in your case. A lot of people on HN are perfectionists, and there's a lot of rhetoric going around our industry about avoiding errors and not breaking things; in reality business processes have always had to be error tolerant (because until recently they were always done by humans) and a quick solution that breaks sometimes is often more valuable than a slow solution that doesn't break.
Another piece is pervasive auditability. Any result should come with an explanation of where it came from; "Bob did some calculations in his head and he reckons the answer is 7" would be acceptable for some kinds of business decisions, while for others it needs to be more like "Bob followed the procedure specified in the XZY institute handbook, page 456". Somehow we've let all that go out the window, partly because people who don't understand computing are managing organisations that deeply depend on it. But you don't even really need computer literacy; what you do need is the same kind of scepticism that you'd apply to any other piece of work.
Managers need to manage. Some of the problem is just people lacking the necessary skills (and a lot of that goes all the way to the top: the UK government doesn't have the wherewithal to hire skilled computer professionals because at every level the people on top don't have the skills to assess whether the people below them are any good), but a lot is a misplaced perception of computers as infallible.
Excel is an excellent hammer. A spreadsheet is a system to develop pure functional graphs and to interact with them in real time. I spent lots of time on finance spreadsheets myself and also thought it was Stockholm syndrome, but when I transitioned to working on software I came to see exactly why spreadsheets are entrenched the way they are, enjoy seeing them used creatively, and would never wish bespoke software on finance people. And good for them, they seem to know better, themselves!
> It's just that you get a lot of "coding is a thing I have to do in order to get to some target", and so people think that once they've finally bashed out their spreadsheet, they've figured it all out.
I have a friend who wrote a 641-line long bash script to automate a web site. It doesn't use subroutines anywhere, the body for the program is a 550 line long loop with multiple if statements and loops inside it.
He thinks the program is maintainable and easy to understand because he didn't have particular difficulty writing it.
He attended the school of self-reliance. He should be applauded for tackling the challenge and doing the job himself, where otherwise it would likely have not been done at all.
Obviously, he's not a programmer. The script, for us, is bad. Perhaps if something important for him depends on this script working, he should pay a software developer to spend some time cleaning it up.
He's entirely self taught. He's not stupid or anything, it's just that he hasn't had the experience of maintaining large codebases, attempting to modify his unmaintainable code or attempting to modify other people's unmaintainable code.
Well why are these people wrong? You had a thing going but you didn’t complete the thought: if Excel works and they can now calculate “anything” without developing software and deploying containers, where is Stockholm Syndrome? Why are they blind? What should they use in finance? Jupyter notebooks?
Having worked in the financial industry, my first job was writing a system to cover for a $10m fuck up in excel where yahoo exchange rates stopped updating and the same spreadsheet was used for a few weeks until someone noticed "Shit, we're losing a lot of money here.".
Excel is the right solution in the same way playdoh is a valid building material.
I know individuals / hobbyists who do this, which is fine. Financial orgs should know better, especially if it is actually "critical." How did this get through any sort of review? How do you know Yahoo is authoritative?
Was it good enough? I would argue that if it enabled your firm to make $12M before being the proximate cause of losing $10M, and the alternative was "we don't win $12M nor lose $10M because we can't afford the project" then I agree it was good enough.
Pretty common for finance in the 00s and 10s to be honest. It's slowly getting better, but until you fire everyone who isn't on board with this and replace them with people who can at least script - at every level of the business - you're going to have the same problem.
That would be good. But to be honest, Excel is scripting - in a 2D FRP REPL (something structurally more advanced than most programmers are using day to day). If we're changing hiring requirements to test for bash/Python/Powershell competency, why not actually test for Excel competency?
Maybe that's the problem? Excel is so easy to start with that people with no experience think they've mastered it, and the industry doesn't seem to have specified any best practices, much less testing the interviewees for their knowledge of them.
I really agree with this. Excel/spreadsheets are great and should be used whenever possible instead of some bespoke solution that will always be worse unless millions and millions of dollars are sunk into it, and even then, will probably be worse, given the state of
software development in 2020.
Like all tools, you have to know how to use Excel. If this is the only error that’s come up, well, so many other problems would have come up with a bespoke solution that Excel is still miles ahead in my book
If you don't already know, Octave is a very good free and open source Matlab compatible interpreter and solution. Was of great use in my time in college.
Back in the day, it wasn't "very good", at least not in terms of getting it to work. That RK4 in Excel happened because I got extremely annoyed at Octave.
Holy shit, how much does it cost to rent out your crystal ball for a few hours? Because you got it 80% right.
I was 19 or 20 back then, and Windows was my main OS - though I did have some experience with Linux as well (running several distributions for desktop use, as well as working with Cygwin and SFU on Windows), and I was a relatively proficient C++ programmer, having spent ~6 years of pretty much all my after-school time coding game engines.
So it's not that I couldn't make it work - I eventually did. But it was so rough around the edges that I gave up in frustration twice.
And yeah, these days, Linux is my daily driver (well, technically Emacs - Linux distros are just various flavors of Emacs bootloaders for me).
That is patently untrue. I've borrowed and lent money based on Excel spreadsheets, more than once. That's small-scale math with real-life consequences, and Excel is an excellent tool for the job.
You're right, but if you asked me to do something in GnuCash which I never used or do the equivalent in Excel with formulas I'm already familiar with, I'd go with Excel in a pinch.
This is why Excel is so well known, because once you get the hang of it, it becomes a great tool for something small.
Excel is great in my industry (slot machine game/math design). It is fantastic for doing game calculations and there are reasonable ways to do error handling/checking for correctness.
I could say the same about programming a web app without writing tests/spending some time on system architecture. It looks fine until it doesn’t work.
In both instances of development (I argue constructing an Excel workbook in my line of work is very similar to programming) there are ways to mitigate risks by doing things similar to “writing tests”.
I wrote a finite capacity planner for a pie factory in Excel v6. It made my Pentium 60 run a bit warm for a while. It took about 18 months - I was the only IT bod in the place. I also learned basic networking and installed a pair of hubs (yes hubs) with a 10BASE2 "backbone" and 10BASET to the desktop. I learned the new Novell 4 stuff, NDS etc and wrote login scripts etc. When I started, Planning were using Lotus 1-2-3 for DOS and some amazing feats of keyboard jockeying. When I finished with them, they cranked the machine and then used their experience to tweak the plan a bit. Run it past the Production meetings, feedback and twiddle a bit and send it out to the Team Leaders, Quality and the rest. If things started to look different, then run the plan again etc. I borrowed my Dad's logistic textbooks to learn about forecasting (v).
Getting from infinite capacity planner to the finite version took quite a while!
I don't think you can fault the tool. VBA in a spreadsheet gives you a lot of power but it needs discipline to wield correctly. I used to have a row at the bottom of all my tables with the word "End" in tiny text in each column, always formatted white on red. All my routines that ran down the table to look up and do something would always look for that signal that the end had been found. Nearly all formulae were entered by VBA. I had auditing routines that would test the various sheets for errors - I suppose I "discovered" unit tests. One of them looked for a row of text with specific formatting ... Another obvious check is having row and column sums cross checking each other. I (re)discovered loads of little things like that.
With care a spreadsheet can be quite handy for all sorts of tasks but please don't equate the ill advised monstrosities you (and I) might have come across in the past with a fault in the tool itself.
Anyway, there is a lot more to this story than that and back then I had a IBM System/36 running the show as well to worry about. Twinax is a right old laugh to deal with. I remember going to Eng and asking to borrow a spanner and a soldering iron - "but you're Planning, what do you need those for".
Sorry, started waffling 8)
(v) How to forecast demand from the multiples in the UK for pasties, sausage rolls etc, back in the day. There are two cycles one is weekly and the other is roughly annual, with peaks and sometimes spikes at Easter and Christmas and some upticks at bank holidays. The weekly one literally looks like a sine wave, the annual one is a bit more involved. As a first go, take the last three orders by day of week for a product and calculate an exponentially smoothed forecast for next week. For example take the last three Mondays to get next Mondays's forecast. Bear in mind that you need to prep, make, bake, chill and wrap the product and ship to depot with about seven to 11 days shelf life and it takes something like one to three days to do that. You are always making to forecast, which is quite tricky. This was about 25 years ago but Asda, Nisa, Lidl etc used to take our forecast and fax/EDI it back as an order without changes.
Excel is nice for visualizing and browsing data you already have, and informally searching and sorting for hypothesis generation.