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
[removed]
Computers aren’t programmed with all the answers to functions they literally calculate the answers from the functions like inputting information into a calculator, so if the numbers and formula are entered correctly the answers will be to.
When I do a very complex spreadsheet, I include a “sanity check”. I take the answer that Excel gave me, and use it to calculate the same problem in a different way. Unless Excel were giving me very well engineered mistakes, it should show if there was an error. Every time I’ve done this, the only mistakes I’ve found were mine, not Excel’s.
A slightly amusing anecdote: Several years ago I was put on some new medication. I was doing a spreadsheet on some engineering calculations, and I did a sanity check. But the answers didn’t line up at all. In fact, they were off by a factor of 10^36 . That’s 1,000,000,000,000,000,000,000,000,000,000,000,000.
I spent most of a day looking for my mistake, but couldn’t find it. Then I had a Eureka! moment. I realized that *my new medication was making me stupid*. (My neurologist had warned me this was possible.) I quit taking the new drug and the next day I quickly found my error.
Let’s say I’m building a spreadsheet to look up some data in another sheet. Something I’ve done frequently and can often generate long complex formulas in a cell.
When typing that formula out, I don’t just write the entire thing. Often I will write one segment and check what I expect the answer to be. Add a bit more code and re-check. Repeat until you have the final code.
And if you’ve written it well, using the correct notation, now I can just drag that long complex formula where I need it and be guaranteed it will work as I expect it to. But instead of writing the same formula 50 times and possibly messing up one instance, I’ve done a bit of pre-work to ensure everything functions right. Copy and paste 50 times or drag to fill and I’m done.
There is no magic. It either works or it doesn’t. And 99% of the time if it doesn’t its something the user typing the formulas or entering the data did wrong.
Because pretty much every math problem can also be solved by hand. There are other ways to calculate the same formula and cross-check results to see that they’re consistent.
And while they’re developing the software they test those rigorously. Developers generally use testing suites to feed a bunch of inputs into a function, compare the outputs to the expected answers, and make sure they match up. Because this can be automated, it’s possible to test a function against a very large number of possible test cases. Every time someone makes a change to the function they run the tests again, and if they’ve introduced an error some of the tests will fail.
In the case of Excel, it’s also very popular software used by millions of people every day. If a bug does get through testing (and they do) it’ll quickly be found by someone using the software, and that person will report it, and Microsoft will (usually) fix it.
The only way to know would be if there was a massive financial crash where Wall Street committed some massive fraud.
As an engineer I have tested excel functions with MathCAD or by hand hundreds of times. We don’t leave important calculations unchecked.
The answer you are looking for is something called Unit Testing. The software engineers come up with a series of tests for the various calculations functions, and these tests will have known answers. If they input data, and the numbers don’t match the expected results, they know it is broken.
Math specifically has something called proofs, which is basically coming up with an alternate/opposite formula that proves the first one is correct, by working your way back to the original equation.
Excel has been used worldwide for performing calculations since 1985. Thats many millions of users, in universities, accountancy firms and government department, performing and checking its functions over 37 years. I call that rigorous testing.
I check my work. I’ve yet to see excel make a mistake. When excel has given me the “wrong” answer it’s because I mistakenly asked it to answer the wrong question.
Latest Answers