Risk: Systemic Financial Collapse. Tags: Government

March 20, 2019 4:43 PM

Spreadsheet Horror Stories "These stories illustrate common problems that occur with the uncontrolled use of spreadsheets. In many cases, we identify the area of risk involved and then say how we think the problem might have been avoided."

1800 BCE marks the world's oldest known spreadsheet error

posted by BungaDunga (49 comments total)

I’m not sure that “spreadsheet error” is quite right for the ancient one, but ... I’m going to allow it.

A great site. Thanks.
posted by Gilgamesh's Chauffeur at 5:29 PM on March 20, 2019 [2 favorites]

But, dude, it's an ancient table of primitive Pythagorean triplets. It's 3800 years old, and predates PYTHAGORAS by like 1500 years... That's freaking awesome.
posted by kaibutsu at 5:49 PM on March 20, 2019 [2 favorites]

This site feels like a horror story. Like, I know I'm reading a story that's intended to scare me. Or I'm watching a movie that's going to jump scare me. And the preamble is a little boring, but that just makes it worse. (you didn't rent this movie from the horror section expecting a calming denouement). Everything seems so normal....except...here comes the scare!!!!

The pay off is that it's bad data presented badly. So the boring set up doesn't turn into horror or porn or anything. It just turns into bad data. The horror is that we're safe.

Sometimes I worry that I'm living my worst life...At least I don't find this site amusing. If you do, I can offer some 1-800 numbers.
posted by es_de_bah at 5:53 PM on March 20, 2019 [3 favorites]

I am not an accountant(worse - I'm an engineer!) , but I always make sure my spreadsheets add up by throwing in some ersatz double-entry bookkeeping. How hard is that?
posted by notsnot at 6:00 PM on March 20, 2019 [5 favorites]

I'm my organization's resident spreadsheet apologist. A lot of these stories are not the fault of the spreadsheet. I have seen people making very similar mistakes using very expensive special-purpose accounting software. There's a quote I read somewhere once (I can't recall the source) that all business requirements eventually end up describing Excel.
posted by majorsteel at 6:16 PM on March 20, 2019 [18 favorites]

I once, being a self-anointed spreadsheet maven at the time, designed a double-entry accounting system in VP Planner Plus, a spreadsheet front end to a five-dimensional matrix array. Yeah, it was kludgy and "user friendly" was definitely not in the cards. But it worked, after a fashion. After that, "the right tool for the job" became my mantra...
posted by jim in austin at 6:19 PM on March 20, 2019

Paging cstross, cstross to the spreadsheet analysis room, please...
posted by prismatic7 at 6:21 PM on March 20, 2019 [5 favorites]

I’m not sure that “spreadsheet error” is quite right for the ancient one, but ... I’m going to allow it.

posted by Gilgamesh's Chauffeur

Oh, come on.
posted by Halloween Jack at 6:46 PM on March 20, 2019 [44 favorites]

I was thinking about all of the other customers, whose spreadsheets probably had the same error. Would the accountants audit all of the spreadsheets that copied the error

Ok so I have decided that software is a public service and should be provided for free by the government. This cheap ass power company isn't the first or only to decide the right software was too hard to get or too expensive or too hard to use or all of the above. I worked for MANY who thought that an underpaid temp and an ancient, broken laptop containing nothing but Excel was the right way to solve software problems and I now believe this is how we're going to end humanity somehow.
posted by bleep at 8:18 PM on March 20, 2019 [4 favorites]

Not with a bang, but with a #REF! error.
posted by turbid dahlia at 8:28 PM on March 20, 2019 [44 favorites]

Yeah, a popular use case for the product I work on is "Organization Y is doing way too much with a shared spreadsheet and can't deal with it anymore". Spreadsheets are still the best tool for a lot of jobs but they're only as good as the way they're set up.
posted by OverlappingElvis at 9:22 PM on March 20, 2019 [4 favorites]

Back in the day I temped at Universal Studios. I had a three week assignment adminning for a VP whose remit was selling DVDs into Blockbusters and Hollywood Videos. After a couple days of doing a terrible job with the phones, the VP happened along while I was playing around with a macro in Excel (the Internet was a thing then to suck up time, but MetaFilter was not, so why not play with VBA?) and he asked how much I knew about Excel. As it happens, quite a bit! I’d been an accounts receivable clerk at a dress factory and had worked with spreadsheets in 123 and built out automated costing sheets for the designers in Excel and...

Well that’s wonderful he said. How’d you like to rework the DVD ordering template I send out to the Blockbusters and Hollywood Videos?

Sure!

Okay, but the deal is I want them to know that if they take however many A list titles, they also have to take this many dogs. Can you make it do that?

Sure!

Okay, the other thing is these are like franchises and I have different deals with different guys. So I have to be able to adjust the A list to dog list ratio for each deal, and nobody on the other end can catch on. Otherwise, everybody will want the same deal I’m stuck with with that guy in Arizona. Can you make a sheet that does that?

Uhh, probably!

I spent the next 2.5 weeks learning about password protected hidden tabs and data entry forms in Excel and a couple other things, et voila! My sheet was guiding the ordering decisions of two of Universal Home Entertainment’s largest business partners.

That was my favorite temp assignment at Universal except for the one in which I got to put Sumner Redstone on hold.
posted by notyou at 9:24 PM on March 20, 2019 [30 favorites]

In my work, I stumbled across a spreadsheet of divorce cases where someone had inserted a name into the "petitioner" column, but not the "respondent" column, so that all the petitioners were then petitioning to be divorced from the wrong person - the respondent from one case further down. Hundreds of divorce cases had the wrong names in them. This was discovered because we were transferring the records to a real database, otherwise it would still be there, I think.
posted by Mogur at 11:00 PM on March 20, 2019 [9 favorites]

I work for one of the large global professional service firms. Once Sarbanes Oxley regulations came into effect in the US in the early 2000’s I can tell you we made tens - if not hundreds - of millions of dollars over several years performing “spreadsheet risk” services, as public companies suddenly needed to show that the spreadsheets that were the real life blood of their business had appropriate controls to meet SOX 404. I’m getting the sweats just thinking about folks trying to apply controls frameworks and do business gymnastics to show how they had implemented “change control” and “access control” over these sheets. Of course all the spreadsheet risk service assessments were themselves done in spreadsheets, mostly built by first time spreadsheet users. Turtles all the way down etc. Fun times.
posted by inflatablekiwi at 11:23 PM on March 20, 2019 [13 favorites]

There's something delightfully nerdy about those Babylonian tablets with their rows and columns and occasional errors. It reminds me of the feeling I get when I've spun up a new Excel sheet to figure something out and can sit back in pleasant satisfaction from having solved a problem and learned something new.

And then I bold the column titles. Always.
posted by Enkidude at 11:33 PM on March 20, 2019 [12 favorites]

In relation to this thread, I was wondering about all the minions who might lie awake at night and worry about all the potential errors in the business critical spread sheet that they may have created - or about just how much sensitive data that spreadsheet might contain. Then I thought about all the directors and senior management of the organisations that might have employed the worrying minions: some of them secretly concerned also; most of them oblivious. But at the top of the pyramid: - the ones who should inherit a share of the cumulative worries - along with the kudos - are those who invented the software. Since they are often a bit anonymous - a shout out to the 3 Doctor Frankensteins: Dan Bricklin ("Father of the Spreadsheet" and creator of VisiCalc in 1979), Mitch Kapor (creator of Lotus 123) and Douglas Klunder (creator of Multiplan - Microsoft's first spreadsheet - and then a lead creator of Excel which first appeared ,for the Macintosh only, in 1985).

How do they sleep at night? - not sure (but given their age - they maybe wake for bathroom break or two).
posted by rongorongo at 12:07 AM on March 21, 2019 [7 favorites]

Excel is a programming environment used for critical decisions, programmed by people often with no training, and almost nobody writes tests. Oh, and the code is mostly hidden.
posted by BinaryApe at 1:14 AM on March 21, 2019 [28 favorites]

rongorongo — if they're anything like the acquaintance who published and funded VisiCalc (from a chess program for the Kim-1) likely quite well. It's the ones who didn't use decimal maths in their libraries who should be sleepless.
posted by scruss at 1:54 AM on March 21, 2019 [1 favorite]

rongorongo - judging by your knowledge I would guess you are yourself involved in s/w dev but just in case ... I think it's fair to say that anyone who makes a claim to "professional software developer" (or similar) is horrified by what users get up to with spreadsheets and I would have thought it's likely that's true for Dan, Mitch and Douglas.
posted by southof40 at 3:52 AM on March 21, 2019 [3 favorites]

I did a stint as a BI reporting consultant around the beginning of the noughties and there is nothing more guaranteed* to bring on the cold sweats than being told "Our director designed our current reporting solution. He's a bit of an Excel expert".

Cue the next 2 days trying to disentangle 30 cross-referenced XLS files each with 20+ tabs.

*Coming close in second place is hearing "He's a bit of an Access expert"...
posted by jontyjago at 3:56 AM on March 21, 2019 [14 favorites]

Here's why Excel is used everywhere. It's Free*. Like everyone knows that a custom programmed database would be better for most applications, but that would cost hiring a programmer, setting up and maintaining a server, and taking the months to do it. You could pay OvelappingElvis $50-100 a month to do it in the cloud, but most office schleps can't get that budget for stationary. So Excel it is.

*By free I mean already paid for. I think products like airtable etc would sell like hotcakes if they were way cheaper, or pay-once, instead of pretty user per month. That's basically a non starter for many.
posted by Popular Ethics at 6:04 AM on March 21, 2019 [7 favorites]

I just wish that Excel was less hlepful about permanently, non-reversibly converting anything into a date if it can possibly, by any stretch of imagination, be interpreted as a date. This is the source of most of the gene name errors in one of the items at the link.

I also wish that Excel was less hlepful about incrementing numbers at the end of character strings when you fill down. I almost always want to keep referring to “Item1” when I fill down, not “Item2” through “Item84.”

People at work like Excel because “it’s visual and you can see what’s happening”, as opposed to R, which seems opaque to them. Maybe that’s even true for simple applications. The problem is when you are writing and filling nested if-else formulas five lines long that do INDEX/MATCH lookups on seven different sheets, and you are having to add dozens of auxiliary calculation columns labeled HIDE THIS, and it becomes actually impossible to check or debug. HOW IS THIS EASIER THAN WRITING ACTUAL CODE. (Based on a real-life experience.)
posted by snowmentality at 6:18 AM on March 21, 2019 [10 favorites]

I am/was??? An enterprise architect? The horrors I have seen excel used for are many and funny. But part of my very real mental illness is triggered by very stupid practices by very large corporations. This week I have factually been unable to work at all.

Why? Good reader... because This week I was asked to transfer highly sensitive data both corporate and customer... to..... google sheets.....

So I started making a puppet because I would rather lose my house and keep my sanity....
posted by mrgroweler at 6:18 AM on March 21, 2019 [11 favorites]

You also have to get some senior vp who is still using excel 2003 to be able to see your data. Yes that is a real example I experienced from 2013.
posted by winna at 6:18 AM on March 21, 2019 [3 favorites]

This is like the SCP Foundation for accountants and office workers.

*whispers* Pivooooot taaaables.
posted by loquacious at 6:26 AM on March 21, 2019 [13 favorites]

I read somewhere once (I can't recall the source) that all business requirements eventually end up describing Excel

This helps me understand iTunes a lot better now.
posted by nikaspark at 6:51 AM on March 21, 2019 [9 favorites]

The ultimate problem here has fuck all to do with spreadsheets or any other software tool. The fundamental problem is that people believe whatever shit the machine spits out, whether it makes a lick of sense or not. It's far too much effort to estimate things in your head and check them against the results.

There are access control and change management issues with spreadsheets that can't really be worked around, but the reporting errors causing figures to be misreported by millions of dollars are stupid human tricks. (Often brought on by management piling on an impossible level of work to the few productive people in their organization, not the humans doing the work literally being stupid.)
posted by wierdo at 7:08 AM on March 21, 2019 [6 favorites]

I also wish that Excel was less hlepful about incrementing numbers at the end of character strings when you fill down. I almost always want to keep referring to “Item1” when I fill down, not “Item2” through “Item84.”

If you use ctrl+D to fill down, that's exactly what it does. You can do it cell by cell, or select an entire run of cells and hit ctrl+D.

ctrl+R does the exact thing except it copies what's in the left field to the right field.
posted by teleri025 at 7:51 AM on March 21, 2019

Here's why Excel is used everywhere. It's Free*. Like everyone knows that a custom programmed database would be better for most applications, but that would cost hiring a programmer, setting up and maintaining a server, and taking the months to do it. You could pay OvelappingElvis $50-100 a month to do it in the cloud, but most office schleps can't get that budget for stationary. So Excel it is.

*By free I mean already paid for. I think products like airtable etc would sell like hotcakes if they were way cheaper, or pay-once, instead of pretty user per month. That's basically a non starter for many.

MySQL?
posted by leotrotsky at 7:53 AM on March 21, 2019 [1 favorite]

This is like the SCP Foundation for accountants and office workers.

*whispers* Pivooooot taaaables.

The phrase, "Watch out for the slicers!" applies equally well in both contexts.
posted by leotrotsky at 7:57 AM on March 21, 2019 [2 favorites]

Here's why Excel is used everywhere. It's Free*.

When I was in school in the 90s/00s, it was also something that every kid learned to use in school, along with Word. Not use well, mind you, but the very basics.

For most people, saying "a programming environment would be better than Excel for this" is like saying "but Linux is so much more flexible than a PC!"
posted by showbiz_liz at 8:39 AM on March 21, 2019 [4 favorites]

You an stop Exel from incrementing using absolute vs. relative references by column($A1), row(A$1), or both($A$1). I have built a few spreadsheets and it helps to have somebody else audit for errors.
posted by theora55 at 8:53 AM on March 21, 2019 [2 favorites]

FTFA: Emailed spreadsheet contained private data in ‘hidden’ columns

One fine summer afternoon in 2005, my alma mater's communications office sent around a spreadsheet to everyone who was staying on campus for a summer research grant. This sreadsheet detailed the students' names, the professors they were working with, and their housing information/contact info, in adjacent columns a, b, c, and k.

Wait. K?

A little sleuthing (i.e. "dragging the column headers to expand the width of columns d-j to more than 0 pixels") by the intrepid three of us working in the CS lab quickly paid dividends, as we discovered that someone had just appended the summer contact info to the Communications and Development master spreadsheet, which included such choice tidbits as "how much tuition this student's parents are paying vs. how much need-based aid is the student getting," "income profile of family based on FAFSA data we somehow acquired," and "how aggressively we should pursue this student's parents for major gifts."

Out of a sense of basic decorum, we emailed the director of the summer program with news of what we had found. As far as I know, no one else on the mailing list figured it out, but the IT department definitely tipped their hand as to their level of control over the college email server, because that message disappeared down the memory hole in about 4 minutes. I can only assume that many Shubs and Zuuls knew what it was to roast in the depths of a Sloar that day.
posted by Mayor West at 9:00 AM on March 21, 2019 [12 favorites]

→ *whispers* Pivooooot taaaables.

sortinng alll the coluuuumns exceeept for onnnnne …
posted by scruss at 9:09 AM on March 21, 2019 [6 favorites]

The first thing I do in any spreadsheet is unhide all tabs and all rows and columns in each sheet. You’d be AMAZED how often you find things like that.
posted by winna at 9:48 AM on March 21, 2019 [4 favorites]

I once - ONCE - sent a budget to an external partner org in a spreadsheet, and then realized that my personal household budget was on another tab.

Nowadays, when I prep a budget in Excel (the main thing I use it for), I only send a PDF version out. That's fine in my line of work and eliminates the possibility of Embarrassing Spreadsheet Oopsies.
posted by showbiz_liz at 10:28 AM on March 21, 2019 [6 favorites]

With Google sheets it's not even enough to delete data, you've got to detach it from its version history too. Otherwise you can just undo the deletion.
posted by paper chromatographologist at 10:46 AM on March 21, 2019 [1 favorite]

I fucking love Excel. It's literally the only Microsoft product I have any strong positive feelings for.
posted by aspersioncast at 10:57 AM on March 21, 2019 [8 favorites]

→ *whispers* Pivooooot taaaables.

sortinng alll the coluuuumns exceeept for onnnnne …

leeeeeading zeeeeeeroes
posted by slogger at 11:00 AM on March 21, 2019 [7 favorites]

Merge cells. Merge cells and freeze pane were created by the devil. Freeze pane breaks the ctrl-home behavior.
posted by winna at 1:38 PM on March 21, 2019 [2 favorites]

I do the financial reporting for a small bank, using Excel. I grab several data feeds at the beginning of the month, crunch them into source workbooks, and then tie my various and sundry monthly financial report to the new source workbooks. It seems simple and straight forward, with data linked to pivot tables. All fine and good.

But, when a manager wants a modification to our reporting I realize that I have an almost superstitious dread of changes. What if I break something? Well, the answer is to run parallel for a couple months. There has to be a better way.

Always, when I send reports out I either copy/paste values into a new report, or I print to PDF. No one needs to get the links to the source tables.

Some day we will hire a smart youth to bring us properly into the current era. My biggest challenge will be to deal with the superstition against change.
posted by Midnight Skulker at 1:58 PM on March 21, 2019 [2 favorites]

This thread is the computer equivalent of having a cricket enthusiast give you a play-by-play.
posted by lollymccatburglar at 2:11 PM on March 21, 2019 [6 favorites]

MySQL?

It might be free, but the average corporate desktop user cannot install anything...

And then... What friendly user interface do you put in front of it?

Access is not bad - as long as "VBA/macros" are disallowed. I often take raw data, do some massaging in Excel and bring it into Access for quick analysis using queries.

(And, Excel is still "free" - the cheapest version of Microsoft Office does not come with Access)

Actually... for awhile Access was in a "sweet spot" - when integrated with SharePoint's "Access Services" (2013-style), you could create "Web Databases", which could technically be SQL Server databases on the back-end (if the stars aligned and you had everything installed and configured correctly), and when using that as a starting point, it would disable VBA/macros.

Access then became a Form/Query/Report design tool - the results of which could then be presented through a browser-based SharePoint "site" construct and shared with many users. You could even centrally control your users local Access installations via "Group Policy" to only allow them to create "Web Databases"... No more VBA or Access databases stored on file-share nightmares...

... and now... it's kinda dead (well, in the "cloud" for sure)... "Gee, Thanks Microsoft !"

Shudders... memories... oh, such random data losses when using an Access database on a network share with multiple users... back in the mid-90's...

Personally, I estimate that at least 50% of my career has been turning Excel spreadsheets of varying complexity into actual applications/reports/dashboards... Originally Excel, then Access databases (with Visual Basic front-ends), then SQL Server, then SharePoint, then back around to Access "Web Databases", then...

Fun times...
posted by jkaczor at 3:08 PM on March 21, 2019 [3 favorites]

My first job out of college was Lotus 1-2-3 tech support, and I overuse and abuse spreadsheets to this day.
It's not the only tool in my toolbox, but with it I can nail just about anything down.
I regularly work in spreadsheets so large that it's faster to re-sort and create columns of single-comparisons rather than COUNTIF or Extract Unique...
I've constructed web pages through substitution, string concatenation, and conditionals.
Even as I've grown more proficient in SQL, I still tend to dump the results into Excel when I need to review or present them.

And now that I've gotten that off my chest, here's an episode of the Planet Money podcast about how spreadsheets changed the world.
posted by cheshyre at 5:37 PM on March 21, 2019 [4 favorites]

-(conditional) × value is my favourite horror from Excel. It's like a per-cell =COUNTIF()
posted by scruss at 7:55 PM on March 21, 2019 [2 favorites]

I'm literally - this very minute - sitting in a meeting watching an Excel atrocity unfold...
posted by wintermind at 7:14 AM on March 22, 2019 [2 favorites]

I'm curious how many of the Excel horror stories I hear would be mitigated if people knew how to use some of the features in Excel properly. I know that I've blown colleagues' minds by using named cell ranges to make formulas intelligible. How much spreadsheet pain is self-imposed? (This is an honest question; I work in a field where management by spreadsheet is decidedly rare and so I'm lucky enough to avoid exposure to the worst of these practices.)
posted by philosophygeek at 1:58 PM on March 22, 2019 [5 favorites]

I'm curious how many of the Excel horror stories I hear would be mitigated if people knew how to use some of the features in Excel properly.

Probably most of them. I'm always a little leery of showing my coworkers how I did something, in fear that they will half-understand and then screw everything up.

The most common problem I've seen is that at least one person in an office actually knows Excel pretty well, but another four or five people who very much don't end up collaborating on something, and one or more of them think they know what they're doing.
posted by aspersioncast at 7:14 AM on March 23, 2019 [1 favorite]

I used to own a small business back before personal computers were affordable. I kept the books in a literal spreadsheet, a paper ledger. The checks had a carbon stripe, so you kept a record of expenses and then assigned the expense to a column - inventory, maintenance, personnel, etc. It had to be tallied by hand with a calculator and usually took several tries to balance. Later, after I sold the business, somebody I knew got a job with Lotus and sent me a copy - on 5 1/4" floppies, many of them. In a cloth covered box, with a crappy manual. When I saw that Lotus could do in seconds what I used to spend hours on by hand, it was love. The sheer human toil that was spent adding columns of numbers. It is an amazing tool, much like the 1st electronic calculators were.
posted by theora55 at 9:22 AM on March 23, 2019 [8 favorites]