I understand that the spreadsheets written by your company must be validated. Errors in the spreadsheet formulas will be the problem 99.99999% of the time, though I should probably have added more 9's at the end.
However your Team Leader (TL) is suggesting validating the underlying software and hardware system. Can your TL point to a documented personal experience, or a well publicized incident where the error was in Excel/Windows rather than the spreadsheet? Not hearsay, but a verifiable error? Or are they asking for this validation just to be a s&%t disturber? I know someone at work who would do that.
I trust Excel. There are 100's of millions of users. Trillions of spreadsheets. Operating on every conceivable version and combination of Excel and Windows. Why does the TL not trust Excel given this level of use worldwide? What sort of edge case do they believe can go unnoticed by so many users? If it's that much of an edge case, how likely are your company spreadsheets to use it?
On the other hand, if we decide the TL has a point, how deep do we need to go? (
Note : I'm intentionally being a s&%t disturber with this list.)
[ul]
[li]The difficulty, as software writers already know, is creating test problems that will find the errors built into the code. If you don't know what's broken how do you know what to test?[/li]
[li]Excel has hundreds of functions. I suspect some of them would be nearly impossible to verify. Goal Seek comes to mind as it's an algorithm rather than a function with a defined output. Do they each need to be validated? How? If only a subset is validated, how do you restrict employees from using the unverified ones?[/li]
[li]Do your spreadsheets use VBA? If you say no, are you sure? Some users are very clever. On the other hand I have no idea how to validate an entire programming language.[/li]
[li]Excel has numerous add-ons. I can't live without Solver. What if one of them, or a combination, causes an error in the verified functions? Thus you must re-run your verification suite with every possible combination of add-ons in effect.[/li]
[li]How frequently is Excel updated, and thus the validation suit must be rerun? If you've installed Office 2021 on each computer then any updates will come with Patch Tuesday (see below) and will be validated then. However if you subscribe to Office 365 (or whatever the name is currently) then you will have no idea when Microsoft makes an update on their server side code. How will you keep your validation up-to-date if you don't know when Excel changes?[/li]
[li]Of course you'll have to repeat these validations every month after Patch Tuesday from Microsoft. Perhaps your IT department already delays these patches to test the updates with other software, so they can add your validation suite to their job list.
<begin sarcasm> I'm sure they'll be thrilled. <end sarcasm>[/li]
[li]Excel doesn't actually do any of the calculations, so you need to re-run all of the above on every CPU model used in your company's computers. This is one case where there is a verified case to point at, with the Intel Pentium CPU's being recalled for a minor error in the FDIV command. (
Wikipedia) If this is the sort of issue the TL will claim they're looking for, then reading Wikipedia may make you realize how unlikely it is your validation suite would find it.[/li]
[li]The execution of the CPU chip can be altered by the BIOS/UEFI microcode. Thus you will need to re-run all of the above on every BIOS/UEFI version available for each CPU and motherboard combination the company owns, and re-run with each BIOS/UEFI update.[/li]
[li]Of course all of this would be checked against hand calculation or other known solutions. However this will likely use a calculator at some point. So now you need to validate each calculator used to make sure it doesn't have any built-in errors.
<begin sarcasm> Perhaps your TL already has a validation suite for the calculator they use! <end sarcasm> If the case is from a textbook, how do you validate the textbook is correct? We all know textbooks get errata as errors are discovered, so what about the undiscovered errors?[/li]
[/ul]
Am I being ridiculous. Of course I am. (
Perhaps I had too much spare time this weekend !!)
If we can't trust such a widely used and tested set of software (Excel and Windows) then where do we stop going down the validation rabbit hole?
Spend your time validating your spreadsheets, and trust everything else.
