Board logo

how to validate formulae in MS Excel?
mads - 27/4/10 at 08:07 PM

hey all,
hoping someone can point me in the right direction...

i want to validate the formulaes used in a spreadsheet designed by someone at work. i've never done this before as most of my formulae are for personal use and not that complex. however this one is vital for safety purposes and i want to make sure the equations work EVERY time with whatever values are entered.

obviously i could simply input a range of numbers in each cell and see what it does but this would take me months to do. is there software or some other method i can use to test the spreadsheet?


JohnN - 27/4/10 at 08:22 PM

In short, the only way you can check a spreadsheet that produces results and not error messages is to test various input values at the extremes of the ranges you expect and check that the results are correct


liam.mccaffrey - 27/4/10 at 09:31 PM

@ John,depending on what the particular function being tested is using range limits might not prove the formular is good, especially if you have an oscillating or asymptotic function.

You could write a macro to run through a large number of inputs and record the results. you could then do some analysis on your output data set.

If this really is safety critical though might I suggest you get it independantly evaluated by some software development people.

[Edited on 27/4/10 by liam.mccaffrey]


Humbug - 27/4/10 at 09:34 PM

If it's vital for safety purposes, don't use an Excel spreadsheet...


liam.mccaffrey - 27/4/10 at 09:44 PM

Should have said that also really

quote:
Originally posted by Humbug
If it's vital for safety purposes, don't use an Excel spreadsheet...


mads - 27/4/10 at 09:54 PM

suggestions on alternatives? something preferably free..... I work in the NHS.


stevebubs - 27/4/10 at 11:31 PM

how complex is it?

What are you trying to guard against? If simple user error then putting in some programmatical verification of the inputs is probably the way forward.


hughpinder - 28/4/10 at 07:31 AM

If its safety critical you must test every possibility - e.g not just numeric data, but letters, control characters, random strings etc in cells that should be numeric. Remember, you will be liable if you say you have tested it and its ok its use. If you format the cells as 'numeric' etc you limit the possibilities a bit. You can also use the 'data/data validation' function to allow only the values in a drop down list, or only numeric data in a certain range, or only whole number in a range etc, to be selected.
Proper validation is very time consuming and must be fully documented, or it's worthless if there is ever an incident. In this case you should show due dilligence has been applied - if you have tested 2000 sets of data and missed one you will be ok, but remember the courts will appoint an expert to look at your test plan. Ensure you make a copy of the spreadheet that is used for the validation tests and keep it forever, as anyone can change your sheet later - All changes will of course be fully documented, the test plan and design spec updated. It is best if these are version controlled as well as the spreadsheet.
This is the reason people think they can knock out a spreadsheet to do something in a day, rather than pay £5000 to a consultant to produce proper software - If you only perform cursory testing and no formal design stating the purpose of the sheet and valid data ranges for all inputs, you have only done 10% of the job! Where I work (coding for pharmaceutical/oil and gas/general chemical control systems etc), we would allow 10% of the time for the formal design and spec, 10% to code it and informally test it (the stage it sounds like you're at), 70% produce formal test plan and execute it and 10% for user documentation/training/integration testing/distribution and anything else. If it was safety critical, at least 2 people would be involved in the design and also the design/checking of the test plan, making these aspects longer.

If you have been asked to validate someone elses work and don't have a formal spec or test plan, find out how long it took to produce and allow 10 times that time for your bit. If you are not familiar with formal testing procedures double that.

Regards
Hugh


mads - 28/4/10 at 12:36 PM

thanks for all the replies guys.

the spreadsheet is to be used to calculate chemotherapy doses for patients - hence the safety critical aspect. Though I think in the end, the pharmacists will just double check all the calculations anyway.

Hugh - thanks for the comprehensive reply. I thought something like that might be the case.


iank - 28/4/10 at 01:46 PM

quote:
Originally posted by mads
thanks for all the replies guys.

the spreadsheet is to be used to calculate chemotherapy doses for patients - hence the safety critical aspect. Though I think in the end, the pharmacists will just double check all the calculations anyway.

Hugh - thanks for the comprehensive reply. I thought something like that might be the case.


They will at the start, but if it produces the correct answer for the first dozen or so patients they won't bother any more - basic human nature.

That's the problem with this kind of thing as it's the edge cases and incorrect data entry (inputting patient weight in grams or lbs instead of kg for example) that will cause problems.

If you're a contractor make sure you have the correct liability insurance for this kind of work, if not then at the least make sure it's rigorously checked by someone else competent.

One important thing, make sure all the cells are locked - and password protected (except those being used for data entry) as it's too easy in excel for a user to accidentally overwrite some critical value/formula if they aren't or bugger around 'making it better'.


scudderfish - 28/4/10 at 03:46 PM

Please please please read this

http://en.wikipedia.org/wiki/Therac-25

Then think very hard if you want to trust a spreadsheet knocked up by some guy


iank - 28/4/10 at 03:50 PM

quote:
Originally posted by scudderfish
Please please please read this

http://en.wikipedia.org/wiki/Therac-25

Then think very hard if you want to trust a spreadsheet knocked up by some guy


If you read the root causes then most of those are what we are suggesting be done to test the system. The others aren't so relevant as it's not an automated chemo delivery system so the resulting value gets reviewed by a trained pharmacist.

p.s. Had Chemo, wouldn't have wanted an over/underdose The system when I had it was a nurse wrote my weight on a scrap of paper and disappeared off to get it dispensed. Presumably a human did the calc manually - not really any safer.


[Edited on 28/4/10 by iank]