5 ms·

The amount of issues I have seen in the last ~20 years caused by "off by one" type of errors in Excel is insane. Few examples: - incorrect schedule for the el

The amount of issues I have seen in the last ~20 years caused by "off by one" type of errors in Excel is insane. Few examples: - incorrect schedule for the electricity grid for an entire country - incorrect assessment of an airport use for an airline (causing few millions USD loss in revenue) - incorrect financial position assessment for a mine (resulting incorrect deciosion to optimize the wrong business process, not sure about how much they lost) Making illegal states unrepresentable is a concept that benefits programming langues and business processes alike.

jessekv 2y ago
IMO unit tests are roughly the software equivalent of double-entry bookkeeping.
- jbaber 2y ago
  This is a very good analogy. I will steal it.
pjc50 2y ago
Don't forget Reinhart-Rogoff, which ties into the other thread on academic fraud/nonreproduction, and arguably did huge amounts of economic damage: https://www.bbc.co.uk/news/magazine-22223190 https://www.bbc.co.uk/news/magazine-22223190
- deleted 2y ago
  [deleted]
rco8786 2y ago
Imagine how many errors occurred before we had digital spreadsheet technology. That said, I’ve always thought there was a product that sits somewhere in between Excel and full blown custom software that provides some of the controls we need while still being and build able by someone with low/average technical skills
- netsharc 2y ago
  FileMaker seems to cover this (admittedly my interactions with it was in high school, which was last millennium), it's a database app but there's a lot of "make your own UI" parts in it, so you can create custom UIs (and wizards) for your use cases. I suppose MS Access offers this too, although FM feels more user-friendly, MS Access felt like Internet Explorer 4, where an error dialog would pop-up for every little JavaScript error (disclaimer: this opinion is from 25 years ago).
  - rco8786 2y ago
    No you're right on the money in my opinion. I have very fond memories of spinning up apps in MS Access that were quick and dirty but extremely powerful, and able to be maintained/modified by folks that were not professionally trained software engineers. I think there's a missing area in the market for something exactly like that again.
    - rrr_oh_man 2y ago
      The hive mind really hated (hates?) MS Access and I don't know why. I built a mini ERP for my father's company when I was 16. Started out as an excel spreadsheet, but then added inventory, accounting, and printing contracts and reports. He kept using it until retirement and was very happy with it. I could learn & apply SQL. Win-win.
      - TMWNN 2y ago
        I've done that over the last several years, building an order-tracking/accounting system for myself in Excel. (Inventory and fulfillment is handled by commercial software.) I'm mulling over whether to move to a "real" database. I'm on a Mac so can't move to Access. I've thought about FileMaker, but am considering Panorama X because unlike FileMaker it reportedly allows undoing almost anything, while FileMaker is like the typical database in a record commit not being undoable. (Yes, I know that not allowing such is good practice, which is why I am not a database admin.) I've heard good things about Panorama X, and it has a spreadsheet-like UI. However, I've used Excel enough to know that I haven't tapped more than a small fraction of its ability, especially things like Power Query. As much as I loathe VBA, what if the cost of moving to a "real" database isn't the up-front cost or conversion time, but the longer-term inflexibility of Panorama (and, pretty much, anything else in my price range) compared to the beast that is Excel?
dereg 2y ago
You’re only looking at one side of it. Consider how much value that excel adds, and how many use cases it enables. It’s incalculable. It’s always easy to poke at excel’s issues, but errors are almost always a skill issue. From a financial perspective, there are many ways you could enforce checks to ensure the model is balanced - it just takes time. Data entry can be an issue, but you can automate that too. The deloitte report is saying the health department should benefit from adopting a gigantic erp system but you could get 90% of the benefit by employing a couple people that really know what they’re doing. You could say that excel should allow those things to happen but the flexibility is precisely what makes it so valuable. The people writing the reports are consultants. Consultants recommend things that benefit consultants. In their case, a multi year process and tens of millions of dollars trying to install ERP software is more a windfall for the vendors and not the companies.
- 7thaccount 2y ago
  Yep. I was going to say the same thing. Installing some $10M+ software costs the company more than just the install and ongoing licensing costs. You have to have an entire new IT team to manage it and staff to understand and so on. There are probably just as many chances of having a big issue.
- potato3732842 2y ago
  Say nothing of many crisis were avoided because somebody was able to pull up a local excel file despite a central service being down or go through their historical files and sniff out a discrepancy. Workflows like that are often impossible or require coordinating with a vendor in another time zone or a bunch of slow cross team communication and ticket submissions with the sort of minimum effort "we'll run a DB in the cloud and slap a JS front end built with a bunch of questionable but flashy libraries and ignore all the edge cases because I'll be working somewhere else in 2yr" solutions that the median software developer hive mind will implement if left to its own devices.
- ericd 2y ago
  Absolutely, kudos to them for following KISS, despite the legions of people who have probably told them they’re doing it wrong.
