Validating Excel (XLS) formulas

Ed Panek

QA RA Small Med Dev Company
Leader
Super Moderator
Our auditor mentioned the need to validate MS XLS formulas for anything more than basic functions (+-/*)


I was thinking we could have a short validation report with values at various potential extreme use cases and breaking the formula into sections and verifying each output of the formula as we went. Would that work?
 

William55401

Quite Involved in Discussions
You are on the right track. Also, if the sheet is for general use (a template that does batch release calculations for example), the formula shall be locked down. The user only inputs the data and your validated (and uneditable) xls gives repeatable output. As orgs continue on their compliance journey, they maintain inventories of these sorts of sheets and catch up on the validation. As new ones are developed, validation is expected at time of template approval. HTH.
 

Ed Panek

QA RA Small Med Dev Company
Leader
Super Moderator
As a f/u question,
We have an ISO13485 auditor that is asking that we validate MS Excel. To be clear, they are not suggesting that we validate how we are using excel in production, but that Excel is actually adding, subtracting, etc. correctly.
Is this a reasonable position and if not how would we respond?
 

silentmonkey

Involved In Discussions
It is indeed a reasonable position. Your organisation is relying on software to make decisions for you that impact the quality of the product. If you blindly trust that the software has been set up correctly i.e. formulae are always correct then you run the risk of the software making an incorrect decision every time.

ISO 13485 requires you to validate software used for your QMS processes.

The approach I've taken to validate Excel spreadsheets is to generate a software description which basically summarises what we intend to use the spreadsheet for and how it works; breaking down all of its worksheets, functions and formulae including the expected inputs and outputs for each function.

A validation protocol is then generated to test all functions and formulae with made up inputs and outputs. Your test scenarios should demonstrate the software is able to differentiate a pass/fail result at the pass/fail threshold. For example:

This function is supposed to tell the user if the measurement is a pass or fail based on the specification defined by the user.

Scenario 1:
User defines specification = 10 - 15
Test input1 used = 0
Test input2 used = 10
Expected output1 = FAIL
Expected output2 = PASS
Actual Output1 produced by software = FAIL
Actual Ouput2 produced by software = PASS

Scenario 2:
User defines specification = 10 - 15
Test input1 used = 9
Test input2 used = 10
Expected output1 = FAIL
Expected output2 = PASS
Actual Output produced by software = FAIL
Actual Output produced by software = PASS

Both Scenario 1 and Scenario 2 produce the expected output correctly but only Scenario 2 actually demonstrates the software can differentiate the pass/fail result correctly.

If you spreadsheet has 1,000 rows of the same formula then it would be sufficient to test only the first 3 and last 3 rows and then reasonably assume everything else in between is correct.

Good luck mate. Excel spreadsheet validation is a b**** and creates a lot of additional work when we just want to do less work by using Excel software in the first place.
 

Ajit Basrur

Leader
Admin
Our auditor mentioned the need to validate MS XLS formulas for anything more than basic functions (+-/*)

I was thinking we could have a short validation report with values at various potential extreme use cases and breaking the formula into sections and verifying each output of the formula as we went. Would that work?

Yes, your approach is correct with respect to validation but there are additional controls required. Refer to section 5 of the attached document. https://www.fda.gov/media/73535/download
 

Attachments

  • III-04 Basic Statistics and Data Presentation (v02).pdf
    515.9 KB · Views: 156

Tidge

Trusted Information Resource
We have an ISO13485 auditor that is asking that we validate MS Excel. To be clear, they are not suggesting that we validate how we are using excel in production, but that Excel is actually adding, subtracting, etc. correctly.
Is this a reasonable position and if not how would we respond?

This sort of 'auditor request' used to be very common, and such questions often degenerated into forcing medical device manufacturers to essentially become the quality branch of commercial software suppliers. This has had a negative impact on medical device manufacturers in the US from adopting modern technologies and contributed to an industry-wide effort to bring some sanity to the question of "Computer Software Assurance (CSA)".

From the draft (which I am not sure is being circulated publicly, but there should be lots of web pages about this topic):

Computer Software Assurance: A set of activities or actions that are performed to give confidence that the software and the automation function as intended and meet the organization’s needs. This may include any combination of the following activities; analysis performed to select the solution, vendor history, vendor data, market performance data, installation activities, factory acceptance testing, or risk-based testing as appropriate.

(me again) To be clear: a medical device manufacturer is intended to understand/state the intended use of commercial software packages, apply a risk analysis to understand the potential impacts of the system and achieve confidence that the software system meets those needs. I am reasonably certain that an organization can rely on vendor history to believe that Microsoft Excel can add numbers. Custom formulae should be tested appropriate to the level of risk.
 
Top Bottom