Main menu

Post navigation

The numbers don’t lie

English: Captain Smith of the Titanic. This photo appeard in the New York Times some days after his death in the sinking of the Titanic. Français : La capitaine Edward John Smith, mort à bord du Titanic. La photo a été publiée dans le New York Times peu après le naufrage. (Photo credit: Wikipedia)

Commander Edward John Smith had a reputation as a safe pair of hands. He had been a sailor for over 30 years and had been a captain for nearly a quarter of a century. He was decorated and saw service briefly in the Boer War. Whenever his company had a new high-profile assignment, he was the natural choice. He ran his vessels like clockwork. Every man was left in no doubt as to where he should be and what he should be doing. He established a routine, such that anything out of the ordinary would stick out like a sore thumb.

The chief stoker below decks carefully monitored the instruments showing the head of steam and made sure that the stokers shoveled just the right amount of coal into the fireboxes. The men on the bridge knew how many knots the ship should be making and which heading they should be on. They were trained to tap their instruments occasionally to make sure that the sensitive needles, being mechanical in nature, did not get stuck in any one position.

All this so that Commander Edward John Smith could spend time with his passengers, making sure that their voyage was the best it could be. One particularly cold and foggy night, a night that has become an immutable historic event, Commander Edward John Smith’s vessel struck an iceberg and sank. Right up until the moment of collision, the readings on all the instruments were perfectly OK.

NASA is an incredible organization, started over 50 years ago by the US Government in order to pioneer the future in space exploration, scientific discovery and aeronautics research. In that time they have spent nearly a trillion dollars in today’s money exploring the near and far reaches of outer space. Space exploration is a risky business, so they have had their fair share of mishaps and in this kind of endeavour, they are usually fatal. One such accident was the Space Shuttle Columbia disaster, in which a number of astronauts lost their life when their craft exploded and broke up on re-entry into the Earth’s atmosphere.

NASA knew that the space shuttle was not perfect. They knew that the heat-resistant tiles had a habit of falling off under the stress of the mission and they had a system in place to deal with it. When the space shuttle reached its destination (usually the International Space Station), the number, position and types of tile that had come off were recorded in a spreadsheet and a model used to decide whether the damage was severe enough that it needed repairs or whether the mission could carry on regardless. On the mission in question, the missing tiles were recorded, the numbers crunched in Excel, and everyone breathed a sigh of relief. There was no need to undertake expensive and time-consuming repairs, the shuttle was safe to continue. Unfortunately, as we all know, it wasn’t.

There are many well documented cases where spreadsheet mistakes have had catastrophic results (http://www.eusprig.org/horror-stories.htm) and indeed, there are several scholarly articles which give sage advice about the risk of relying on spreadsheets and yet they are almost universally relied upon. They have a habit of multiplying because people take copies of spreadsheets and make their own changes to do their own analysis. They have a habit of networking with other spreadsheets and then feeding into spreadsheets into still more spreadsheets.

All of this means that a single mistake in a spreadsheet can have a wide-ranging effect. And because spreadsheets tend to be appended to, the effect of such a mistake can multiply over time. Given the potential for error – why do we do it ?

There is no arguing that a spreadsheet is a powerful tool, and it’s precisely that power that is so enticing. It draws us in with its ease of use. The way that seas of figures can be magically crunched in the blink of an eye means that they are tremendous labour-saving devices. Beautiful three-dimensional graphics turn the boring figures into extremely persuasive visual metaphors for the point that we are trying to argue. But you must always be on your guard, for they are constantly trying to lure you over to the dark side….

They want you to take their figures at face value. They want you to trust them. Once it is in a spreadsheet – then surely it must be the truth, and it is. The spreadsheet contains the exact results of all the numbers in the spreadsheet after the formulae have been applied (assuming your hardware is all in order http://www.willamette.edu/~mjaneba/pentprob.html). It only takes one number or one formula to be wrong and your numbers cease to be reliable.

Just remember, when all your numbers look OK – look up now and then to make sure you’re not heading for an iceberg…