back to article Excel's comedy of errors needs a new script, not new scripting

There is a perennial fad for finding deep truths in religion. Really only one concept stands up: without reincarnation, how can one explain the constant reappearance of old ideas as new in IT? This last week has seen a couple of turns of tech's cosmic wheel. LG's 2015 V series Second Screen idea woke up in the body of the …

  1. Donk

    From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

    "The European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors."

    ESRIG: http://www.eusprig.org/

    In the same chapter, he also talks about the same Excel issue that you just mentioned and how biologists have had to rename enzymes because Excel "autocorrects" them

    TL;Dr Another quote from "Humble Pi: A Comedy of Maths Errors" by Matt Parker -

    "Tell them to use a real database LIKE AN ADULT."

    1. fwthinks
      FAIL

      Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

      I have been waiting patiently for years for the option to disable auto conversion on imported times and dates. I would not have an issue if this was customizable, but it cannot be blocked - only worked around. Have Microsoft never heard of multi-national companies using different locale settings?

      Can't they focus on fixing major frustrations with the product rather than adding new features?

      1. J.G.Harston Silver badge

        Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

        Similarly leading zeros: Folio 0020

        Stings of digits with an 'E' in them: product code 12E3

        Long strings of digits: Source ID: 12345678901234

        Slashes with digits: house number: 12/13

        Things that look like dates. serialnum: 01DEC

        The work-around around work-around around work-arounds I've had to go to. It's. A. String. Ooooo, it looks like a number. NO. IT'S. A. STRING!

        1. Missing Semicolon Silver badge
          Facepalm

          Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

          Phone numbers! Excel goes "but it starts with a +, it must be a number, whatever you say".

          1. sabroni Silver badge
            Boffin

            Re: Phone numbers!

            The clue is in the name.

      2. coredump

        Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

        > Can't they focus on fixing major frustrations with the product rather than adding new features?

        How many other projects have we said the exact the same thing about? Most of them aren't spreadsheets.

        1. Michael Wojcik Silver badge

          Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

          The problem is that the people who pay for most software aren't end users. The ones writing the checks tell vendors "we'd buy another X licenses if only it could do this one other thing". Because of the cost of switching to a different package, user frustration usually doesn't have much effect on renewal rates. So the economics favor new features over fixing problems.

          I don't see how market forces are likely to shift enough to solve that, or how to apply regulation to change it, unfortunately. Seems to be a hard problem to solve. With security issues at least there's now some regulatory pressure and it's gradually increasing, but usability (aside from accessibility, which does have regulatory pressure) and even correct functionality are harder to control.

    2. TeeCee Gold badge
      Coat

      Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

      "...estimates that over 90 per cent of all spreadsheets contain errors."

      Let me guess. They have a spreadsheet showing that data, don't they?

      1. Strahd Ivarius Silver badge

        Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

        running on a Pentium, no less...

    3. TheSirFin

      Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

      Great book. Another great read, which is full of such reference is "Science Fictions" by Stuart Richie. (Royal Society Book of Year nominee. Check out the of Austerity study by Reinhart and Rogoff. Global policy to implement Austerity (especially Tory UK) was based on a spreadsheet that an error in it. If you fix the error (as they did in 2013 ... it shows that Austerity should not have been the chosen route. horrific .....

      1. Twanky

        Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

        Many thanks for the book recommendations.

        it shows that Austerity should not have been the chosen route. horrific

        Rather than the UK, I'd point to Greece to highlight the pain of Austerity as a policy. For Greece it was eventually imposed by the lenders (IMF and EU) effectively saying 'no more credit'.

        Borrowing is not inherently wrong - ask boomers who now own their own homes how they got there and most will say they took on debt - and some fell foul of 'negative equity' and had to start over.

        Reinhart and Rogoff's error in their spreadsheet weakened but did not destroy their central finding which was that high international debt is associated with low growth. They should not have been storing their data in a spreadsheet. It's the wrong tool for the job.

        It seems entirely reasonable that paying interest on or paying off debt leaves less value to be spent on all the good things a country needs to progress - like medical care, education, infrastructure (and sadly, defence). Every now and then there is a push to forgive some international debt - which is a good thing when it's got out of control. It's a bit like a personal bankrupt these days may have their finances managed for them by an externally appointed administrator - but the law takes a very dim view if they bypass their administrator and take on even more debt.

        Lastly, Thomas Herndon (the student who discovered the error) deserves high praise.

    4. oikos

      Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

      What's that joke about excel being like an incel because it incorrectly assumes everything is a date?

    5. jmch Silver badge

      Re: From "Humble Pi: A Comedy of Maths Errors" by Matt Parker

      "Tell them to use a real database LIKE AN ADULT."

      My career as a data professional tells me exactly this. And yet putting on an alternative hat as a user I really, REALLY understand why they do it. Users have no (or very limited) access to, or training in how to use, real databases. And in every organisation I've been, which is a lot, it's the same story - IT bods and database professional do not have the available capacity to train users, or it's considered out of their remit and seen as a waste of time by their bosses in IT and the users' bosses in business.

      So the users have stuff they need doing, limited help from organisations that increasingly pigeonhole roles, and bosses breathing down their neck to get things done yesterday. And so they turn to the only tool that is available to them which can deliver, which is Excel.

      Of course complex spreadsheets have errors, for the same reasons that code has bugs. It's less to do with the competence of the user creating the spreadsheet or the developer creating the code (although a fair chunk of errors is due to cluelessness), and more to do with the pressure to deliver, which means that testing, verification, error-checking etc are last priorities.

  2. Pascal Monett Silver badge
    Trollface

    Brilliant article

    Too bad no managers will read it.

    1. Loyal Commenter Silver badge

      Re: Brilliant article

      Paste it into a spreadsheet and they will

      1. Dave@Home

        Re: Brilliant article

        Nah, they'll maybe open the spreadsheet, but actually read the thing and parse what it's telling them?

      2. Twanky
        Flame

        Re: Brilliant article

        Nah. Too many managers I knew would say 'Explain what this is telling me'.

        There's a huge difference between managers and leaders.

        1. coredump

          Re: Brilliant article

          Some managers I know would say "where's the slide deck for this?"

    2. MyffyW Silver badge

      Re: Brilliant article

      One of my peers saw me reading El Reg the other day and sneered "You don't still read that do you?" as if a couple of rungs up the corporate hierarchy should cure me of this bad habit. Well yes, I still do, and guess what? That's why I'm better informed than said colleague :-)

  3. Primus Secundus Tertius

    Clueless users

    I have used spreadsheets for membership lists of various voluntary groups, inheriting these lists from previous society officers. How little so many people understand how to set up data systematically! British postal addresses are particularly variable and therefore difficult.

    There are people who know in detail how to use word processors even though they are not computer specialists. We need the same for spreadsheets.

    1. J.G.Harston Silver badge

      Re: Clueless users

      And glod protect me from clueless lusers who enter telephone numbers as an unformatted string of random digits.

      Phone: 1.1428E8

      1. Michael Wojcik Silver badge

        Re: Clueless users

        Hey, that's my phone number (to five significant figures)!

        Of course, users should be able to enter phone numbers as strings of digits with no punctuation or whitespace. Just as they should be able to enter them with those things, and should be able to enter credit card numbers with or without spaces or punctuation, and so on. Humans shouldn't be forced to adapt themselves to trivial limitations in software.

        1. J.G.Harston Silver badge

          Re: Clueless users

          I managed to work out an Excel custom format to get phone numbers to display as phone numbers. Then couldn't work out how to copy it to a work spreadsheet. So started again from scratch - only to find that work's Excel is crippled to remove "custom data format".

        2. Twanky

          Re: Clueless users

          I really don't think anyone should be entering or storing credit card numbers in a spreadsheet.

    2. Phil O'Sophical Silver badge

      Re: Clueless users

      British postal addresses are particularly variable and therefore difficult.

      Postcode and house number is all you need, the rest is just redundant window dressing. Often better to ignore it anyway, people who live just across the parish or county boundary from a more desirable address often use the "wrong" town/county name because it sounds more upmarket.

      1. Little Mouse
        Headmaster

        Re: Clueless users

        As long as you also allow for house names instead of numbers. And numbers with a, b, c, etc, after them. And flats / sublets that can have compounded numbers. And all the other edge cases I've probably missed.

        But yes, your point that an address has a unique identifier + postcode still stands. (But no doubt someone will now point out an example where it doesn't.)

        1. Arthur the cat Silver badge

          Re: Clueless users

          My house is two buildings knocked into one and has two numbers, usually written as <N>–<N+2>. Most address handling systems I deal with can only handle a single number without punctuation. The majority lock onto the lower number, my bank uses the higher. The TV licence lot accepted the dual number form for their main database, but their licence checker only used one, so for years I'd get an automatically renewed licence and threatening letters telling me I hadn't got a licence and they were sending someone round. (They never did.)

          1. Strahd Ivarius Silver badge
            Joke

            Re: Clueless users

            every year some bloke is sent to your location, never be seen again...

            1. Michael Wojcik Silver badge

              Re: Clueless users

              After failing to mind the gap between the two numbers.

          2. keithpeter Silver badge
            Windows

            Re: Clueless users

            "(They never did.)"

            They never do.

            I have never (5 decades) owned a television set. I have only recently (last decade) paid a licence fee because I discovered that, without any parliamentary debate whatsoever, there had been an order in council to regard a 'broadband connected computer' as television receiving apparatus.

            Icon: waves palsied fist at heavens

            1. AJ MacLeod

              Re: Clueless users

              You should get a refund then... you don't need a TV licence just because you own a broadband connected computer. Nor in fact do you need one even if you own an actual TV - it's only watching broadcast (or streamed BBC) TV that requires a licence.

            2. Already?

              Re: Clueless users

              Like he says, get a a refund, and stop paying.

              It’s been years since a licence was needing simply for owning receiving apparatus. You need a licence in the UK to watch or record any tv channel either live or as live, regardless of where it comes from, or to watch anything on the BBC iPlayer.

              You can watch all other streaming services content without a licence, unless you’re watching a live or as live broadcast - e.g. tennis or Premier League football on Amazon.

              Live or as live allows for the inherent delays in broadcasting, including the short delays added to allow for cutting away if something untoward occurs.

        2. Anonymous Coward
          Anonymous Coward

          Re: Clueless users

          "an address has a unique identifier + postcode still stands. (But no doubt someone will now point out an example where it doesn't.)"

          Assuming you have the correct and/or up-to-date postcode.

          Many companies use the same postcode from the day the premises was just 4 sticks and a bit of string in a muddy field. I used to work in a building where they had sold a parcel of land that meant that the entrance was now on a different street. One day we received a letter from Royal Mail with a new postcode on the other street and everybody immediately thought 'no more "but the entrance is on Other St", that is except for the company's building management team, who insisted on using the original address ('it's on the blueprints').... until the day I pointed out that their fire instructions gave a non existent postcode for locked unmarked gate at the end of a narrow unmarked alleyway.

          I've also had a customer give an address and postcode that was perfectly valid, except it was for the same address in a town 5 miles away... they still insisted it was correct when the parcel didn't turn up (it eventually returned as 'unwanted' by a no doubt peed-off 'recipient')

          1. J.G.Harston Silver badge

            Re: Clueless users

            Gawd! And no end of doing field service work and finding the job sheet has the address of the post room, not the work site several miles away.

            I think the worst I had was turning up at Mansfield Hospital and the equipment was waiting for me in Nottingham.

      2. Trigonoceps occipitalis

        Re: Clueless users

        "Postcode and house number is all you need, the rest is just redundant window dressing."

        That was true right up to the time, several years ago, when a new development used the same numbers as many houses in my Post Code area. It has caused untold problems because most believe this wrong information and use the default address offered by the address finder. It is a real problem and landed me with a £700 unauthorized overdraft (thank you waterboard) when the tenants changed in a house down the road.

        I consulted the Post Office and the idea that solely a UK Post Code and house number uniquely identify an address has been wrong for a long time.

      3. Stephen Wilkinson

        Re: Clueless users

        You just need your Unique Property Reference Number (UPRN).

        I don't know if the Post Office would get the letter to you though.

      4. GrapeBunch

        Re: Clueless users

        Shirley you mean "wwong".

    3. J.G.Harston Silver badge

      Re: Clueless users

      British postal addresses are particularly variable and therefore difficult.

      Do you mean postcodes? They're not. One or two letters, number 0-99, possible additional letter, space, digit-letter-letter. And the outward code is human-readable and so has a built-in error trigger.

      If you mean the whole address - well, that's the same the whole world over. The Caravan, The Farm Annex, Tom's Farm, Warren Farm Lane, Flyde Moor, Heskthorpe, XY0 0XZ

      1. Strahd Ivarius Silver badge
        Devil

        Re: Clueless users

        a regex is needed there!

    4. sgj100

      Re: Clueless users

      The really dangerous users are not those who are clueless as they don't know enough to be dangerous. The really dangerous users are those who know a little (or think they do). Every organisation for which I have managed IT has had users of this type, often in the finance and marketing departments. If I'd had sufficient IT resources to provide database querying/report writing "on demand" to end users I would have banned the use of Excel. However, I was never in this position because the "I can do it in Excel" attitude prevailed.

      1. veti Silver badge

        Re: Clueless users

        Any alternative that involves making users more dependent on the IT department is going to be a very hard sell.

        It's not enough to just store, query, analyse and share data. Users need to be able to do all this ad hoc, experimenting with dozens or hundreds of permutations of queries, parameters, operations and presentations, before they hit on some formula that they think makes sense.

        And they "need" to be able to do all this on their own, without discussing it with another living soul. There will be failures. Many. There will be all-nighters. Reputations are made and lost that way.

        Short of telling them all to learn SQL (and just imagine how much safer that would be, even if you could talk them into it), what else do you suggest?

        1. Joe W Silver badge

          Re: Clueless users

          Yeah, I suggest they learn SQL. Which is not hard, dammit! It is not harder than the crazy Excel stuff people do. It is quicker. It is less error prone.

          My pet peeve: Excel translates the macro names, though I think not all of them. So instead of "=AVG( A1:A3)" you need to write "MITTELWERT(A1:A3)" or whatever the translation is, it should be "ARITHMETISCHESMITTEL", IMNSHO, because that would actually distinguish between that and the geometric mean (for example). Plus, the English macro names are in many cases abbreviations and thus shorter and similar to the names used in e.g. SQL, SSRS, or real programming languages (like, say, Fortran).

          1. jmch Silver badge
            Facepalm

            Re: Clueless users

            "My pet peeve: Excel translates the macro names,"

            Yes it does, and it's a giant pain, especially with the recent trend of IT departments not giving enough rights to users (including tech-savvy IT ones) to change the install language of Office.

            Hence one of my permanent bookmarks in one of my recent contracts being a list of Excel functions and what they are called in French

    5. Allan George Dyer

      Re: Clueless users

      @Primus Secundus Tertius - "British postal addresses are particularly variable"

      FTFY.

      Save me from forms insisting I enter a City (Hong Kong), State (Hong Kong), Country (Hong Kong) and Postcode (HK).

      1. J.G.Harston Silver badge

        Re: Clueless users

        Hey, I had a postcode in Hong Kong: RKT/12/C/601

        Are they not used any more?

        1. Allan George Dyer

          Re: Clueless users

          Didn't see anything like that in an address in HK in 29 years. It does remind me of references on some government letters. Was it a BFPO number?

          1. J.G.Harston Silver badge

            Re: Clueless users

            No. The closest I've worked out the structure is that I lived in Lei Yue Mun, which is in Kwun Tong, so I assumed the letters were R-for-something Kwun Tong.

  4. gryphon

    I remember the accountants at a previous firm back in Excel 97 using very large spreadsheets filling up pretty much every cell plus macros and so on.

    I think the line then was that you could assume 1 error for every 20 lines in Excel so they probably had hundreds of errors in something that was basically driving the company. <Shiver>

    Plus they kept breaking it because it was so large, average spread sheet back in the day was a couple of hundred Kb if memory serves, these were Mb's.

    Thankfully they didn't understand Access or that would have been the next thing they would have pounced on.

    On the other hand if you want to actually understand Excel and use it to it's fullest extent it's always best to talk to an accountant.

  5. disgruntled yank

    No kidding

    Back in the minicomputer days, someone wrote (approximately) that a spreadsheet is a program written by someone with no experience in analysis, coding, or debugging, and with an axe to grind.

    This spring, in Risks Digest 33.14, John Levine wrote about a time in the 1980s, when he worked on a time-series modeling package name Javelin, saying among other things that

    'We converted a lot of 1-2-3 spreadsheets to Javelin models for prospective and current customers, and found that to a first approximation, any spreadsheet large enough to be interesting had mistakes. We also found that people Did Not Care. A particularly telling comment was "it's my manager's job to find the errors in my speadsheets."'

    1. ChoHag Silver badge

      "it's my manager's job to find the errors in my speadsheets."

      So *that's* what they're for?

    2. Cederic Silver badge

      Re: No kidding

      I used Javelin. It had some very nice features but constrained functionality.

      I found it most useful as a communications tool for data projections. You could document the data so that both were visible at the same time.

      Problem was that it didn't really do anything you couldn't do in Lotus123, which could also do so much more. Truth was that anybody able to learn Javelin and really benefit from it could also handle creating time series support in a spreadsheet - not least because if your job needed Javelin, you were almost certainly going to be using a spreadsheet too.

  6. navarac Silver badge

    Security - not

    Microsoft has never had any idea what-so-ever about security, and the fact that some (a lot?) of people using their products have little, or no, better idea either. Instead of bleating about rounded corners, and other niff naff and trivia that tries to emulate Apple and mobile devices, they'd do far better to secure and consolidate their products, and get some consistency in them. Enough of the eye candy syndrome, please.

  7. Anonymous Coward
    Anonymous Coward

    Who is to blame?

    Like many of us, I have seen appalling things done with spreadsheets. Spreadsheets which have become "mission critical".

    Part of the problem is getting corporate IT to provide a proper solution. The solution may not be that complex, but when someone is faced with an IT department that demands a business case and a committee approval and a PM and an architect and more committees and .... Well, it's no surprise they just give up and resort to Excel. Even when errors (or regulation) could make this an existential threat to the company.

    1. Loyal Commenter Silver badge

      Re: Who is to blame?

      The alternative to demanding a business case and PM and architect, and having committees is the other nightmare situation - "just make it work" with all the unwritten specifications and feature creep, you end up with a mutually inconsistent mess which is never finished.

      We wouldn't need PMs and architects et al if the customer could write down, clearly and unambiguously, their complete requirements with a guarantee that they cover everything, in detail, and won't grow half way through the project.

      Which brings me to the role you missed off - Business Analyst. Or do you want to do all the requirements gathering, analysis, technical spec writing, cost estimation and so on yourself, before presenting the developers with a complete and full spec? Because we'd really like that, rather than it being assumed we'll do it for you, and that we can read your mind...

      1. Anonymous Coward
        Anonymous Coward

        Re: Who is to blame?

        Well, I didn't mention BAs because ... my experience with them hasn't always been great. This was a long time ago, but my best experience was with expert users working directly with devs with oversight from a lead tech/architect.

        My point being that "enterprise IT" by trying to achieve hyper-control over everything encourages shadow IT. It makes it much more likely that your accounts department is dependent upon "Dave's Spreadsheet" (Dave having left 4 years ago).

        If you want to do everything with process-heavy waterfall, then fine. Just be aware that it almost never works.

        1. cschneid

          Re: Who is to blame?

          "If you want to do everything with process-heavy waterfall, then fine. Just be aware that it almost never works."

          Neither does shadow IT, that's kind of the point.

          1. Arthur the cat Silver badge

            Re: Who is to blame?

            The problem is that in an environment where everything is wanted yesterday, shadow IT delivers something much faster than enterprise IT. It may be a pile of festering dung that's going to kill the company in the long run, but all managers wanted an answer yesterday and shadow IT gave it to them only 24 hours late so they aren't going to kill it.

            1. Anonymous Coward
              Anonymous Coward

              Re: Who is to blame?

              The problem is when enterprise IT takes a minimum of 3 months and tens of thousands of pounds to deliver even the smallest change.

              I remember at one place chatting to a DBA about why a particular DB was slow. He was well aware of the problem and which alterations to indexes would've fixed it in minutes, but couldn't bear the idea of going through the obligatory process.

              This is too familiar

            2. Anonymous Coward
              Anonymous Coward

              Re: Who is to blame?

              Yes, so as usual, unreasonable / impossible management demands / expectations are the root of the problem.

              The failure of so-called Shadow IT is it tries to deliver on it, rather than rightfully pointing out that any answer given on management's crazy deadline would be bollocks from the start.

              Maybe so-called Enterprise IT goes too far in the other direction, with schedules and requirements and so on.

              But total appeasement every time is not a good path. Sometimes "no", or at least "not right now", is the only responsible answer.

        2. Fred Daggy Silver badge

          Re: Who is to blame?

          Trust me, it isn't the IT that wants this. It the Business Manglement that wants this.

          Simply, cost. If a project can be stalled by the beur ... beuar ... business civil service, then that has saved the company some money in avoided expense. 2 logical fallacies for the price of one.

          It does take time and money to do something right. It takes a lot more time and money to document "what is right" so that when the next Excel guru throws in the towel, the next one can stumble on a bit. And there's the rub of it.

      2. Doctor Syntax Silver badge

        Re: Who is to blame?

        Back in the day we were analyst/programmers. That meant that we actually talked to users, possibly at their desks to observe processes,* to find out what they did and coded up a solution. Of course that sort of thing was liable to make several layers of manglement unhappy because it didn't need their high-ceremony processes. As a result it seems to have got stamped on rather heavily.

        *Remote working wouldn't have made that easy.

        1. Arthur the cat Silver badge

          Re: Who is to blame?

          Of course that sort of thing was liable to make several layers of manglement unhappy because it didn't need their high-ceremony processes.

          It didn't help that the position was often abbreviated to anal/prog.

        2. Loyal Commenter Silver badge

          Re: Who is to blame?

          These days, we still end up doing the analysis part, it's just we don't get the time, budget, training, or pay to do it.

        3. Anonymous Coward
          Anonymous Coward

          Re: Who is to blame?

          Back in the day we were analyst/programmers. That meant that we actually talked to users, possibly at their desks to observe processes

          And we got shit done.

          Of course that sort of thing was liable to make several layers of manglement unhappy because it didn't need their high-ceremony processes.

          Nothing makes management more unhappy than people getting stuff done without their input. They would always rather not do anything and be in control.

          A smallish company I worked at many years ago, IT management decided to tighten up on procedures and documentation. Demand everything go through all these processes. The result was, the users set up their own IT department to develop stuff they needed. I saw some of the code they produced and it was horrible but apparently it did the job.

          1. Anonymous Coward
            Anonymous Coward

            Re: Who is to blame?

            > saw some of the code they produced and it was horrible but apparently it did the job.

            "Apparently" is the operative word.

            It's a fair bet they didn't really know if the code actually did the job either.

      3. veti Silver badge

        Re: Who is to blame?

        Which is exactly why they need Excel. Nobody in the whole history of business computing has ever understood a non-trivial problem well enough up front to write a spec for it. (Like, literally nobody. Not once. Anywhere, ever.) It takes experimentation, trial and error - often lots of it - to hit on something useful.

        By imposing some half-arsed waterfall development methodology on them, you are erecting an insuperable barrier to at least 95% of all people who might otherwise be persuaded to take that route. (5% may be willing to attempt it, but they'll probably fail. The exceptions will be people who've already done the analysis themselves. Using Excel.)

    2. Mike 137 Silver badge

      Re: Who is to blame?

      "Part of the problem is getting corporate IT to provide a proper solution"

      Sometimes a very big part. Many times I've consulted with organisations that kept their sensitive infosec documentation on group-organised sharepoint, which meant there was no individual level control over who could get access to what. In every case but one, when I proposed segregated secure storage with individual access permissions this was either rejected out of hand or the "reviews process" continued until the proposal stalled and died. And in that one case the implemented storage was only used as a source from which to make floating copies of documents that finished up on sharepoint. I often wonder whether there might have been a more rewarding career than infosec managemnt.

      1. Anonymous Coward
        Anonymous Coward

        Re: Who is to blame?

        > kept their sensitive infosec documentation on group-organised sharepoint

        Maybe that was part of the problem right there.

  8. Pirate Dave Silver badge

    "Make spreadsheets better"

    The problem with that is Microsoft would add a raft of "new ideas" to Excel that would do little to make it "better", but would slow it to a bloody crawl. Like that damned yellow word-suggestion thing that showed up n Outlook in the past year that absolutely slows things to a crawl while it's trying to figure out which three-letter words to suggest. Even though I've supposedly turned it off, it still rumbles back to life at random moments, like a drunk at a frat party. Imagine something like that running constantly in Excel. Yeah, no thanks. I'd rather go back to Excel 2003.

    The root of the problem is that there aren't many Good Ideas left at Microsoft these days, but there is an apparently never-ending supply of stupid, pointless, and/or wasteful ideas.

    1. Arthur the cat Silver badge
      Unhappy

      Re: "Make spreadsheets better"

      The root of the problem is that there aren't many Good Ideas left at Microsoft these days, but there is an apparently never-ending supply of stupid, pointless, and/or wasteful ideas.

      s/Microsoft/most big companies, governments, etc/

      1. Pirate Dave Silver badge

        Re: "Make spreadsheets better"

        Yeah, don't even get me started. It seems companies (and gummit) don't even pretend to give a shit about individual customers anymore, they only worry about the aggregate now. Amazon being the most recent to smash the "Piss-off Pirate Dave" button to bits.

      2. Anonymous Coward
        Anonymous Coward

        Re: "Make spreadsheets better"

        Gawd! I'm in the middle of yet another NHS re-arrangement at the moment. We have weekly telecasts we are supposed to watch to keep us up to date on the process.

        Anon for obsv.

    2. A.P. Veening Silver badge

      Re: "Make spreadsheets better"

      while it's trying to figure out which four-letter words to suggest.

      FTFY ;)

  9. a pressbutton
    Coat

    Excel is already the single most dangerous tool to give to civilians.

    Hm.

    I put it to all that in the UK, a car is rather more dangerous.

    In the US, semiautomatic weapons

    In France, the literary fiction.

    1. Cederic Silver badge

      Re: Excel is already the single most dangerous tool to give to civilians.

      Ah, such optimism.

      With a car I can be a menace to a smal group. With a semi automatic firearm I can really ruin someone's day.

      With Excel? I can destroy industries, entire economies.

      1. a pressbutton

        Re: Excel is already the single most dangerous tool to give to civilians.

        No you cant. google "company destroyed by excel". provide the top 10 names pls.

        Excel cocks up

        and user accepted the results without thinking and peer reviewer accepted the results without thinking and managers unquestioningly accepted the results without thinking and the chief accountant accepted the results without thinking.

        Now, you may be able to seriously damage a financial firm but I do not think the trading algorithms are in VBA.

        Some may point to the VAR projections used by LTCM but again dont think they were in VBA and anyway, where was the human judgement.

        As noted above "The managers are supposed to check..."

        Having said that, plenty of lives ruined by mailmerge as noted below...

        1. Anonymous Coward
          Anonymous Coward

          Re: Excel is already the single most dangerous tool to give to civilians.

          some trading algorithms are in VBA, running on overpowered workstations to be able to work at the expected speed...

          1. I'm Brian and so's my wife

            Re: Excel is already the single most dangerous tool to give to civilians.

            And those workstations are thundering their way through the overnight processing every night under the traders' desks - no testing, no source control, no backup...

    2. Pirate Dave Silver badge

      Re: Excel is already the single most dangerous tool to give to civilians.

      "In the US, semiautomatic weapons"

      Nah, the Big Mac is still our most deadly weapon of mass destruction. In so many ways.

      1. ComputerSays_noAbsolutelyNo Silver badge
        Coat

        Re: Excel is already the single most dangerous tool to give to civilians.

        ... but doesn't the Bic Mac cause the mass to increase?

        -> I'll see myself out

    3. Anonymous Coward
      Anonymous Coward

      Re: Excel is already the single most dangerous tool to give to civilians.

      Excel can be equally dangerous when given to the military!

      https://www.theregister.com/2020/08/07/army_promotion_excel_snafu/

  10. tojb
    Big Brother

    Ever done bird?

    Lot of people doing stir because of spreadsheet errors https://slate.com/business/2009/10/were-hundreds-of-criminals-given-the-wrong-sentences-because-lawyers-messed-up-a-basic-work-sheet.html . Nowdays they use an interactive document, with more text and fewer dynamic cells visible to the user, but still. Being a judge doesn't require smarts in particular.

  11. a pressbutton

    More seriously

    Business processes are continually evolving.

    One of the first mistakes IT people make is that they (I) expect people to come to them with a nicely documented petition pleading for something to be different and this is considered once a year and if they feel merciful, it might appear a year after that implemented in a way that solves the documented issue but not what they (I) wanted/needed.

    That "beautiful" planned, tested, trained, maintained ERP-DB system may be 100% as far as the tests the group IT have in place, but in many cases the product is less useful than the hokey old macro enabled sheet that intern did last summer.

    Yes, spreadsheets do have lots of errors, but if you compared the utility of a spreadsheet and all its issues with the utility of an enterprise solution and all its issues, well it is pretty obvs that if the spreadsheet was not about as bad or slightly less bad, it would not be used.

    After a few years there is a string vest (I would have said 'web' or network' but...) of linked hokey solutions that sort of work enough of the time (there is a survivorship bias here - if it failed, the sheets would be gone or the company or both) to keep the business going.

    The core issue is in the gap between "Proper solution" and what a business wants. Wants change. People do not know what they want. It is usually what they are offered but with a number of 'alterations'. Once they get those alterations, there are more alterations wanted.

    This is dynamic and iterative.

    If IT cannot provide the service, and there is a low up-front cost (a helpful young thing who learned excel in college) the business will do it themselves with the tools they have. Most businesses work off numbers. Excel happens to be the dominant tool.

    Thus the article.

    I put it to you that the issue is not primarily Excel.

    It is the IT team's ability to provide the service quickly and at about the same (up front) price as the business.

    There are a number of solutions

    - take Excel away (you will taken away first)

    - teach everyone to program (but there are programmers and ... programmers)

    - do something else

    Like google sheets where the macro language is a bit trickier and so discourage fledgling foot gunners

    Or a *really good* db / reporting tool

    1. Loyal Commenter Silver badge

      That "do something else" starts with gathering your business requirements and writing them down. If they're all in your head, and you're relying on mashing together a spreadsheet to turn your thoughts into business reality, you are pretty much doomed to fail.

      Although many businesses do seem to run on bodges and just-in-time hacks, many businesses also fail when they come up against the challenge of things like regulatory change, or the need to grow. A big part of this comes down to not being properly organised. Excel may or may not be a culprit here, but I've never seen it used in a way which improves a business...

    2. Triggerfish

      I think it should be harder for a big company to fail at this, but many SME sized companies the IT dept is somewhat generalist. And this is aso a problem becauwhats so different between that and theyse you encouter just process issues and getting it off the ground.

      If say you are lucky and have someone who can create a db anda decent frontend, you need a PM to ease the scope creep, juggle stakeholders covey what they want to the techs in, you need stakeholders who dont really know what they want and likely don't get between what is being propsed and their excel 'database', you need to be able to explain the extra development and running costs gaints this.

      User issues have a short distance to c-suite and a lot of their work has the similar short distance, thats added things to manage.

      You have users that are sticky as hell to the old system (I once got roped into a fucked erp install (1st job off of helpdek of all things...) and the most fucked thing about it was all the users where point blank refusing to use it and still running all the ordering etc from the old excel, macroed, pivot tables 'now shadown' ERP system they had been using for years. (Admittedly the install wasnt confidence inspiring either),

      For me excel is a problem and some of that problem is lack of understanding. Better training on even basics of data integrity and spreadsheet construction so on would go a long way, everyone is supposed to just know spreadsheets when they come in and they get a lot more complicated than word in daily use.

      IInstead of everyone teaching I had some spreadsheet mokneys who know the trick and work them closely with the IT dept.

      IT dept needs to get access to a decnt PM to work with the stakeholdes, it still may not be faster but theres way more conisderations sometimes also. But at least a project moves along (theorectically).

    3. a pressbutton

      I was waiting for someone to say "Agile" and "Prototyping"

      1. Doctor Syntax Silver badge

        Prototyping is fine, if only to show the users why their idea won't work.

        OK, being serious, a prototype can be useful in getting users to discuss and think through what it is they need. A former colleague had a bit of a problem with this approach: he could never get his users to realise that what he was presenting as a model for discussion wasn't the real thing so his attempts got snared up in their rubbishing this unsatisfactory "product".

  12. Arthur the cat Silver badge

    Reincarnation

    There is a perennial fad for finding deep truths in religion. Really only one concept stands up: without reincarnation, how can one explain the constant reappearance of old ideas as new in IT?

    Never mind religion, think of horror films. The Big Bad has finally suffered an unsurvivable event. The plucky nubile heroine tentatively approaches his corpse. And then he sits up.

  13. aerogems Silver badge

    Make other tools better

    SAP is a big offender here. Best case scenario a t-code lets you export data to a spreadsheet. If you're less than lucky it might be something like a tab delimited text file. Since you can't really do much in the way of manipulating data within SAP, you're stuck exporting it to Excel, doing your thing, then importing it BACK into SAP.

    We're blaming the wrong tool here. Sure, Excel sucks, but maybe instead of blaming a hammer because it makes for a poor screwdriver, we should stop creating situations where the only tool you have is a hammer.

  14. redwine

    awk/sed

    I do a lot of work on the command line before copy/pasting into excel for presentation.

    I sometimes try to do it directly in excel because that would make sense right? ... but no, it's too fiddly and things just don't work as you'd expect.

    awk/sed all the way!!!

  15. chivo243 Silver badge
    Stop

    first step

    make sure you've selected all columns and rows for you crucial calculation. My former boss once placed and order from "his" spreadsheet, using my hand collected data for every switch closet in the network. As the end of the refresh came near, I noticed that we were 3 switches short! Ensue lots of shouting, brow furrowing and finger pointing, by everyone in the office, he finally went back to his spreadsheet and found he had neglected to select the last row in his calculation!

    My handwritten list had the correct number at the bottom, plus spares for the shelf, glad the spares came in handy so fast! He got lucky, the project finished on time, and he only needed to order the spares.

    1. Christopher Reeve's Horse

      Re: first step

      This is where adhering to using the structured tables features in Excel can eliminate most of these kind of errors. If someone isn't using structured references in their excel formulae then they really should learn to.

  16. Anonymous Coward
    Anonymous Coward

    I respectfully suggest that Access is still the bigger danger. The number of projects I've had where the brief starts "This team have dug themselves into a hole with Access. Get them out".

    That said I did come across one case where Excel was being used as a Javascript generator for a very visible web application....

    1. Abominator

      And it's slow and killing the share drive.

    2. Martin Gregorie

      Couldn't agree more - the most messed-up small database projects I've been called on the optimize have all been written by people pretending to be DB designers and using Access to build their project.

      Most of these gentry wouldn't have recognized a data structure diagram, let alone think of drawing one as the an essential first step in their development process.

  17. Abominator

    Fucking hell.

    And it's still not got source control.

    Utterly unfit for purpose in the modern world.

  18. phillfri

    Excel is like any other tool

    Excel is like any other tool. If you haven't been trained (or seriously trained yourself) to use it and computers then you shouldn't be using it. But management today doesn't want to spend any money on employee training, and IT is far more interested in pushing everyone onto overpriced, recurring cost cloud systems so they can reduce their in house technical personnel budgets - resulting in untrained IT people as well as untrained employees. Once you learn how to use it and secure it, Excel is an extremely versatile tool when used for the purposes it was designed for. Which is exactly why it is forty years old and still used worldwide.

    1. Loyal Commenter Silver badge

      Re: Excel is like any other tool

      A hammer drill is an extremely versatile tool when used for the purposes it was designed for.

      I wouldn't use one for doing my tax returns, and I certainly wouldn't put one on every desk in your average office, but there you have Excel, installed in a business environment by default...

    2. Triggerfish

      Re: Excel is like any other tool

      It is a extremely useful tool, but as the poster says it becomes the hammer and every data storage problem becomes a nail.

      1. Christopher Reeve's Horse
        Coat

        Re: Excel is like any other tool

        It's a very useful hammer! Perhaps it's a matter of the user needing to be worthy of holding it!

        1. that one in the corner Silver badge

          Re: Excel is like any other tool

          Whosoever can wield this spreadsheet shall be granted forever ownership and a right bollocking when it goes wrong, no matter it is only your second week on the job.

  19. Strahd Ivarius Silver badge
    Holmes

    I've got a mail about an Office script repository...

    It is called spankoverflow.com, and you have just to type "Office" in the provided search box to get video tutorials.

    Or so I was told, for some reason the site is blocked from my workplace.

  20. Anonymous Coward
    Anonymous Coward

    Oo! Oo! I have one!

    Major reshuffle of posts in a huge national org. This comes in the form of a 'pink and grey'. Left side of the workbook in Excel is current post. It has about twenty columns for each person, one person to a row. The grey side is where they're going. Another twenty columns. You had to check the left which was a straight database pull, and then manually change it all in the right, to whatever you had written down on a paper notepad.

    There were blank spaces dotted all over the place, but that was fine, as you didn't filter or sort, you just changed the original entry in the grey.

    I'd just finished the nth sheet totalling about 1000 people. Saved all the copies, all good. Put that aside. A couple of days later I'm looking at them and it doesn't seem right.

    'Boss, did you review these sheets?'

    'Yes, I did.'

    'Did you sort or filter by any chance?'

    'Yes, just to make it easier to read.'

    '.... Did you save afterwards?'

    'Yes... Why?'

    Excel of course getting confused with blanks, who h meant entries were no longer aligned. So John in the pink on row 19 was mirrored in the grey on row 438.

    We couldn't establish when the last good copy was, because he'd been doing it on the old sheets for a while. Had to scrap it all and start again.

    1. Christopher Reeve's Horse

      Re: Oo! Oo! I have one!

      Again, the answer here is using structure tables and formulae. This way, the sort and filter controls will always apply to your entire databody range (table). Use 'Format as table' from the Home ribbon, and then reference the table name and columns/rows in your formulae directly using the table1[column1] type format. This table method even keeps column formulae consistent in most usage cases.

  21. bazza Silver badge

    Make Spreadsheets Better?

    I'm not entirely sure how that would be accomplished, whilst also maintaining the simplicity. Forget scripting / macros - the spreadsheet is already pretty optimal as a concept that a lot of people can grasp and run with.

    The problem I see is that there's a massive gap between a spreadsheet, and programming languages. The latter may offer "better" things - better separation of code and data - but it's a very big step between a spreadsheet with a few formulae dotted around it, and a piece of software absorbing and processing the same data and outputting the same result, with graphs, print-outs, etc.

    1. Christopher Reeve's Horse

      Re: Make Spreadsheets Better?

      Exactly this. Then also factor in continuous development requirements! The cost / complexity step of moving from a Subject Matter Expert managing their own Excel data model to a corporate IT controlled tactical application that the SME can still operate and improve is HUGE.

      1. bazza Silver badge

        Re: Make Spreadsheets Better?

        This is perhaps why it's important that everyone going through the education system has to learn to code, at least to some extent.

        Better everyone becomes somewhat proficient, at least to know what is possible and when to call in an expert, than carrying on in ignorance with a spreadsheet. And if every other SME is also a half way decent coder, so much the better! For example, it's interesting to see loads of biologists / immunologists who are now busily learning R.

        I don't know what you think, but I don't think that purely graphical languages are an answer. Better something text based, with help (eg Jupyter?)

  22. david 12 Silver badge

    Excel has fixed more spreadsheet errors than it has created.

    Doesn't anybody else remember spreadsheets as sheets, spread out on desks, with the numbers penciled in, then inked in when 'finalized' ?

    And copied by hand from one spreadsheet to the next, with summery sheets calculated on calculators, and child sheets created by dividing up the pool?

    Spreadsheets were always full of errors, finding, correcting, and avoiding those errors was one of the great reasons spreadsheet programs were so popular. That and printers -- printing out your spreadsheets was awsome.

    1. Anonymous Coward
      Anonymous Coward

      Re: Excel has fixed more spreadsheet errors than it has created.

      So have spelling correctors:

      And copied by hand from one spreadsheet to the next, with summery sheets

  23. Potemkine! Silver badge

    how can one explain the constant reappearance of old ideas as new in IT?

    They may be not so much new ideas in IT, but most of the time old ones coming back on another aspect/variation/disguise.

    "New" is often just marketing hype.

    == Bring us Dabbsy back! ==

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Other stories you might like