mads
|
| posted on 27/4/10 at 08:07 PM |
|
|
how to validate formulae in MS Excel?
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?
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
JohnN
|
| posted on 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
|
| posted on 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]
Build Blog
Build Photo Album
|
|
|
Humbug
|
| posted on 27/4/10 at 09:34 PM |
|
|
If it's vital for safety purposes, don't use an Excel spreadsheet...
|
|
|
liam.mccaffrey
|
| posted on 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...
Build Blog
Build Photo Album
|
|
|
mads
|
| posted on 27/4/10 at 09:54 PM |
|
|
suggestions on alternatives? something preferably free..... I work in the NHS.
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
stevebubs
|
| posted on 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
|
| posted on 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
|
| posted on 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
|
| posted on 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'.
--
Never argue with an idiot. They drag you down to their level, then beat you with experience.
Anonymous
|
|
|
scudderfish
|
| posted on 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
|
| posted on 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]
--
Never argue with an idiot. They drag you down to their level, then beat you with experience.
Anonymous
|
|
|
