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’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.
Latest Answers