Mistakes in Excel: The 5 Biggest Spreadsheet Fails

These people probably have their worst days ever: They made fatal errors in their spreadsheets. But they are not alone: According to a study, 88% of all spreadsheets have errors. The reason – of course – is that every Excel file is created by a person – and people make mistakes.

The larger an Excel model gets, the more opportunities for mistakes exist. Many errors don’t really matter as they are not having an impact on the final results. But there are others which are quite serious. Let’s have a look at the 5 biggest Excel fails.

1. Formatting mistakes: MI5 bugging wrong phones

The MI5 – the British secret service – made more than 1 thousand bugging errors: They bugged the wrong phones. How can that happen? Apparently, a formatting error of a spreadsheet was the cause:

“A spreadsheet formatting error caused the service to apply for data on the identity of telephone numbers ending in 000, rather than the actual last three digits.”

The mistake happened in 2010. According to Sir Paul Kennedy, interception of communications commissioner, the formatting error was fixed. Also, the numbers are now checked manually.

2. Forgot minus sign: error at Fidelity’s Magellan fund

Ever accidentally swapped a minus with a plus sign? This accounted forgot to put a minus sign on the losses of $1.3 billion. So the loss was considered as a gain. That means, the result was $2.6 billion off. The New York Times says:

“In transcribing net gains and losses from the fund’s investments onto a spreadsheet used to calculate distributions, the accountant mistakenly transcribed a $1.3 billion loss as a gain.”

This error already happened back in December/January 1994/1995. Fidelity Investments had announced a year-end-payment to it’s shareholders. They promised a distribution of $4.32 per share. But when the mistake was discovered, the president of Fidelity, J. Gary Burkhead, said:

“Some people have asked how, in this age of technology, such a mistake could be made.”

3. Typed wrong number: Overbooked Olympic tickets

In 2012 the Olympic games took place in London. Booking tickets was done in rounds. After the first round, a staff member wanted to enter the remaining number of ticket for the next round of ticket sales. But instead of 10 thousand tickets, the member of the staff entered 20 thousand instead. The error was discovered when the organizing committee check the seating layout. 

Upon discovery, many of the customers have been contacted:

“In December we contacted around 3,000 customers who had applied for tickets in the four sessions during the second-round sales process. We are exchanging their synchronised swimming tickets for tickets in other sports that they originally applied for.”

4. “Honest mistake”: $1.1 billion error in financial result statement

Fannie Mae, the mortgage company, made an $1.1 billion mistake in a spreadsheet. They had to correct their financial results of the year 2003. Although the specific cause was not uncovered, the time pressure to adopt new accounting standards has led to the error. Jayne Shontell, Fannie Mae senior vice president for investor relations, said:

There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard.

5. Wrong formula range: Popular scientific paper has wrong results

It can even happen to scientist. An average formula didn’t cover the complete range of values as shown in the picture on the right hand side. The mistake happened in a popular paper by two Harvard professors in 2010.

The conclusion of the paper was that economic growth slows significantly if the debt of the country exceeds 90% of the countries Gross Domestic Product. The paper was published in the center of the economic crises and got a lot of attention.

The mistake was discovered by the student Thomas Herndon, who was doing a homework assignment. He says:

“My heart sank,” he says. “I thought I had likely made a gross error. Because I’m a student the odds were I’d made the mistake, not the well-known Harvard professors.”

-Bonus- Conversion mistake: NASA loses a spacecraft

Not really a spreadsheet error – but quite close: Many of the before listed mistakes could be fixed more or less easily. But when it comes to NASA, the impact can be quite grave: In this case, NASA lost a spacecraft on the journey to Mars – because of a conversion error. One team calculated in the metric system – whereas another team involved used English units (e.g., inches, feet and pounds). The result was that the acceleration readings provided in pound-seconds were mistaken for the metric measure of newton-seconds.

As the spacecraft took 9 months for the 461-million-mile distance to Mars, the miscalculations led to a wrong course and the Mars probe was destroyed in the Martian atmosphere.

Conclusion

As we can see, many people make mistakes in Excel spreadsheets. Of course, that’s not an excuse for being careless, but maybe our last mistakes don’t seem that serious after knowing the examples above.