Validating Excel (XLS) formulas

Ed Panek

QA RA Small Med Dev Company
Leader
Super Moderator
#1
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?
 
Elsmar Forum Sponsor

William55401

Quite Involved in Discussions
#2
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
#3
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
#4
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.
 
#5
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

Tidge

Trusted Information Resource
#8
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.
 
Thread starter Similar threads Forum Replies Date
C Practical experience validating CNC Mills Medical Device and FDA Regulations and Standards News 11
B Validating calibration certificate as per API Q1 General Measurement Device and Calibration Topics 9
D Validating Operating system Qualification and Validation (including 21 CFR Part 11) 4
N Validating Software before getting approved as Class 2 device US Food and Drug Administration (FDA) 5
A Validating my own application developed with Agile Qualification and Validation (including 21 CFR Part 11) 5
T Risk analysis of QMS software - Validating software we use for QMS ISO 13485:2016 - Medical Device Quality Management Systems 7
H Validating Nondestructive leak testing machine Qualification and Validation (including 21 CFR Part 11) 3
S Validating a Bonding Process Qualification and Validation (including 21 CFR Part 11) 3
M FDA's expectation for validating OTS software updates Other US Medical Device Regulations 8
R Re-validating revised Medical Device and Aerospace Product COTS software ISO 13485:2016 - Medical Device Quality Management Systems 2
J Validating a stopped test due to never finding any defects Inspection, Prints (Drawings), Testing, Sampling and Related Topics 2
J Validating multiple injection molding machines efficiently ISO 13485:2016 - Medical Device Quality Management Systems 5
J Grouping Products and Testing Sterile and Biocompatibility - Validating Products Other Medical Device Regulations World-Wide 3
G Validating a Process Based on Lot Release Criteria Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Q Meeting the Requirement of Clause 7.5.2 on Validating Special Processes AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 5
M Validating a Product through Tool Control and Tool Inspections Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
G Validating a Sandblasting Process - Any pointers? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 10
G Testing and Validating a New Vertical Milling Machine AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 10
Q Validating Tape Measures used to Measure Product Conformity to Requirements ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
M Protocol for Validating Pull Tester and Force Gage Qualification and Validation (including 21 CFR Part 11) 9
T Validating Supplier Statement - Rusty shield that supplier denied responsibility Manufacturing and Related Processes 7
M Validating 3rd Party Software which Tracks Archived Samples Qualification and Validation (including 21 CFR Part 11) 2
Q Validating ESD (Electrostatic Discharge) Flooring ISO 13485:2016 - Medical Device Quality Management Systems 2
M Procedure for Validating Laser Marking & Electropolishing of Orthopedic Implants Other Medical Device and Orthopedic Related Topics 13
Q Validating the AOI process (Automated Optical Inspection) Reliability Analysis - Predictions, Testing and Standards 9
K Validating a gage/fixture in-house under TS 16949 - Need information General Measurement Device and Calibration Topics 2
C Validating Custom adaptations of Medical Devices ISO 13485:2016 - Medical Device Quality Management Systems 11
C Validating a Gage R&R spreadsheet - Defining Formulas in spreadsheet cells Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 6
G Software Validating Adobe Professional according to Part 11? Quality Assurance and Compliance Software Tools and Solutions 10
D Validating software in materials testing - Integrated system of individual components General Measurement Device and Calibration Topics 3
D Validating Multimeters (how to) General Measurement Device and Calibration Topics 8
Q Three year audit program template excel Internal Auditing 1
T Control Excel template Document Control Systems, Procedures, Forms and Templates 5
bryan willemot Looking for NADCAP audit Excel spreadsheets template for vendors, specifically heat treat (Vacuum Furnace) AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 10
A Kanban Excel template Lean in Manufacturing and Service Industries 1
I Excel based Gage R&R VS Minitab calculation Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 5
Ashland78 Need IATF 16949 ISO Gap Analysis Excel File Internal Auditing 3
M List of FAA Part 145 Repair Stations in MS Excel format? Federal Aviation Administration (FAA) Standards and Requirements 0
V Csv, excel format - audit trail file of HPLC system ( Empower, openlab, EZchrom or any other ) Qualification and Validation (including 21 CFR Part 11) 0
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 10
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23

Similar threads

Top Bottom