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?

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

@ 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]

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

Should have said that also really

quote:

---

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

---

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

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.

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

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.

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.

---

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

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

---