Using validated SAS JMP to validate Excel for Statistical Analysis?

[ilacatd]

My company has one user (me) with a version of SAS JMP statistical software. This software can be validated by running some NIST-traceable tests supplied by SAS.

I'd like to enable other engineers in the company to use Excel 2003 or 2007 for their statistical analyses, if possible, to avoid buying a separate statistical software package for each user. There are some pretty powerful add-in packages out there which are either free or cheap. I figure I might be able to use my validated JMP software for this purpose. All I need are some sample data sets, preferably NIST-traceable, which I can use to perform some simple to medium-complexity statistical tests which we may encounter while doing process validation and other manufacturing-related tasks like Gauge R&R, SPC, etc. We are a medical device company with a class II device, so I'd like it to hold up in an ISO or FDA audit.

These tests would include:

descriptive statistics (mean, std. dev, histograms, box plots, etc.)
tests for normality
t-tests
ANOVA
f-test
chi-square test
linear regression
random sampling generator
hypothesis tests
Wilcoxon signed rank and rank sum tests
etc.

If I run the tests on Excel and JMP and get the same result, that should probably suffice for my validation.

Does anyone have any thoughts about whether this is a good approach, and where I can find such sample data sets?

Thanks.

 

[George Weiss]

Re: validating use of Excel for statistics

This is part of a validation. Another is bad data. There would be some evaluation to extent of acceptable inputs in different fields. There would be affects and considerations like degrees of agreement. and controlling the agreement.
Having field or final value(s) of 1.000000 = 1.000000, and not 1.00
There is an ability to lock the excel books from change, which would be very important. You would need records of the validation, and records of rev.# of the excel.books. A guide to software validation is a good place to look. A checklist, procedure, SOP, JI, WI, and policy(s) in your QMS. Because you are medical, there is a required, not desired, validation fence to climb.
.
Validation procedures for medical testing @
https://www.biopharmacos.com.br/download/validation_procedures_methodology.pdf
Validation procedures for analytical measurements @
(broken link removed)
good practices @
(broken link removed)

Good Practices ​

There are many good software practices to be considered. The following are some that have been found to assist in managing validation.

• Treat each software product as a piece of calibration equipment that has to be "re-calibrated" each time it is changed or modified.
• Place software product masters in a read only directory.
• Network computers to access a shared program on a server.
• Lock spreadsheet cells that contain math.
• Password protect configuration files or setup screens.
• Backup, back up, and backup off site!

Plan for hardware/software disaster recovery.

 

[AnaMariaVR2]

Re: validating use of Excel for statistics

See this--> Re: Quality System Database Validation - FDA's General Principles of Software Validat

(broken link removed)

 

[Ninja]

Re: validating use of Excel for statistics

Lock spreadsheet cells that contain math.

Why did it take me so long to ask this here among the number crunching wizards?

1. I put math in a cell (XL)
2. I protect it (locked, not hidden...it's the result the operator is looking for)
3. It references unprotected cells (where my guys put the data)
4. If they DRAG a highlighted data cell, the formula in the "protected" cell changes ?!?.

How can I truly protect the cell, even from data drag? Anyone see this behavior too? Anyone have a solution?

 

[ilacatd]

Re: validating use of Excel for statistics

Lots of great tips - thanks!

 

[George Weiss]

Re: validating use of Excel for statistics

How can I truly protect the cell, even from data drag? Anyone see this behavior too? Anyone have a solution?

It would be good to have seperate pages for some math fields/activities.
An entry page, and an output page, and the math/formulas page
Excel has endless pages.
It is possible to make a fairly simple Excel book look fancy with alot of process descriptions, usage notes, validation options.
You can display function operations and create flow charts along side the process.

 

[Bev D]

one thing to consider is that the controls and validation testing potentially required for EXCEL by the FDA can be much more expensive than simply buying the JMP licenses. This is particularly true for ad hoc analysis (as opposed to an EXCEL template that manufacturign might use on a regular basis for some calculations that ahve set sampel sizes etc.)

with ad hoc analyses - you would have to build templates that are protected yet flexible enough to handle the various scenarios your engineers might encounter. in essence you will be recreating JMP only a much clunkier version.


I do find it incredible that some regulatory agencies require extensive validation of statistical software but not for the statisical methods and choices themselves.

 

[Ninja]

Re: validating use of Excel for statistics

It would be good to have seperate pages for some math fields/activities.
An entry page, and an output page, and the math/formulas page
Excel has endless pages.
It is possible to make a fairly simple Excel book look fancy with alot of process descriptions, usage notes, validation options.
You can display function operations and create flow charts along side the process.

All good suggestions, thanks. But now how do I protect it from dragged input cells?

All the fanciness is great, but if I tell the formula to reference $C$3, then protect the formula cell, and the operator drags the data from C3 to D8...my "protected" formula now references $D$8.

I can make it user friendly easy enough...but how do I protect it and make it reliable?

 

[Miner]

Make sure that you not only lock the formula cells, but also protect the worksheet in which the formula resides. I tested it on Excel 2003 and that prevented the formula from changing.

 

[Ninja]

Make sure that you not only lock the formula cells, but also protect the worksheet in which the formula resides. I tested it on Excel 2003 and that prevented the formula from changing.

I THINK I've done that. I am also using XL2003.
I've attached a sample file in an effort to explain what I'm seeing.

Thank you all for your feedback and suggestions.