Practicalities of performing Spreadsheet Validation

When we last left off at Spreadsheet Validation (part 1), we had covered the various standard and regulatory requirements that apply to spreadsheet validation and the types of spreadsheets that require validation.

Now, as promised, part 2.

Spreadsheet Validation under ISO 13485

Starting with determining criticality classification, we will also examine the relationship that exists between criticality classification and likelihood or occurrence of failure (Complexity Classification) and how this contributes to the risk assessment of the spreadsheet under validation to determine the level of validation required.

Spreadsheet Validation Part 2

Finally, the approach to spreadsheet validation will round out the topic and hopefully illuminate the process for you, the reader!

Criticality Classification

Criticality Classification starts with understanding the risk management process associated with the spreadsheet validation.  We do, after all, work in an industry where “proportionate to risk” is ingrained in our very fiber.

So much so, that ISO 13485 specifically mentions ISO 14971 in Section 3 – Terms and Definitions and again at Clause 7.1 where an organization is required to have one or more processes for risk management established, implemented and maintained as part of their Quality Management System (QMS).

The risk process should look loosely familiar to you all: Identification, Evaluation, Classification.

Once these three steps have been completed, its time to Mitigate.

Identification involves determining and documenting the hazards associated with the use of the spreadsheet, Evaluation is determining the severity of the identified hazard, Classification means categorizing the risk according to severity and finally Mitigation represents performing activities that reduce the severity or the likelihood of the risk.

Criticality Classification is broken into three level – Low, Medium and High.  Sounds easy, right.

But sounding easy is not exactly useful unless you understand what is involved in each of the levels.

High Criticality represents direct impact to patient safety, product quality, or the integrity of the associated data.

Medium Criticality corresponds to an indirect impact to patient safety, product quality, or the integrity of the associated data, and to absolutely nobody’s surprise.

Low Criticality signifies no impact to patient safety, product quality, or the integrity of the associated data.  Let’s break down this understanding further.

What type of information falls into each of these categories?

High Criticality includes:

• Laboratory Test Calculations
• Clinical Trail Data
• Product Quality Status Management
• Adverse Event Tracking
• Label Management

Medium Criticality includes:

• CAPA Tracking
• Calibration Tracking
• Audit Tracking
• Validation Tracking

Low Criticality includes:

• Out-of-office dates
• Cost reports
• Turnaround time reports

Likelihood or Occurrence of Failure (Complexity Classification)

Similar to Criticality Classification, Occurrence is also categorized into three categories: High Occurrence, Medium Occurrence and Low Occurrence, which represent various inputs to the spreadsheet that signify various levels of complexity classification.

For example, high occurrences are spreadsheets that include:

• Custom macros
• Linked spreadsheets
• Nested logic functions and
• Sophisticated look up functions.

An example of Medium occurrences are advanced statistical functions, and Low occurrences are standard functions and basic statistical calculations such as addition, average and standard deviation.

But how does all of this translate into a Spreadsheet Risk Assessment?

Examples of Spreadsheet Risk Assessment

Let’s take for example a spreadsheet that tracks a training attendance record. In this case the criticality is Medium, and the Occurrence is low as there is indirect impact on quality and/or safety and the spreadsheet will contain basic functionality.

What is an example of a high-risk spreadsheet?

Well, consider a spreadsheet that uses statistical analysis of clinical study data that includes complex macros.

This would warrant a combination of High Criticality and High occurrence as there is direct impact on patient safety and the spreadsheet contains a custom macro.

Can you have a High/Low combination?

Indeed, you can.  An organization may use a spreadsheet to perform simple arithmetic calculation for a linearity panel.  In this case the criticality is High, but the occurrence is low.

The rationale?

There is direct impact to product quality, however, the spreadsheet has only basic spreadsheet functionality.

It’s important to note that all scenarios will require a risk assessment.

So, we have looked at the risk assessment activities, what next?

