by Matt Parker
It gets really interesting when you drill down into how these spreadsheets have gone wrong. The 6,650 spreadsheets with no formulas in them are basically being used as a glorified text document listing out numbers, so I’ll ignore them. I only care about spreadsheets that are doing some maths which may go wrong. So that’s the remaining 9,120 spreadsheets containing 20,277,835 formulas.
Excel does have one good layer of mistake prevention when someone is typing in a formula: it checks that all the syntax is correct. In normal computer programming, you can easily leave a spare bracket somewhere or miss putting in a comma. Which leaves you swearing loudly at a semicolon at 3 a.m. (‘What the hell are you doing there?’), or so I’ve heard. Excel at least does a cursory check that all your punctuation is in order.
But Excel cannot make sure that you use sensible functions or point them at the correct cells to feed the right data into the formulas. In those cases, it executes the commands and returns an error message only if the maths go completely wrong. #NUM! means the wrong kind of numerical data is being used; #NULL! means the input data range has not been correctly defined. There is also my favourite, #DIV/0!, for any attempts to divide by zero.
Hermans found that 2,205 spreadsheets had one or more Excel error messages. Which means that around 24 per cent of all formula-containing spreadsheets contained an error. And those errors had company: the error-prone spreadsheets had an average of 585.5 mistakes each.fn4 An astonishing 755 spreadsheets had over a hundred errors, and one spreadsheet took first place with 83,273 errors. At this point, I’m actually just impressed. I couldn’t make that many mistakes at once without a separate spreadsheet to keep track of them all.
But this is only a tiny subset of mistakes in spreadsheets: the obvious ones. Many more formula errors will be unaccounted for. Without having a deep knowledge of what the creator was trying to do in the first place, there is no easy way to scan spreadsheets and make sure the formulas are all pointing in the right places. This is probably the biggest problem with them. It’s easy to select the wrong column accidentally and, suddenly, the data is coming from the wrong year, or the data is gross instead of net (gross data indeed!).
This can lead to real problems. In 2012 the State Office of Education in Utah miscalculated its budget to the tune of $25 million because of what State Superintendent Larry Shumway called ‘a faulty reference’ in a spreadsheet. In 2011 the village of West Baraboo in Wisconsin miscalculated how much their borrowing would cost by $400,000 because a range being summed missed one important cell.
Those are just the simple ones which were found out. It’s no coincidence that they are both from public bodies in the US; they have a responsibility to the public and cannot easily sweep large mistakes under the rug. Goodness knows how many minor mistakes there are in the complex webs of formulas that exist in industrial spreadsheets. One Enron spreadsheet had a chain of 1,205 cells that fed directly from one to the next (with a wider net of 2,401 cells feeding in indirectly). One mistake in the weakest cell and the whole thing breaks.
This is before we even get to ‘version control’, which means making sure everyone knows what the most up-to-date spreadsheet is. Of the 68,979 Enron emails about spreadsheets, 14,084 were about what version of a spreadsheet people were using. And here is a real-world example of that going wrong. In 2011 Kern County in California forgot to ask a company for $12 million tax because they used the wrong version of a spreadsheet, missing $1.26 billion worth of oil-and-gas-producing property.
Excel is great at doing a lot of calculations at once and crunching some medium-sized data. But when it is used to perform large, complex calculations across a wide range of data, it is simply too opaque in how the calculations are made. Tracking back and error-checking calculations becomes a long, tedious task in a spreadsheet. It’s arguable that almost all my examples stem from when a more appropriate system has been overlooked in favour of Excel, which is, let’s face it, cheap and readily available.
A final warning from finance. In 2012 JPMorgan Chase lost a bunch of money; it’s difficult to get a hard figure, but the agreement seems to be that it was around $6 billion. As is often the case in modern finance, there are a lot of complicated aspects to how the trading was done and structured (none of which I claim to understand). But the chain of mistakes featured some serious spreadsheet abuse, including the calculation of how big the risk was and how losses were being tracked. A Value at Risk (aka VaR) calculation gives traders a sense of how big the current risk is and limits what sorts of trades are allowed within the company’s risk policies. But when that risk is underestimated and the market takes a turn for the worse, a lot of money can be lost.
Amazingly, one specific Value at Risk calculation was being done in a series of Excel spreadsheets with values having to be manually copied between them. I get the feeling it was a prototype model for working out the risk that was put into production without being converted over to a real system for doing mathematical modelling calculations. And enough errors accumulated in the spreadsheets to underestimate the VaR. An overestimation of risk would have meant that more money was kept safe than should have been, and because it was limiting trades it would have caused someone to investigate what was going on. An underestimation of VaR silently let people keep risking more and more money.
But surely these losses would be noticed by someone. The traders regularly gave their portfolio positions ‘marks’ to indicate how well or badly they were doing. As they would be biased to underplay anything that was going wrong, the Valuation Control Group (VCG) was there to keep an eye on the marks and compare them to the rest of the market. Except they did this with spreadsheets featuring some serious mathematical and methodological errors. It got so bad an employee started their own ghost spreadsheet to try and track the actual profits and losses.
The JPMorgan Chase & Co. Management Task Force did eventually release a report about the whole shamozzle. Here are my favourite quotes about what happened:
This individual immediately made certain adjustments to formulas in the spreadsheets he used. These changes, which were not subject to an appropriate vetting process, inadvertently introduced two calculation errors, the effects of which were to understate the difference between the VCG mid-price and the traders’ marks. (p. 56)
Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR. (p. 128)
I find that incredible. Billions of dollars were lost in part because someone added two numbers together instead of averaging them. A spreadsheet has all the outward appearances of making it look as if serious and rigorous calculations have taken place. But they’re only as trustworthy as the formulas below the surface.
Collecting and crunching data can be more complicated, and more costly, than people expect.
FOUR
Out of Shape
The geometry of the football on UK street signs is wrong. It may seem inconsequential, but it really bugs me.
If you look at the classic football design, you will find twenty white hexagons and twelve black pentagons. But on the UK street signs for a football stadium the ball is made entirely of hexagons. No pentagons! The dark pentagons have been replaced by hexagons. Whoever designed it must not have bothered to look at a real football. So I wrote to the government.
Or, more specifically, I started a UK parliamentary petition. This is an official type of petition which you need to apply for but which guarantees a response from the government if you get 10,000 signatures. My first application was unsuccessful because the petition committee said, and I quote: ‘We think you’re probably joking.’ I had to write back to argue my case: I was serious about accurate geometry. Eventually, the UK government agreed that I’m not funny and allowed the petition.
It turns out I was not the only person annoyed by the incorrect football on UK traffic
signs. The petition was featured in several national newspapers and on radio stations. I’d never appeared in the BBC News sports section before. I was invited on to different sports-based programmes, where I spent a lot of time saying things like: ‘A pentagon has five sides, but if you look at the signs, all of the shapes are hexagons, with six sides.’ I was basically making the argument that five is a different number to six. Did I mention that I hold a position at learned institution Queen Mary University of London as their Public Engagement in Mathematics Fellow? They must be so proud.
An actual football and, I don’t know, maybe a traffic sign for ‘bee-hive ahead’?
But not everyone liked it. Some people got very angry that I was asking the government to do something they did not personally believe in. I’d made it very clear that I did not want to change the old signs (even I can appreciate that might be a misuse of taxpayer funds). I just wanted them to update Statutory Instrument 2016 No. 362, Schedule 12, part 15, symbol 38 (I did my homework! Told you I was serious) so all future signs would be correct. But that was not enough to please many people.
In my interviews I was very clear that incorrect footballs on signs was not the most pressing issue facing society. But just because I also think we should adequately fund public health, education and so on does not mean I can’t also campaign for the more trivial things in life. My main point was that there is a general feeling in society that maths is not that important; that it’s okay not to be good at it. But so much of our economy and technology requires people who are good at maths. I thought the government acknowledging that there is a difference between hexagons and pentagons would raise awareness of the value we should place on maths and education. Five is a different number to six!
And for the record, the signs are super-wrong.
This was not just a picture of a different type of football: it could not even be a ball. That feels like a grand statement: that you could never make a ball out of hexagons. But I can state with complete mathematical confidence that it is impossible to make a ball shape out of only hexagons, even if they are distorted hexagons. It’s possible to prove mathematically that the image on the signs could never be a ball. There is something called the Euler characteristic of a surface which describes the pattern behind how different 2D shapes can join together to make a 3D shape. In short, a ball has an Euler characteristic of two and hexagons on their own cannot make a shape with an Euler characteristic of more than zero.
Who’s up for a game of geometrically plausible foot-doughnut?
There are different shapes which have Euler characteristics of zero, such as the torus. So while you cannot make a football out of hexagons, you can make a foot-doughnut. Hexagons are also fine for a flat surface or a cylinder. A friend of mine (hilariously) bought me a pair of football socks because they had the classic football-sign pattern with all hexagons: but because a sock (ignoring the toe) is a cylinder, that is fine. His gesture was both genius and cruel; the socks were simultaneously right and wrong. I’ve not been so conflicted about a pair of sports socks since Year 9 PE lessons.
I describe these as my ‘plane socks’.
This does not exclude the possibility that the street signs show some exotic shape which appears to be all hexagons on the side facing us but has some other crazy shapes going on around the back. After I complained about this online, a few people rendered such crazy shapes in the misguided belief it would make me feel better. I appreciate their effort. But it didn’t.
Through all of this, people were signing the petition and, before long, I hit the ten thousand required signatures and began eagerly to await the response from the government.
When it came, it was not good.
Changing the design to show accurate geometry is not appropriate in this context.
– UK Government, Department for Transport
They rejected my request. With a rather dismissive response! They claimed that 1. the correct geometry would be so subtle that it would ‘not be taken in by most drivers’ and 2. it would be so distracting to drivers that it would ‘increase the risk of an incident’.
And I felt that they hadn’t even read the petition properly. Despite me asking for only new signs to be changed, they ended their reply with: ‘Additionally, the public funding required to change every football sign nationally would place an unreasonable financial burden on local authorities.’
So the signs remain incorrect. But at least now I have a framed letter from the UK government saying that they don’t think accurate maths is important and they don’t believe street signs should have to follow the laws of geometry.
Tri-hard
There is more than one way to make a geometric mistake. To me, the less interesting way is when the geometry theory is solid but someone makes a miscalculation when doing the actual working out – even though that kind of error can lead to some pretty spectacular consequences.
In 1980 the Texaco oil company was doing some exploratory oil drilling in Lake Peigneur, Louisiana. They had carefully triangulated the location to drill down to look for oil. Triangulation is the process of calculating triangles from fixed points and distances in order to locate some new point of interest. In this case, it was important because the Diamond Crystal Salt Company was already mining through the ground below the lake and Texaco had to avoid drilling into the pre-existing salt mines. Spoiler: they messed up the calculations. But the results were more dramatic than what you’re probably imagining.
According to Michael Richard, who was the manager of the nearby Live Oak Gardens, one of the triangulation reference points was wrong. This moved the oil drilling about 120 metres closer to the salt mines than it should have been. The drill made it down 370 metres before the drilling platform in Lake Peigneur started to tilt to one side. The oil drillers decided it must be unstable, so they evacuated. Arguably, the salt miners had an even bigger surprise when they saw water coming towards them.
The drill hole was only about 36 centimetres across, but that was enough for water to flow from Lake Peigneur down into the salt mines. Thanks to good safety training, the mining crew of about fifty people was able to evacuate safely. But how much water could the mine take? The lake had a volume of around 10 million cubic metres of water to give. But the salt below had been mined since 1920 and the mines now had a volume greater than the volume of the lake above.
As the water gushed down, earth was eroded and salt dissolved. Soon, the 36-centimetre hole had become a raging whirlpool 400 metres in diameter. Not only did the entire lake empty into the salt mine, but the canal joining the lake to the Gulf of Mexico reversed direction and started to flow backwards into the lake, forming a 45-metre waterfall. Eleven barges which were on the canal were washed into the lake and dragged down into the mine. Two days later, the mine was completely full and nine of those barges bobbed back to the surface. The whirlpool had eroded away around 70 acres of nearby land, including much of Live Oak Gardens. Their greenhouses are still down there somewhere …
Because of the miscalculation of a triangle, a freshwater lake which was only about 3 metres deep was completely drained and refilled from the ocean. It’s now a 400-metre-deep saltwater lake, and this has brought a complete change in plants and wildlife. Amazingly, there was no loss of human life, but one fisherman out on the lake did have the fright of his life when the peaceful water suddenly opened up into a raging whirlpool.
As devastating as a miscalculation like that can be, I’m more interested in geometry mistakes where someone has not properly thought through the shapes involved – situations where the geometry itself is wrong, not just the working out. Which brings me to one of my favourite hobbies: finding pictures of the moon which have stars shining through them.
Moon unit
The moon may be a sphere but, from where we’re standing, it looks like a circle. Or, to be technical, a disc. (In maths, a circle and a disc are different things: a circle is just the line around the circumference, and a disc is completely filled i
n. A frisbee is a disc; a hula-hoop is a circle. But I’m going to use them interchangeably, as they are in normal language.)
So, when we look up from the Earth we can see the disc of the moon, at least when it is a full moon. Then the moon is on the far side of the Earth from the sun and can be fully lit. Any positions in between mean that the moon is being illuminated from the side and we see only parts of it in the light. This is the stereotypical crescent moon of art and literature. But it is just a lighting effect. The moon is not actually crescent-shaped.
Even when we cannot see parts of the moon, they are still physically there. During a new moon, when it is completely lit from behind, it appears only as a black, starless circle in the sky. For while we sometimes cannot see the moon, it is still there as a silhouette. Which is why I get upset when a crescent moon is shown with stars visible through the middle of it!
Sesame Street is a repeat offender. In Ernie’s book I Don’t Want to Live on the Moon, the cover shows stars shining right through a crescent moon. And in a ‘C in space’ segment, the moon looks surprisingly happy, despite the fact that these stars are shining through it. Okay, yes, the moon having a face and emotions is not astronomically accurate either, but that is still no excuse for teaching children inaccurate geometry. I expect more from a supposedly ‘educational’ programme. The only explanation I can think of is that, in the extended Sesame Street universe, there are Muppet bases on the moon, and those are the dots of light we are seeing.
Only if you assume those are lunar stations does this make sense.
Worse, there are Texas vehicle registration plates which celebrate NASA’s presence in the Lone Star State. The space shuttle taking off on the left is surprisingly accurate, ascending sideways instead of directly up. This may look incorrect, but the space shuttle needed a huge amount of sideways speed to be able to get into orbit. Space is not that far away: as I type, the International Space Station is at an altitude of only 422 kilometres. But for something to stay in that orbit, it needs to be moving around the Earth at about 27,500km/h; that is, 7.6 kilometres every second. Getting to space is easy. It’s staying there that’s difficult.
