Spreadsheets in Clinical Medicine

Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved Spreadsheets in Clinical Medicine - A Public Health Warning Grenville J. Croll Grenville@spreadsheetrisks.co m Director, Spreadsheet Engineerin g Ltd Raymond J Butler Ray.butler@virgin.net EuSpRIG – European Spreadsheet Risks Interest Group ABSTRACT There is overwhelming evidence that the continued and widespread use of untested spreadsheets in business gives ri se to regular, sign ificant and unexpected financial losses. Whilst this is worrying, it is perhaps a relatively minor concern compared with the risks arising from the use of poorly constructed and/or untested spreadsheets in medicine, a pr actice that is alre ady occurring. This article is intended as a warning that the use of poorly constr ucted and/or untested spreadsheets in clinical medicine cannot be tolerated. It supports this warning by reporting on potentially se rious weaknesses found while testing a limited number of publicly available clinical spreadsheets . 1 INTRODUCTION The impact and to some extent the in cide nce of errors in fi nancial spreadsheet models have been widely documented [Panko, 2000, 2006] [Butler, 2000] [Croll, 2005] [EuSpRIG, 2006]. We are aware of no similar studie s in m edical domains. Because spreadsheet users are human, spread sheets are error prone. This has been shown by repeated studies of various t ypes over several decades. Experim ent has shown time and again that th e only method for reducing e rrors in spreadsheets is the use of multiple people to test a sp readsheet, with m ultiple test passes. Even then, errors will remain. Recent quantitative evidence shows that spreadsh eet users are also overconfident [Panko, 2003]. Since they rarely test their spreadsheets, they don’t find any errors, increasing their confidence in the way they use spreadsheets. If they do find an error or two, then that also increases thei r confidence, as they are not m otivated to find further or all errors. Finally, there is some evidence [Banks A., Monday D., 2002] that the differing ways that spreadsheet users in terpret the real world and model it in a spreadsheet gives rise to differing numerical evaluations of the same situation. The use of software in medicine is no t new of course [Johnson, K.A, Svirbely, J.R., Sriram, et al], (2002)], what is new we believe are the is sues related to the widespread use of end-user software, sp ecifically spreadsheets, in medicine. We believe these issues to be of crucial importance when consid ering the continued Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved use of spreadsheets to support potentially life or death de cisions relating to patient care. Medical error accounts for an estimated 98,000 deaths annually in the U SA, 30,000 in the UK, and is the seventh larg est cause of death [Kohn, L.T., Corrigan, J. & Donaldson, M. (Ed) (2000)]. 2 SPREADSHEETS IN CLINICAL MEDI CINE A search of the PubMed abstracts data base [PubMed, 2006] revealed over eight hundred references to the use of spreadsheet s, many of which were clearly used in clinical applications [Maceneaney PM., Malone D.E., 2000] [Linthout N., et al, 2004] [Cederbaum M., Kuten A. 1999]. An in ternet (Google) survey of the word “spreadsheet” followed by a medical ke yword such as “cardiovascular”, “pediatrics”, “anaesthesiology”, “oncology” etc quickly identified a number of circumstances where publicly available sp readsh eets are being used or could be used in clinical situations. We assume that the spreadsheets identif ied in this m anner represent a small fraction of the spreadsheets actually in use in m edicine. Some evidence for this is found in reports [Johnson et al, 2002] that medal.org, a web site containing some thousands of downloadable medical spr eadsheets, received in excess of 1,800 unique visitors per day in 2002. 2.1 Paediatrics A recent article [Narchi, H. 2004a ] is abstracted as follows: “In a series of three articles, we describe the step-by-step design and use of a spreadsheet to analyze th e results of a diagnostic test or a therapy in the literature. This first ar ticle describes the require d skills, which are minimal. The hardware and software requirements are modest, widely available and rela tively cheap. In addition to the elimination of the potential risk of calculation errors, time and effort is saved by the physician. The use of such a spreadsheet will further consolidate the concept of evidence- based medicine by readers of the medical literature and will help to further imp rove the quality of care ” (Authors’ emphasis) The last sentence in this abst ract indicates that the spread sheets referred to are to be intended for use in patient care. That the spreadsheets are intended for modification is made clear in the article by: “The flexibility of the design also allo ws customization by each user, such as …..adding other statistical formul as for further analysis of data….” Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved Modification of spreadsheets is a well known source of e rror. This arises through a variety of mechanisms, including the unintentional overwriting of formulas and the accidental entry of incorrect data and formulas. A second article [Narchi, H., 2004b] describes a spreadsheet which implements Bayes theorem to compute the post-test p r obability of diagnosing a disease based on the prevalence of that disease in a clin ician ’s practice. Table 1 in the article lists 23 cell addresses and th eir contents, being descriptive labels and a further 20 cell addresses and their c ontents, being formulas. There are material risks of • typographic error in such lists of formulas, • error in the entry of the formulas into an actual spreadsheet. The spreadsheet is of a similar size b ut substantially greate r complexity than those used in studies in other domains used to determine the likelihood of m aking a mistake [Panko, R., 2003]. The following sentences gi ve rise to concern: “Table 1 describes the data to be en tered in particular cells…..Save your work. The spreadsheet is now ready for use” Critical paragraphs about how the spreadsheet should be checked, tested, brought into use and maintained over its lifeti m e and through the many modifications it will endure are missing from this documentation. 2.2 Anaesthesia A web site designed to inform Nurse Anes thetists [Evans, T.J., 2006] contains the following text: “…. here are two guides to help y ou in your anesthesia practice. First is a Microsoft Excel spreadsheet titled ‘ Pediatric Anesthesia Worksheet’. Use it to calculate medica tions and other parameters for pediatric patients”. The spreadsheet pediatriccalV2.xls referre d to above contains a data entry box where the user can enter their paediatric patient’s Age, W eight, Height, Hours NPO, Respiratory Rate, Hematocrit a nd Minimum Allowable Hematocrit. The spreadsheet then calculates using a series of spreadsheet logi c cells the doses of drugs for pre-operative, peri-operative a nd post-operative care including narcotics, analgesics, antibiotics, muscle r e laxants and emergency m edications. The spreadsheet is protected, that is a passw ord is ostensibly required to modify it. However, by simply cutting and pasti ng the whole spreadsheet to another worksheet, the spreadsheet is fully acce ssible in its entirety a nd therefore Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved potentially at risk of untested modifica tion. The part of the spreadsheet that calculates pre-operativ e doses is as follows: I J K L 5 Pre-operative mg 6 Atropine IM 0.02 mg/kg 0.10 7 Atropine IV 0.01 mg/kg 0.10 8 Cimetidine PO/Slow IV 7.5 mg/kg 0.0 9 Clonidine PO 4 mcg/kg 0.000 10 Glycopyrrolate IV/IM 0.01 mg/kg 0.00 11 Ketamine Stun IM 5 mg/kg 0.0 12 Metoclopramide IV 0.1 mg/kg 0.0 13 Midazolam IV 0.05 mg/kg 0.00 14 Midazolam PO 0.5 mg/kg 0.0 15 Midazolam IM 0.08 mg/kg 0.00 16 Midazolam Nasal 0.3 mg/kg 0.0 17 Morphine IM 0.1 mg/kg 0.0 18 Ranitidine IV (up to 50 mg) 1 mg/kg 0.0 The formula for calculating e.g. atropi ne dose (L7) bears examination: =IF(E19*0.02>0.6,0.6,IF(E19*0.02<0.1,0.1,E19*0.02)) E19 contains the weight in Kilograms. Pe rhaps if E19 had been defined as a name e.g. “Bodyweight”, the formula would be easie r to read and check. Note the use of embedded constants – they have some clinic al meaning and bear removal to a data area where they might be explained. Conve rsely, and perversely, the constants in the labels (column K) are repeated and not used in column L, which is where the “0.02” in the atropine formula comes fr om. A complicated undocum ented formula for Body Surface Area is unused. The spreadsheet authors provide the following disclaimers: “The authors have exerted every effort to ensure that th e drug dosages set forth are in accordance with current recommendations at the time of publication. The user is urged to check the drug's package insert for any changes in indica tions and dosages as well as for warnings and precautions. The responsibility is ultimately tha t of the prescribing clinician”. We would regard this spreadsheet applica tion as being safety critical and would suggest that there should be independent evidence of the testing to which this spreadsheet has been subjected. The docum entation for this s preadsheet comprises one file containing the following information: DIRECTIONS FOR USE OF PEDCAL Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved 1. OPEN FILE. 2. SELECT READ ONLY. 3. ENTER PATIENT’S NAME IN TOP LEFT HAND CORNER AND ENTER DATA IN “DATA ENTRY BOX.” COMPLETE AS MUCH INFORMATION AS POSSIBLE. INFORMATION WILL HIGHLIGHT RED WHEN PROPERLY ENTERED. ENTER AGE IN ONE FIELD ONLY, EITHER MONTHS OR YEARS. 4. PRINT. 5. GO TO FILE/EXIT. IN SAVE CHANGES DIALOG BOX CLICK “NO.” We would regard this level of documenta tion as being inade quate in a saf ety critical software application. 3 SOME INITIAL TESTING We took the opportunity to te st a small number of spr eadsheets using the “SpACE methodology” [HMRC, 2006]. One was the paediatric anaesthesia model pediatriccalV2.xls introduced above. The other two were taken from several thousand posted on www.medal.org , the website of the Institute for Algorithmic Medicine. One of these is intended to asse ss the risk of cardiac problems arising in patients undergoing non-cardiac surgery [C ardiac, 2006], and the other to support a decision to assess an elderly patient for masked depression [Svirbely, 2006]. Material errors in any of these could have catastr ophic consequences for the patients concerned. Our testing was confined to spreadsheet use and mechanics. Without the required domain knowledge it is not possible to comm ent on the appropriateness / completeness of data inputs, the appropria teness of their use, dosage and other interpretive issues. The spreadshe et test ing we performed produced over 15 pages of detailed notes, which we omit f or clarity. 3.1 Findings Our knowledge of the clinical domain was not sufficient to determine whether any material clinical errors were present. However, we noted a very alarm ing incidence of poor / high risk practice in the spreadsheet modelling p erformed. 3.1.1. Common to all three models was extensive use of: • Constants for drug dosage, risk f actor scores, and predicted body measurements embedded in form ulas • Complex nested IF formulas (that shown above was by no means the most complex) some with m ultiple AND a nd OR conditions. Many of these also had embedded constants • Protection / locked / unlocked cells. Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved • Formulas with no dependents – Some were completeness checks, but some appeared to be potentia lly important calculations. 3.1.2. Lacking in all three were: • Documentation of the spreadsheets’ wo rkings, and instructions for their use was not present in the relevant Excel files. Limited instructions wer e given in the source web sites, but this was not linked to or embedded in the application. • The use of data validation to ensure that accurate and appropriate data was input to the models. 3.1.3 Paediatric Anaesthesia model only • Most dosage information was given in milligram s. A very few doses were shown in micrograms (in the labels) but in a column headed mg (expressed in 3 decimals). 3.2 Discussion of broad findings 3.2.1 Embedded Constants These make maintenance very difficult, a nd h ide the internal working s of the spreadsheet from users. Experience in ot her dom ains shows that if the constants were to change (perhaps because a drug com pany changes its recommended dosage, or there is a change in clinical pr actice) there is a v ery high risk that the spreadsheet would not be ch anged to reflect this. Because the constants are hidden in the formulas, there is no easy way for a user to confirm that the embedded values are the same as those shown in the adjacent labels. This is important because as discussed a bove, it is very easy to circumvent the protection and change the formulas, either by accident or by design. Because these spreadsheets are distributed freely over the internet there is no way that all users can be identified for a “product recall” in the event that an error is discovered or an update is found to be necessary. It would be much more secure if th e spr eadsheets used form ulas that “looked up” external values clearly identified elsewhere on the spreadsheet. 3.2.2. Complex Nested IF formulas These are used to determine dosages or risk factors relating to m ultiple variables. In the spreadsheets examined, they co mmonly have multiple conditions and complex conditional logic. This class of form ulas is known to be among the most error-prone and difficult to maintain. Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved A more robust and secure alternative w ould be to use the VLOOKUP function to determine the value. This would b e more transparent, eas ier to maintain, and present a much lower risk of error. 3.2.3. Protection / Locked cells The spreadsheets examined use protectio n in conjunction w ith appropriately locked / unlocked input cells to give some elem entary security. As discussed above, this is easy to circumvent and ma y provide a false sense of security to users, especially given the complex f ormulas and embedded num bers outlined above. 3.2.4 Documentation Elementary (but, as stated above, inade quate) user instruct ions and background information was given on the web sites fr om which the spreadsheets test ed were obtained. This was not repeated or linked to in the m odels. Human nature will inevitably lead users of these spreadsheets who find them useful to distribute them to colleagues. Without adequa te documentation, there is a high risk of inappropriate use. While registered users of some web sites may receive advice of updates and co rrections there is a high risk that users who have received them at second hand will not. 3.2.5. Data Validation The two spreadsheets from medal.org included som e basic completeness checking, displaying error messages if bla nk cells are present in the input range. These give no assurance that correct valu es are present, as they would fail to detect (for example) spaces or text pl aced in cells where numbers are expected. There is no check to detect nonsense valu es, or input errors (for example, the dosages calculated in the paediatric anaesthesia model depend on age and body weight, but will not detect such unfeasibl e input as a 300 lb, 6 ft tall, 6 month baby. This is a gross example – We suspect that much subtler input errors could cause disastrous errors in dosage The correct use of Excel’s Data Validati on functions or of its forms tools to restrict the available inputs and warn about unexpected or out-of-range values would greatly reduce the risk of error from incorrect inputs in these critical areas. 3.2.6. Formulas with no dependents All the spreadsheets examined contained fo rm ulas with no dependents that did not appear to be the end results. Our domain know ledge is not sufficient to allow us to determine whether these are critical errors or m erely ways of displaying optional information that is “nice to have” but is not directly related to a m odel’s purpose. Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved 3.2.7 Units in the Paediatric Anaesthesia model While the units are cle arly marked in the labels, it would be fairly easy for a practitioner to confuse milligram s and microgram s, resulting in a serious potential over or under dose of medication, especi ally where m icrogram quantities are displayed in a column headed “mg” (see the Clonidine line (row 9) in the spreadsheet extract shown above). The US Institute of Medicine [K ohn et al (2000)] states that in the US “Medication errors…account for over 7,000 deaths annually” They also cite individual tragic and avoidable deaths that have been caused by dosage mix-ups. The minimum security against errors that should be introduced is to display drugs with exceptions from the normal dosage uni ts with a different background colour to draw users’ attention to the difference. 3.3 Spreadsheet Health Risks Identified Not all of the risks identified aris e from the m echanical aspects of spreadsheets. Many of them are potentially compounded by the method of distribution adopted, which allows: • Distribution of the spreadsheet models separately from the instructions • Onward distribution of models to in dividuals not registered with the providing web site. This means that the authors cannot issue updated models or “product r ecalls” to all users • Amendment and onward distribution of m edical spreadsheets with a spurious “seal of approval ” from the originators. 4 SUMMARY AND CONCLUSION We acknowledge the clinical abilities and knowledge of those involved in the development of the spreadsheets we have examined. W e applaud their intent to make available the highest standard of medi cal care, and we regret that we have to identify specific examples. Our purpose is to highlight the following points based upon our own knowledge of the spreadsheet domain: • The risks arising from use of unteste d and poorly engineered spreadsheets in clinical medicine and • The apparent lack of (and theref ore the scope for development of) good practice in developing spreadsh eet models for clinical use. Source material for good practice in spread sheet development has been developed for the financial and taxation domains and is widely and freely available [ Read, N. & Batson, J., 1999] [O’Beirne, 2005]. We call for more re search into the use of spreadsheets in this safety-critical do main and for the porting of spreadsheet good practice from the financial ar ea to the medical profession. Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved Documentation relating to the use of spread sheets in clinical medicine invariably states that ultimate responsibility f or th e use of such spreadsheets lies with the user. Such is the case in business wher e the spreadsheet user, often a highly qualified and experienced business professi onal, bears ultimate responsibility. We believe, in each case, that delegation of re sponsibility is no barrier to the repeated perpetration of grave errors. 5 REFERENCES Banks, D. & Monday, A. (2002) “Interpretation as a Factor in Understanding Flawed Spreadsheets”, European Spreadsheet Risks Interest Group, 3 rd Annual Symposium, Cardiff, pp13-20. Butler, R (2000) “Risk Assessment For Spreadsheet Developments: Choosing Which Models to Audit”, European Spreadsheet Risks Interest Group, 1 st Annual Symposium, Greenwich, pp 65-74 Cardiac (2006) http://www.medal. org file www-Active-ch6-ch6.03-Sheets- cardiac risk index-1.xls 5th June 2006 17:44 Cederbaum M., Kuten A. (1999) “Spreadshee t calculations of absorbed dose to water for photons and electrons according to established dosim etry protocols”, Med. Dosim. Fall; 24 (3):205-10 Croll, G.J., (2005) “The Importance and Crit icality of Spreadsheets in the City of London” European Spreadsheet Risks Interest Group 6 th Annual Symposium, Greenwich 2006, pp82-92 EuSpRIG, 2005. http://www.eusprig.org/ stories.htm 8/4/05 9:20 Evans, T.J. (2006) http://www.anesthesia-nursing.com/manual.html 5th June 2006 18:15 HMRC (Her Majesty’s Revenue & Custom s), (2006) http://customs.hmrc.gov.uk/channelsPo rtalWebApp/channelsPortalW ebApp.porta l?_nfpb=true&_pageLabel=pageVAT_ShowContent&id=HMCE_PROD_009443 &propertyType=document . 5th June 2006 18:18 Institute for Algorithmic Medicine, Hous ton, TX, USA. “The Medical Algorithms Project” www.medal.org .16:00 3 June 2006 Johnson, K.A, Svirbely, J.R., Sriram, M.G ., Smith, J.W ., Kantor, G., Rodriguez, J.R., (2002) “Automated Medical Algorith ms: Issues for Medical E rrors” J Am Med Inform Assoc. Nov–Dec; 9(6 Suppl 1): s56–s57. Kohn, L.T., Corrigan, J. & Donaldson, M. (Ed) (2000) “To Err is Human: Building A Safer Health System, Institu te of Medicine Copyright © 2006 Grenville Croll/Ray Butler/EuSpRIG. All Rights Reserved Linthout N., Verellen D., Van Acker S., St orme G., (2004) “A simple theoretical verification of monitor un it calculation for intensity modulated beam s using dynamic mini-multileaf collimation” Ra diother. Oncl., May; 71 (2) 235-4 1 Maceneaney PM. & Malone D.E., (2000) “Applying ‘evidence based m edicine’ theory to interventional radiology. Part 2: a spreadsheet for swift assessment of prodedural benefit and harm.” C lin Radiol. Dec; 55(12):938-45. Narchi, H., (2004a) International Pediatrics Vol. 19 No. 2 119-120 Narchi, H., (2004b) International Pediatrics Vol. 19 No. 3 188-191 O’Beirne, P. (2005) “Spreadsheet Check & Control” Systems Publishing, Co Wexford, Ireland Panko, R. (2000) “Spreadsheet Errors: Wh at W e Know. What We Think We Can Do”, European Spreadsheet Risks In terest Group, 1st Annual Symposium, University of Greenwich, pp7-18. Panko, R. (2003) “Reducing Overconfid ence in Spreadsheet Development”, European Spreadsheet Risks Interest Group, 4th Annual Symposium, Dublin, pp49-66. Panko, R (2006) “Spreadsheet Research Web Site” http://panko.cba.hawa ii.edu/SSR/index.htm 15:20 3 June 2006 PubMed, (2006), http://www.pubmed.gov. 17:33 5th June 2006. Read, N. & Batson, J. (1999) “Spreads heet Modelling Good Practice”, IBM & Institute of Chartered Accountan ts in England and Wales, London Svirbely, J (2006) http://www.medal .org file www-Active-ch-18 04-sheets- depression elderly Brodaty -1.xls 5th June 2006 17:44 7 BIBLIOGRAPHY Baker, K., Powell, S., & Lawson, B., ( 2005) “Spreadsheet Engineering Research Project”, http://mba.tuck.dartm outh.edu/spreadsheet 15:07 20/5/05 Croll, G.J., (2003) “A Typical Model Audit Approach”, IFIP, Integrity and Internal Control in Information Systems, Vol 124, pp. 213-219. Grossman, T.A. (2002), “Spreadsheet E ngineering: A research Framework”, European Spreadsheet Risks Interest Group, 3 rd Annual Symposium, Cardiff, pp21-34.