Well, the next part is just as exciting as the last, let me assure you!

Spreadsheet Validation Approach

Against the backdrop of a full software validation, for example, a document management system, CAPA system or ERP system, it should be recognized that a spreadsheet validation is a significantly smaller scope, with a shorter timeline and is usually less complex.

When one considers what is actually involved: a single technology, with a single function, in a well understood technology with little or no programming involved, it should become clearer that spreadsheet validation is not the unsightly beast that exists in the shadows of the QMS, playing the villain of the piece!

Not unlike other validation activities, the spreadsheet validation will include the following:

1. Authorized Change Control
2. Validation Plan
3. User and Functional Requirements
4. Design Documentation
5. Pre-approvals
6. Operation Qualification
7. Performance Qualification
8. Verification summary and validation conclusion
9. Validation approval
10. Installation Qualification
11. Validation Closure

And don’t forget that all important Validation Report.

But riddle me this, what do you mean by User and Functional Requirements?

Here we go in 3,2,1 – let the fun begin………and louder for those in the back!

User and Functional Design

1. Combine user and functional requirements into a single set of requirements.

• • User requirements equal High Level
  • Functional requirements are the more granular details

2. Document the features required

• • Intended business use

3. Include the Key Areas

• • Calculations
  • Statistical Functions
  • Security

“Make this make sense”, I hear you exclaim and your wish or in this case exclamation is my command.

What does this actually look like?

Consider a spreadsheet that has been used for clinical data purposes where patients’ glucose levels are tracked.

User Requirement 1:

The spreadsheet shall be secure from modification

Functional Requirements:

1.1 All cells except for Patient ID, sample date, individual patient results shall be secure from modification by user.

1.2 The cells listed in requirement 1.1 shall be yellow

1.3 No other cell in the spreadsheet shall be yellow

1.4 There shall be a password used to lock the spreadsheet from modification by the user.

User Requirement 2:

The spreadsheet will record the Patient ID number

Functional Requirements:

2.1 The Patient ID will be recorded as a 4-digit number

User Requirement 3:

The spreadsheet shall record the date of analysis

Functional requirement:

3.1 Date of analysis will be recorded as DD/MMM/YYYY

User Requirement 4:

The spreadsheet shall calculate the average glucose (mg/ml) from 3 sample results

Functional Requirement:

4.1 The spreadsheet will require entry of all 3 samples of glucose results

4.2 The spreadsheet shall display an error if any result is less than 1mg/ml

4.3 The spreadsheet shall display an error if any result is more than 10mg/ml

User Requirement 5:

The spreadsheet shall record the laboratory analyst.

Functional Requirement:

5.1 Lab analysts will be selected from a drop-down menu.

Hopefully, these few examples have shed some light on what user requirements and functional requirements will look like when performing a spreadsheet validation.

Design Documentation

Phew, we are nearly there, stick with me! The design of the spreadsheet needs to be documented as part of the validation plan.

Some examples of spreadsheet design incudes:

• Password protection on non-data entry cells
• Standard number of decimal points to use
• Standard for rounding rules
• Standard date formats
• Standard colours for data entry cells and calculated cells
• Standard file naming convention
• Standard error message formats

What series of steps happens next?

Lock in and I will make this quick and painless.

1. Document the Operational and Performance Qualification Test Plan
2. Document the Installation Qualification Test Plan
3. Obtain pre-approvals required up to this point
4. Run the OQ and PQ
5. Report and record any Issues that may have occurred during the OQ and PQ
6. Issue the summary report
7. Obtain the validation approvals
8. Execute the pre-approved installation qualification
9. Obtain signature to close validation project

And there you have it folks.  That concludes all the information I had locked away related to spreadsheet validation.

I hope you had as much fun reading this as I had in putting it together.

Now, go forth and validate those spreadsheets to ensure you meet all your regulatory obligations and conformity to those software validation requirements of ISO 13485.