How can anyone know whether Excel is giving the right answer to anything that is more complex that one can do in one’s own head? I mean if I had to check every formula and function, I don’t need Excel, but couldn’t there be a bug that someone missed that returns an erroneous result in specific situations? That could be potentially catastrophic depending on what the data and results were used for.
In: 0
You must not be old enough to remember this:
https://en.wikipedia.org/wiki/Pentium_FDIV_bug
You know excel gets the right answers all the time because it’s the product of 30 years of development, tens of thousands of programmers, hundreds of professional bug testers, and billions (literally) of users refining it into what it is today. The basic functions have been mastered 30 years ago by every spreadsheet software. Whenever a function haven’t worked properly, someone fixed it and everyone adopted the fix.
Essentially, software like excel or Google Sheets didn’t come out of nowhere. They’re built on a solid foundation, running on reliable machines, and they aren’t trying to reinvent the wheel.
You’re absolutely right, and it’s a very serious problem.
There are three main issues. First, the built in formula in Excel are not very accurate and several have well known errors, especially the statistical functions. These errors can’t be fixed without breaking old spreadsheets, so the errors have simply been left.
Quite a few papers have been written about this problem, for example:
https://www.researchgate.net/publication/47457454_On_the_Numerical_Accuracy_of_Spreadsheets
Another serious problem comes from Excel’s feature of automatically recognising the data type in a cell. If you import a CSV and a cell contains a string like “MARCH1”, Excel won’t leave it as a string, it’ll convert it to a date. This is a disaster if that string isn’t a date, but actually the name of a protein (“Membrane Associated Ring-CH-Type Finger 1” in this case).
https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates
Studies have found that 30% of published papers in proteomics contain errors caused by Excel misinterpreting protein names.
Finally, it can be extremely difficult to check the result of a complex spreadsheet. They can become like a large pile of spaghetti, with cells calculating values from other cells in huge undiscoverable chains. Editing one cell can break distant parts of a spreadsheet in mysterious and hard to predict ways.
The solution to all this is … don’t use spreadsheets for important or complex calculations. Other programming systems are available (R, Colab, etc.) which are more suitable for these tasks.
This is why modern maths curricula are trying to teach kids number sense, rather than rote calculation steps.
It means that when you put your formula into excel, you know how to sense check it.
For example, if I put in:
247 + 4674 * 3566
I know the answer is an odd number that is less than 20,000,000, and more than 14,000,000 Because the 247 is tiny compared to the final result, and I can round up 4674 and 3566 to 5000 and 4000 respectively. Or I can round them down to 4000 and 3500. I know the final answer is odd, because multiplying two even numbers gets an even number, and adding 247 to an even number gets an odd number.
This is step one of the “differences” method used in “new maths” when doing multiplication. The key is that here, I’m letting the computer do all the tricky stuff.
Computers are good at the tricky stuff, but they’re no good at telling if the data entered in is correct. So if they give a wrong answer, it’ll often be very wrong. So a wrong answer is often easy to spot. Humans are much better at knowing what the answer *should* look like, and then investigating if it looks wrong.
Depending on how much I care, I could try for a greater or lesser rigour in my cross-checking. It really depends on the report being run, and what kind of errors you expect to see in your data. A financial statement needs to zero out exactly, while an estimate might be more forgiving, especially if you know the tendency in your data is to overestimate time and costs.
I feel like this is the same as asking “how does my calculator do calculations right?”. If you input the formula right it will be interpreted and calculated right, like a calculator, that’s what it’s made to do.
People checked and we know Excel doesn’t always give the right answer.
For example Excel wrong believes that the year 1900 was a leap year.
So if you calculate how many days ago a date before march 1900 was you get the wrong answer. However this bug has been known about for so long that people have built their data and macros to work around it and Microsoft fixing it now would break all that stuff.
Also while not actually wrong, Excel has a nasty habit of assuming any given input that looks remotely like a date is meant to be a date.
If you put things in manually you notice that easily, but if you use excel to process data from another source automatically, It will end up corrupting your data.
There have actually been names for genese that scientist simply renamed rather than dealing with the common occurrence of Excel messing things up.
There have been lots of cases where people have been using Excel to process data and messed things up, but that is mostly user error than Excel itself.
For example during the height of the COVID pandemic the English health service processed COVID test results by importing CSV files into excel and hitting the limit of the maximum number of row excel can have, cutting of tens of thousands of test results from being processes.
Generally a big problem is that Excel is a spreadsheet software but people keep insisting on using it as a database or something it is even less suited for.
However by and large if used correctly and while staying aware of the programs limitations you get the right results. Enough people are using it that any deep bug would have been found by now.
Latest Answers