Software validation 1

[K.P.M.]

Hello Everyone,

Recently I came know that during ASME audit, some Team leader has been started to ask software validation of Microsoft Excel too. Also asking for validation must be done for all possible configurations.like PC operating system, excel version..

Can anyone help us regarding this?

Because I believe that Microsoft Excel itself doesn't calculate vessel thickness or relevant it. We have to write all formula just like calculations done on a paper.


Regards,
KPM

 

[geesaman.d]

Oof, that could be an ugly request considering how frequently windows and excel get updated, usually automatically. Can it be done annually or quarterly, rather than trying to react to every single update?

What I have seen done is have a reasonable range of test case calculations with the longhand calculation results to confirm their accuracy. Then when Excel and/or Windows change, you re-run those test cases and confirm they still output correct answers.

It's hardly the ideal way to catch exceptional issues but it will certainly catch a major shift that affects all calculations.

David

 

[Geoff13]

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.

 

[Cobra17]

I'm guessing what they want for the audit is something that validates the sheet. for example, if it was a VIII-1 audit, and you have a shell spreadsheet, do the shell sample from PTB-4.

 

[KevinNZ]

Where would this stop? ..all possible configurations. like PC operating system, excel version.. What about CUP model and RAM?

 

[DKTim]

Perhaps there's some miscommunication.

Are you sure they want to validate excel at a software, and not just the custom excel sheet that you use to perform analysis?

If it's the latter, that's easy. There's lots of resources on how to do that. Generally it requires that you show your work & references right in the calculation output and most importantly you should have a verification input/output. Where you recalculate the same parameters and get the same results after ever time you update the excel calculator.

If it's the former, and they want you to validate excel at a software level (i.e. prove that excel will output the same result on every OS/driver/hardware), that is an impossible task.

Because in order to do so, you'd have to run your excel on SIL rated hardware. Otherwise, you're at risk of a random photon tripping a 0 into a 1. No, ECC registered RAM doesn't qualify.

If it is indeed true that your Team Leader phrased it exactly as you wrote it, that leads me to believe that individual is not qualified to give such orders.

The logic is simple, what if you did indeed provide a "software validation" of excel?

Is your team leader competent enough to verify your validation? I don't think they are, and it's not a stretch of logic to circle back and state that if they can't verify the validation.. then they shouldn't be asking for it (at least not in the specifics).

Would anyone in this forum be qualified enough to verify someone else's "validation of excel in performing calculations".

People that provide unreasonable requests in the name of safety should not be in a position overseeing safe design. Because safe designs should be achievable and easily auditable.

 

[mk3223]

It seems that the Auditor asks if the "validation software' is developed by a third party company with a history of good reputation and service references. If it's prepared by your own, if it's been reviewed and confirmed by your supervisor or a qualified Professional Engineer.