whiplash451 2y ago
Do you have evidence that _any_ other software would not be prone to this kind of errors, taking into account the fact that at the scale of dozens of billions of dollars, the numbers you mentioned seem like rounding errors (except for the first one which would deserve a reference)?
Suppafly 2y ago
>The amount of issues I have seen in the last ~20 years caused by "off by one" type of errors in Excel is insane. Generally those aren't a fault of excel, but a fault of someone doing something dumb in excel. People do dumb stuff in every problem and it's nearly impossible to prevent it.

jessekv 2y ago

IMO unit tests are roughly the software equivalent of double-entry bookkeeping.

jbaber 2y ago
This is a very good analogy. I will steal it.

pjc50 2y ago

Don't forget Reinhart-Rogoff, which ties into the other thread on academic fraud/nonreproduction, and arguably did huge amounts of economic damage: https://www.bbc.co.uk/news/magazine-22223190 https://www.bbc.co.uk/news/magazine-22223190

deleted 2y ago
[deleted]

rco8786 2y ago

Imagine how many errors occurred before we had digital spreadsheet technology. That said, I’ve always thought there was a product that sits somewhere in between Excel and full blown custom software that provides some of the controls we need while still being and build able by someone with low/average technical skills

netsharc 2y ago
FileMaker seems to cover this (admittedly my interactions with it was in high school, which was last millennium), it's a database app but there's a lot of "make your own UI" parts in it, so you can create custom UIs (and wizards) for your use cases. I suppose MS Access offers this too, although FM feels more user-friendly, MS Access felt like Internet Explorer 4, where an error dialog would pop-up for every little JavaScript error (disclaimer: this opinion is from 25 years ago).
- rco8786 2y ago
  No you're right on the money in my opinion. I have very fond memories of spinning up apps in MS Access that were quick and dirty but extremely powerful, and able to be maintained/modified by folks that were not professionally trained software engineers. I think there's a missing area in the market for something exactly like that again.
  - rrr_oh_man 2y ago
    The hive mind really hated (hates?) MS Access and I don't know why. I built a mini ERP for my father's company when I was 16. Started out as an excel spreadsheet, but then added inventory, accounting, and printing contracts and reports. He kept using it until retirement and was very happy with it. I could learn & apply SQL. Win-win.
    - TMWNN 2y ago
      I've done that over the last several years, building an order-tracking/accounting system for myself in Excel. (Inventory and fulfillment is handled by commercial software.) I'm mulling over whether to move to a "real" database. I'm on a Mac so can't move to Access. I've thought about FileMaker, but am considering Panorama X because unlike FileMaker it reportedly allows undoing almost anything, while FileMaker is like the typical database in a record commit not being undoable. (Yes, I know that not allowing such is good practice, which is why I am not a database admin.) I've heard good things about Panorama X, and it has a spreadsheet-like UI. However, I've used Excel enough to know that I haven't tapped more than a small fraction of its ability, especially things like Power Query. As much as I loathe VBA, what if the cost of moving to a "real" database isn't the up-front cost or conversion time, but the longer-term inflexibility of Panorama (and, pretty much, anything else in my price range) compared to the beast that is Excel?

dereg 2y ago

You’re only looking at one side of it. Consider how much value that excel adds, and how many use cases it enables. It’s incalculable. It’s always easy to poke at excel’s issues, but errors are almost always a skill issue. From a financial perspective, there are many ways you could enforce checks to ensure the model is balanced - it just takes time. Data entry can be an issue, but you can automate that too. The deloitte report is saying the health department should benefit from adopting a gigantic erp system but you could get 90% of the benefit by employing a couple people that really know what they’re doing. You could say that excel should allow those things to happen but the flexibility is precisely what makes it so valuable. The people writing the reports are consultants. Consultants recommend things that benefit consultants. In their case, a multi year process and tens of millions of dollars trying to install ERP software is more a windfall for the vendors and not the companies.

7thaccount 2y ago
Yep. I was going to say the same thing. Installing some $10M+ software costs the company more than just the install and ongoing licensing costs. You have to have an entire new IT team to manage it and staff to understand and so on. There are probably just as many chances of having a big issue.
potato3732842 2y ago
Say nothing of many crisis were avoided because somebody was able to pull up a local excel file despite a central service being down or go through their historical files and sniff out a discrepancy. Workflows like that are often impossible or require coordinating with a vendor in another time zone or a bunch of slow cross team communication and ticket submissions with the sort of minimum effort "we'll run a DB in the cloud and slap a JS front end built with a bunch of questionable but flashy libraries and ignore all the edge cases because I'll be working somewhere else in 2yr" solutions that the median software developer hive mind will implement if left to its own devices.
ericd 2y ago
Absolutely, kudos to them for following KISS, despite the legions of people who have probably told them they’re doing it wrong.

whiplash451 2y ago

Do you have evidence that _any_ other software would not be prone to this kind of errors, taking into account the fact that at the scale of dozens of billions of dollars, the numbers you mentioned seem like rounding errors (except for the first one which would deserve a reference)?

Suppafly 2y ago

>The amount of issues I have seen in the last ~20 years caused by "off by one" type of errors in Excel is insane. Generally those aren't a fault of excel, but a fault of someone doing something dumb in excel. People do dumb stuff in every problem and it's nearly impossible to prevent it.