Validation and Protection of Calculation formulas on MS/Excel
The process of validation of the formulas on MS / Excel® is manually performed and is often expensive, especially when the formulas are many and complex.
The verification of the correctness of the results is only visual (comparison with generated documents) and unguided (except in voluntary implementation of procedures / SOP).
The production of the necessary documentation is challenging, the monitoring of the whole process is complicated.
Revalidation, particularly in the absence of audit trail of the process, is required and often with close frequency.
The validation of Formulas on MS / Excel® it is greatly facilitated by a procedural approach and the use of software tools in accordance with the current standards: GxP, EU Annex 11, FDA 21 CFR Part 11, ISO 17025, ISO 13485.
The goal is to have validated Spreadsheets and maintained in a safe environment, using appropriate software support for the automation of the Verification / Qualification process, Documentation and Protection.
In this way you can obtained an obvious saving of time and costs.
The need to validate the formulas on MS / Excel has long been mainly highlighted and addressed in the pharmaceutical sector, but also the ISO / IEC 17025 imposes clauses of (4.3, 4.12 5.4, 5.5) for the “data security”, their “protection” and the “Validation of logical calculation”. In particular, paragraph 5.4.7 (Control Data) specifies that “… the software developed by the user must be documented in sufficient detail and suitably validated”.
This requirement, even according to the ACCREDIA guidelines (RT-08), also applies to the development on Spreadsheet (eg. MS / Excel formulas).
Polisystem Informatica, has created a new “Additional Component” of MS / Excel that governs the process of Validation and Security Wizard of formulas on Excel, recording, in parallel, on a dedicated DataBase to support, useful information for the management of Access and Duties, the correct storage of Folders and the accompanying documentation, the Log of the revised Folders and its Audit Trail of detail.
The level of validation to be set, depends on the level of risk perceived and established by any inconvenience that may occur in the process due to errors (fundamental or marginal) of the formulas; The Validation Plan will then set following these criteria.
The availability of standard operating procedures for the use and management of the sheets could be an essential prerequisite for the proper validation.
When a folder with Excel formulas can be considered a “quality records” must have :
• Definition of User Requirements
• Used Validation Protocol and the related acceptance criteria (Validation Plan)
• Test cases
• A Validation Report
• An “Audit Trail” for both Formulas Model, both for data input Sheets
The definition of the logic of such Workbooks, is generally entrusted only to authorized users (eg. Heads of Service), even though the use of such logic should be allowed to a wider number of users (Lab Operators) sharing in the network Folder.
These considerations apply to any laboratory that uses MS/Excel for calculations in support of analytical or company that uses MS / Excel as part of a process step that affects the quality of a product.
Integrated use of Logic calculation on MS/Excel for Results/Uncertainty on LIMS :
Some LIMS (always LIMS “Analisi” (put link) of Polisystem Informatica), has an integrated platform that organizes, performs, documents and maintains the traceability of the calculations.
These calculations (for both results of a test following a Operating Procedure, both for the relative determination of Uncertainty), are carried out automatically, after the input of only the values of variables (freely definable).
The ISO / IEC 17025 requires the clauses (4.3, 4.12, 5.4, 5.5) for the “data security”, their “protection” and for the “Validation logical calculation”.
In particular, paragraph 5.4.7 (Control Data) specifies that “… the software developed by the user must be documented in sufficient detail and suitably validated”.
This requirement, according to the interpretation ACCREDIA, also applies to the development on Spreadsheet (eg. Formulas MS/Excel macros or Visual Basic).
E’ noto che nei Laboratori Prove MS/Excel è di gran lunga lo spreadsheet più utilizzato, il più delle volte proprio per automatizzare calcoli a supporto dell’applicazione di Metodi di prova.
Many LIMS, however, are designed to set the logic to support the application of the calculation methods only proposing a syntax definition of the formulas that is almost always restricted and devoid of powerful mathematical functions, statistical and logical that Excel has.
Moreover there is no comparison between the extreme ease in setting of the offered formulas from Excel, with respect to the difficulty that certainly can encounter in transcribing a formula, sometimes also complex, according to the “poor” implementable syntax in a field of a dataBase.
Finally, how to validate the logic set according to this syntax, and syntax same ?
While the validation of the syntax should be provided by the Software House of LIMS (service not so common), the validation of the logic remains the responsibility of the Laboratory and often is performed manually or with “handmade” processes, or, worse, the laboratory chooses to use of computer systems not integrated with LIMS transcribing in the same LIMS directly the calculated results, with traceability.
It is precisely for these reasons that the LIMS “Analisi” (put link) uses as the calculation “engine” MS / Excel:
“ValProt” is designed to automate the activities of validation and protection of the formulas created on MS / Excel folders from all workshops, also providing specific automations for the LIMS Users “Analisi”.
“ValProt” is simply an “Add-In” MS / Excel, using the full functionality of Excel and enriching them with the storage of additional information about a support DataBase, the user takes his hand and guides him in the activity Validation and protection of the logical calculation.
