revising the panko– halverson taxonomy of … · 2019-03-15 · revising the panko–halverson...
Post on 04-Aug-2020
2 Views
Preview:
TRANSCRIPT
REVISINGTHEPANKO–HALVERSONTAXONOMYOFSPREADSHEETERRORSAcceptedforpublicationinDecisionSupportSystems,February2010.
RaymondR.PankoUniversityofHawaiiPanko@hawaii.eduhttp://panko.shidler.hawaii.eduSalvatoreAurigemmaUniversityofHawaiiSA8@hawaii.edu
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page2
ABSTRACT
Errortaxonomiesareusefulbecausedifferenttypesoferrorshavedifferentcommissionanddetectionratesandbecauseerrormitigationtechniquesoftenareonlyusefulforsometypesoferrors.Intheearly1990s,PankoandHalversondevelopedaspreadsheeterrortaxonomy.Thispaperupdatesthattaxonomytoreflecthumanerrorresearchmorefully.Thetaxonomyfocusesonquantitativeerrorsduringdevelopmentandtestingbutnotesthatqualitativeerrorsareveryimportantandthaterrorsoccurinallstagesofthesystemdevelopmentlifecycle.
KEYWORDS
Spreadsheet,spreadsheeterror,enduserdevelopment,endusercomputing,executionerror,taxonomy,error,violation,contexterror,omission,logicerror,planningerror,mistake,slip,lapse.
1. INTRODUCTION
Spreadsheetsarewidelyusedincorporations,andthereisstrongconvergentdatashowingthatmostcorporatespreadsheetshavematerialerrors[15].Consequently,agreatdealofallspreadsheetresearchhasfocusedonthestudyoferrors.Whileone“solution”maybetostopusingspreadsheets,humanerrorresearchsuggeststhaterrorratesprobablyaresimilarforotherdecisionsupportsystemdevelopmenttechnologies[14].
Nearlyallspreadsheeterrorresearchershaveusedtaxonomiestocategorizeerrors.Theyhavedonethisbecausetherearemultipleerrormechanismswithdifferentcommissionrates,differentdetectionrates,anddifferentlysusceptibilitiestoerroravoidanceanddetectionmethods.Errormitigationstrategiesneedtobedevelopedandassessedwithrespecttospecifictypesoferrors.
ThepurposeofthispaperistorevisitandrevisethewidelycitedPankoandHalverson[18]taxonomyofspreadsheeterrors.Thereareseveralreasonsfordoingso.First,thetaxonomywasbasedonaspectsofgeneralhumanerrorresearchknowntoPankoandHalversonin1993.Second,subsequenttaxonomieshaveidentifiedimportanterrortypesthatwerenotincludedinthePankoandHalversontaxonomy.Third,theomissioncategoryinthetaxonomyhasproventobetoonarrow,andthemechanical-logical-omissiontrichotomyingeneralneedstobereplacedbythemorewidelyusedmistake-slip-lapsetrichotomy.
ThispapercoverssomeofthesametopicsaddressedbyPowel,Baker,andLawson[23],whoincludeddiscussionsoferrorclassification,impact,frequency,creationandprevention,anddetection.Mostobviously,ourpaperdiffersbyfocusingprimarilyonthefirsttopic,errorclassification.Wewillnoteotherdifferenceslaterinthispaper.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page3
2. TAXONOMIES
2.1TAXONOMIES
Taxonomieshavelongbeenusedinscience.SendersandMoray[31],writingabouthumanerror,saidthat:
…ataxonomyisafundamentalrequirementforthefoundationofempiricalscience.Ifwewantadeepunderstandingofthenature,origins,andcausesofhumanerror,itisnecessarytohaveanunambiguousclassificationschemefordescribingthephenomenonwearestudying.[p.82.]
Thereisno“best”errortaxonomyforspreadsheets[9,25]oranyothertypeofhumancognitiveactivity.Researchersandprofessionalswithdifferentfocusesneeddifferentthingsfromerrortaxonomies.Forinstance,spreadsheetdesignersneederrortaxonomiesthatdistinguishbetweentypesoferrorsthatneeddifferentameliorationstrategies.Thelegalsystem,incontrast,needsdistinctionsthathelpassignresponsibilityfordamages[31].Inaddition,eachtaxonomyilluminatessomeaspectsofaphenomenonwhileblindingtheresearcherorpractitionertootheraspectsofthephenomenon[1].
2.2PHENOMENOLOGICALVERSUSDEEP(THEORY-BASED)TAXONOMIES
SendersandMoray[31]distinguishedbetweendifferentlevelsoftaxonomies.Themostsuperficiallevelconsistsofphenomenologicaltaxonomiesthatarebasedonsimpledescriptionsoferrormanifestations.Forinstance,typingerrorsatthislevelwouldbedescribedbysuchthingsaskeystroketranspositions.Atthelevelofphenomenologicalerrors,thereisnoexplanationforwhydifferenterrorsoccur.
Phenomenologicaltaxonomiesareusefulfordestroyingmythsaboutwhattypesoferroroccurfrequently.Ifacertaintypeoferrorprovestobeparticularlyfrequent,itmeritsparticularattention.Conversely,ifatypeoferroroncebelievedtobeimportantactuallyisfairlyrare,thenshiftingresourcesfromthistypeoferrorstoothertypesoferrorsmaybeimportant.Researchusingphenomenologicaltaxonomies,then,canpuncturefalsebeliefbubbles.
Inaddition,inspreadsheetexperimentsonthedetectionoferrors,experimenterstypicallyseedspreadsheetswitherrorsthattheresearchersbelievetobecommonerrors[7,8,11,16].Ideally,theselectionofseedederrorsshouldreflectthetruerelativefrequenciesofdifferentkindsoferrors.Otherwise,theresultsoftheseexperimentsmeasuredasthepercentageoferrorsdetectedwillbemisleading.
Incontrasttophenomenologicaltaxonomies,deepertaxonomiesareinformedbytheory.Thisisespeciallyvaluableiftheorypredictsmanifestationsofresults.Inerrorresearch,forinstance,theorymaysuggestthatdifferenttypesoferrorswillhavedifferenterroroccurrencerates,differentdetectionrates,ordifferentmechanismsformitigation.Unfortunately,thereisnocompletetheoryforhumanerror,socreatingfulldeeptaxonomiesforspreadsheeterrorsisnotpossibletoday.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page4
2.3ERRORATTRIBUTION
Nearlyallspreadsheeterrorresearchisbasedontheanalysisofspreadsheetsthathavealreadybeendeveloped.Thissuggeststhatweshouldonlyhavephenomenologicaltaxonomies.However,mostpublishedtaxonomiesofspreadsheeterrorstilltrytoexplainobservederrorsintermsofunderlyingtheories.Whilethismaybemethodologicallyundesirable,itisalsoundesirabletousetaxonomiesthatdescribeerrorsbutgivenocluesastowhydifferenttypesoferrorsoccurorhowtheycanberedressed.
Indefenseofattributingerrorcauses,itmaybeplausibletoinferthecauseofmanyerrorsinoperationalspreadsheets.Forinstance,ifasubjectswitchesYear1andYear2salesvaluesafterreadingthemoffasheetofpaper,thisseemslikelytobeduetoalapseinsidethesubject’smemory.
Aswemovefromtightlycontrolledexperimentstotheinspectionofoperationalspreadsheets,wearelikelytoneedmorepurelyphenomenologicaltaxonomies.However,eventhisisonlyaconjecture.Forinstance,ifanoperationalspreadsheetcomputesrevenuesonRow47andinthenextrowmultipliesrevenuesbythecorporatetaxratetocomputecorporatetaxes,itisfairlyclearthatthespreadsheetdevelopermistakenlybelievedthatcorporatetaxesarecomputedonthebasisofrevenuesinsteadofincome.
3. HUMAN ERROR TAXONOMIES
3.1HUMANERRORRESEARCH
Ourconcernisnottaxonomiesingeneralbuthumanerrortaxonomies.Inthis,wearefortunatebecausehumanerrorhasbeenstudiedinmanyhumancognitivedomainsformorethan100years.Thesedomainshaveincludedmathematics,programming,throwingswitches,aircraftaccidents,automobileaccidents,nuclearincidents,proofreading,andlinguistics,tonamejustafew.Inthe1980s,researchersfromdifferenthumancognitiondomainsbegantorealizethattheywereseeingthesametypesoferrorsanderrorfrequenciesindifferentcognitivedomains.Reason[29]summarizedmanyoftheseconvergentfindings.Panko[14]summarizesmeasuredhumanerrorratesinstudiesindifferentfields.
Perhapsthemostimportantfindingfromtheconvergederrorliteratureisthathumancognitiveprocessesproducethecorrectresultnearlyallthetimebuthaveasmallinherenterrorratethatstemsfromthesameprocessesthatproducecorrectresults[29].Inotherwords,thewayweactuallythink(asopposedtothewaywebelievethatwethink)istheheartoftheproblem,notsimplesloppiness.
Unfortunately,thefactthatwemakerelativelyfewmistakesaswedocognitiveworkisnotgoodenoughinsomecontexts.Forinstance,softwareprogrammersusuallyare95%to98%accuratewhentheywritecode[14].However,inprogramsthathavelongflowsoflogic,eventhishighlevelofaccuracyisnotenough.Thesameistrueinspreadsheets.
Athirdimportantfindingisthatwhilewearegoodatavoidingerrorsasweworkandcatchingmanyofourerrorsimmediately,wearenotasgoodatdetectingerrorsafterthefact[14].
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page5
Whenweexamineacognitiveartifactsuchasaprogram,wetypicallyfindonly60%to70%oftheerrors,andthisvarieswidelybyerrortype[14].
Inthispaper,weattempttoexploittheworkonerrortaxonomiesinhumanerrorresearch.ThismakesthispaperdifferentfromthecriticalreviewofPowell,Baker,andLawson[23],whichwasnotbasedonthehumanerrorliterature.
3.2WHATISANERROR?
Themostfundamentalissueinanyerrortaxonomyishowtodefine“error.”SendersandMoray[31]definedanerroras:
“anactionthatisnotintendedbytheactor;notdesiredbyasetofrulesoranexternalobserver;orthatledthetaskorsystemoutsideitsacceptedlimits” SendersandMoray[31],p.25.
Thekeypointisthatthereneedstobeacriterionfordeterminingwhethersomethingiscorrectoranerror.Inmanycases,thecriterionwillbeobvious,suchasamistypednumber.Inothercases,especiallyinmattersofgoodpractices,theremaynotbeauniversallyacceptedcriterion.
3.3MISTAKES,SLIPS,ANDLAPSES
Inhisbook,HumanError,Reason[29]presentedataxonomyofhumanerrorsbasedonpriorworkbyReasonandMycielska[30]andNorman[13].Thistaxonomy,showninFigure1,beginswithabasicdistinctionbetweenplanningandexecutionerrors.Iftheplaniswrong,thisisamistake,regardlessofhowgoodtheimplementationis.However,iftheplaniscorrectbuttheexecutioniswrong,thisisasliporlapse.
Figure1:MistakesversusSlipsandLapses
ThedistinctionbetweenslipsandlapseswasproposedbyNorman[13].Aslipisanerrorduringasensory-motoraction,suchastypingthewrongnumber(say$120,000insteadof$210,000)orpointingtothewrongcellwhenenteringaformula.Incontrast,alapseoccurswithintheperson’shead.Alapseisafailureinmemory.Alapseofteniscausedbyoverloadingthelimitedhumanmemorycapacity.
Thistaxonomyhaspossibleimplicationsforautomatedspreadsheeterrordetectionprograms,whichonlyworkonfinalspreadsheetartifacts.Itislikelythaterrorsinvolvingplanningandmemorythatoccur“offthespreadsheet”willleavefewifanyartifactsinthespreadsheetforautomatedanalysistoolstofind.Evenslipsduringexecutionmaynotleaveartifactsforautomatedspreadsheetanalysisprogramstofind.
Forhumanerrorhunters,too,thethreetypesoferrorssuggestthatconstraininginspectiontothespreadsheetitselfislikelytomissmanyerrors.Itismandatorytoinspectrequirements,designs,anddomainalgorithmstounderstandiftheyhavebeenexecutedproperlyinthespreadsheet.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page6
3.4RASMUSSEN
Rasmussen[28]furtherdividedmistakesintorule-basedmistakes,whichoccurwhendeveloperortesterappliesaheuristicruleincorrectly,andknowledge-basedmistakes,whichoccurwhennoruleappliesandthepersonmustusehisorhergeneralknowledgeofthedeviceorelectricalengineeringingeneral.AlthoughtheRasmussen[28]taxonomyisimportant,applyingitrunsintotwoseriousissues.First,developersandtestersmustbehighlyexperienced,ortheywillnothavewell-developedheuristicrulesoradequateknowledge.Moreseriously,thistaxonomycannotbeusedwithoutdoingaprotocolanalysis.Wewillnotincludethisdistinctioninourtaxonomybecausemostresearchdoesnotuseprotocolanalysis.
3.5ALLWOOD
AstudybyAllwood[2]examinedthecommissionratesanddetectionratesfordifferenttypesoferrors.Allwood[2]conductedaprotocolanalysisstudyusingstudentssolvingmathematicalproblems.Allwood’sstudentsmade327errorsastheyworked.Sixoutofeverytenerrorswereexecutionerrors,whichinvolvedsomethinglikedoinganadditionincorrectly.However,thesubjectsspontaneouslycaught83%ofexecutionerrorsastheyworked.Consequently,executionerrorsaccountedforonly29%offinalerrors.
Logicerrorsthatinvolvedmathematicalthinking,namelysolutionmethoderrorsandhigher-levelmatherrors,onlyaccountedforaquarterofallerrorsmade,buttheirrelativelylowerrordetectionrates(48%and25%respectively),resultedintheiraccountingfor40%ofallfinalerrors.
Skiperrorsinvolvedsubjectsskippingastepinasolutionprocess.Theseerrorswerecomparativelyrare,accountingforonly9%ofallerrors.However,noneweredetectedspontaneously,sotheyresultedin29%ofallfinalerrors.
PankoandHalverson[18]basedtheirtaxonomyofspreadsheetdevelopmenterrorsheavilyonAllwood’staxonomyandresearchfindings.
3.6FLOWERANDHAYES
AnotherintriguingerrorinsightcomesfromFlowerandHayes[6],whousedprotocolanalysistostudythewritingprocess.Theyfoundthattheirsubjectsneededtoworkatseverallevelsofabstractionsimultaneously.Subjectshadtoselectspecificwordswhilegeneratingsentences;andsentenceproductionhadtofitintotheauthor’splanfortheparagraph,forlargerunitsofthedocument,forthedocumentasawhole,andforthedocument’spurpose(requirements).Planninghadtobedoneatalllevelsofabstractionsimultaneously.Eachlevelofabstraction,furthermore,createdconstraintsthathadtobeobeyedwhenconsideringotherlevels.
Figure2showsthatweportraytheFlowerandHayestaxonomyofconcernsasacontextpyramidthatisinverted,placingalloftheweightofallcontextlevelsonthewritingofeachword.Thiscancreateanenormousloadonthewriter’smemoryandplanningresources.Indeed,interruptionstudieshaveshownthatwritingisoneofthemostcognitively-intensivehumanactivitydomains[6].
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page7
Figure2:ContextPyramidinWriting
Inspreadsheetdevelopment,thesameheavymentalloadislikelytooccur.Wheneveradeveloperentersaformula,heorshehastobecognizantofthealgorithmfortheformula,thealgorithmforalargersectionofthespreadsheet,thespreadsheetdesignasawhole,andthespreadsheet’sexternalrequirements.Anerrormayoccurbecauseofaflawatanyoftheselevels.
3.7VIOLATIONS
Earlier,wenotedthatevenwhenweareattemptingtoworkdiligently,errorsareinevitablebecauseoftheverywaysinwhichhumancognitionworks[29].Insoftware,testing,Beizer[4]hasarguedthatprogrammersmustbeheldblamelessforerrorsfoundintestingbecauseoftheinevitabilityoferrorsevenwhenpeoplearediligent.
However,theargumentthaterrorsareinnocentdoesnotapplyifthepersonisintentionallycircumventingpoliciesandrules.Thisideawasfirstarticulatedinhumanresearchonautomobileaccidents,inwhichspeeding,drinking,andotherviolationsofthelawareviewedasnon-inevitableandblameful[29].Consequently,itmakessensetomakeadistinctionbetweeninnocenterrorsdotohumancognitiveprocessesanderrorsduetoviolations.
Indriving,therearespecificlawsthatprescribemosttypesofdangerousdrivingbehavior.Consequently,identifyingcertaindrivingactionsasviolationsoftenisstraightforward.However,eveniftalkingonahands-freemobilephoneislegal,itsignificantlyreducesaperson’sdrivingability,andaccidentswehavewhentalkingonhands-freemobilephone,whilenotillegal,maystillbenegligence.Theusefulnessofadistinctionbetweeninnocenterrorsandviolationsseemstobemostusefulwherethereisstrongagreementonwhatisacceptableandunacceptable.
4. SPREADSHEET ERROR TAXONOMIES
Sofar,wehavelookedatgeneralhumanerrortaxonomies.Wewillnowlookspecificallyatspreadsheeterrortaxonomies.
4.1HUMANERRORTAXONOMIESANDEXPERIMENTS
Manyspreadsheettaxonomieshavebeenbasedondatafromexperiments.Powell,Baker,andLawson[23],citingReason[20],notethatexperimentsaredangerousbecausetheyoftenarecontrived.Certainly,thisistrueissomeexperiments.However,spreadsheetexperimentsusuallyrequireadevelopertocreateaspreadsheetfromawordproblemortoattempttodetecterrorsinaspreadsheet.Theseactivitiesdonotseemtobeoverlycontrived.
Moreimportantly,experimentsareusefulinisolatingspecificaspectsofhumancognitionanderrormaking.Ingeneral,spreadsheetshavefocusedondeterminingwhetherresearchresultsfromsoftwaredevelopmentwillcarryovertospreadsheetdevelopment.Ingeneral,theydo[16,18].
Inaddition,experimentsareusefulnotonlyinmeasuringrawerrorratesbutinnotinghowcommissionanddetectionratesdifferfordifferenttypesoferrors.Forinstance,Panko[16]foundthatomissionerrorsaredetectedmuchlessfrequentlythanothertypesoferrors—afindingseeninresearchinotherhumancognitivedomains[29].
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page8
Evenforinspectionsforoperationalspreadsheets,resultsfromhumanerrorresearchareofpracticalimportance.Ifdetectionratesreallyaredifferentfordifferenttypesoferrors,thentheprofileofdetectederrorswillnotmatchtheprofileofrealerrorsinspreadsheets.Also,ifresearchshowsthatthedetectionoferrorsinlongerformulasislessthanitisinshorterformulas[16],thismeansthaterrordetectionprotocolsshouldspecifyminimumtimestobespentonmorecomplexformulas.Insoftwarecodeinspection,furthermore,ithasbeenfoundthatdetectionyieldisstronglytiedtothemaximumnumberitemstobecoveredinaninspection,andtheminimumtimetobetakenininspection[5,14].
4.2GALLETTA
Gallettaetal.[8]conductedanexperimentusingMBAstudentsandaccountantsworkingontheirCPAaccreditation.Inthisstudy,subjectsinspectedspreadsheetslookingforerrors.Galletta,etal.[8]dividederrorsintotwotypes.Domainerrorsoccurredwhenaformularequiredknowledgeofaccounting.Deviceerrorsinvolvedusingthecomputerandthespreadsheetprogram—typingerrorsandpointingerrors.Thestudyfoundthatdeviceerrorshadahigherdetectionratethandomainerrors.
4.3PANKOANDHALVERSON
Fortheir1993experimentonerrorsinspreadsheetdevelopmentandinspection,PankoandHalverson[18]createdataxonomyofspreadsheetresearchissuesasathreedimensionalcube.Figure3showsthatthethreesidesofthiscubewereresearchissue,lifecyclestage,andmethodology(experiment,survey,etc.)foraddressingtheresearchissues.
Figure3:PankoandHalversonSpreadsheetRisksResearchCube
Researchissuesincludedstructuralconcerns(poorstructure),actualerrors,userworkpractices,assumptions,andspreadsheetmodel’scharacteristics(size,percentageofcellsthatareformulasordata,complexityofformulas,one-timeuseversusmany-timeuse,thenumberofpeoplewhousethespreadsheet,risks,andcontrolpolicies.Inotherwords,thetaxonomywentwellbeyondquantitativeandqualitativeerrorcategoriesandwellbeyonderrorstudiesingeneral.
Under“actualerrors,”whichmeantquantitativeerrors,thetaxonomynotedseveralwaystocounterrorsandnotedthateachhasadvantagesanddisadvantages.Theerror-countingmetricslistedwerethepercentageofmodelscontainingerrors,thenumberoferrorspermodel,thedistributionoferrorsbymagnitudeorseverity,andthecellerrorrate.
Forerrormagnitudeandseverity,PankoandHalverson[18]notedthat,“Someerrorsareimportant,otherunimportant.Onemeasureisthesizeoftheerrorasapercentageofthecorrectbottom-linevalue.Anotheriswhetheradecisionwouldhavebeendifferenthadtheerrornotbeenmade.Wesuspectthatquiteafewerrorsareeithertoosmalltobeimportantorstillgiveanswersthatleadtothecorrectdecisions.”
Powell,Baker,andLawson[25]discussederrormagnitudeinsomedetailanddidsoevenmoreinanearlier[22]paper.Theyfocusedonthedollarmagnitudesandpercentagemagnitudesof
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page9
errors.Otherstudieshavelookedattheseriousnessoftheerrorsinthecontextinwhichthespreadsheetwasused[15].
Intermsofthecellerrorrate,whichisthepercentageofcellsthatcontainanerror,PankoandHalverson[18]weretakingacuefromsoftwaredevelopmentresearch,whichhaslongmeasuredthefaultsperthousandlinesofnoncommentsourcecode(faults/KLOC).Therateoffaults/KLOCisroughlythesameacrossprograms.Thisallowssoftwaredeveloperstogetaroughestimateofthenumberoferrorstheycanexpecttofindwheninspectingamoduleofcodewithknownlength.Inmanufacturing,reliabilityengineersalsomeasureaverageerrorratesfordifferenttypesofactivities,inordertodesignandmanageprocesses.
Consequently,PankoandHalverson[18]suggestmeasuringerrorfrequencyintermsofthecellerrorrate(CER)—thepercentageofcellscontainingerrors.ForthecomputationofCERs,PankoandHalverson[18]arguedthatspreadsheetresearchshoulddividethetotalnumberoferrorsinvalue(formulaandconstant)cellsbythetotalnumberofvaluecells.WewillseethatthisCERmeasurehasproventobeinadequate.
Likefaults/KLOC,theCERisaroughwaytoanticipatethenumberoferrorsinaspreadsheet,justasfaults/KLOCisinsoftware.Noteveryspreadsheetwillhavethesamecellerrorrate,muchlesseverymoduleinaspreadsheet.Inaddition,forbothfaults/KLOCandCERcalculations,omissionsandsomeothererrorsdonotoccurinaparticularcellandthereforedonotaffectthenumberofcells,except,inthecaseofomissions,toreducethem.Likeothertypesofbaseerrorrates[14],thecellerrorrateisausefulindicatorofanticipatederrorrates,notaprecisiontoolforestimatinghumanerrorrates.However,itisapowerfulwaytoshowthatspreadsheetcellerrorratesarefartoohighforsafety,giventhelongchainsofformulasleadingtoresultsinspreadsheets.
PankoandHalverson[19,20]alsoarguedthaterrorsshouldbecountedonlyonce,inthecellsinwhichtheyoccur.Forexample,ifthiserrorisrepeatedincopiedcells,itshouldonlybecountedasasingleerror.(Theoriginalformulathatiscopiediscalledtherootformula.)Also,onlycellsinwhichtheerrorwasactuallymadeshouldbecounted,notdependentcellsthatareincorrectonlybecauseoferrorsinprecursorcells.Mostsubsequentstudieshaveusedthis“originalsin”approach.Ofcourse,incomputingcellerrorrates,thesamenumeratoranddenominatormustbeused.Forexample,ifonlytherootformulainarowofcopiedcellsisusedasthenumeratorforacopyingerror,thesamemustbetrueinthedenominator.
Figure4showsthePankoandHalverson[18]taxonomyofdevelopmentandtestingerrortypes.Thetaxonomyfirstdivideserrorsintoqualitativeandquantitativeerrors.Thisdemarcationofthetwotypesoferrorswasverysimple.Ifsomethingmakesacomputed(“bottom-line”)valueincorrect,thenitisaquantitativeerror.Ifitdidnot,itisaqualitativeerror.
Figure4:PankoandHalverson1996TaxonomyofDevelopmentandTestingErrorTypes
Themostcommonqualitativeerrorisputtingaconstantinsteadofacellreferenceintoaformula[Panko,1988].Forinstance,ifthetaxequalstheincomebeforetaxtimesthetaxrateof15%,theformulafortaxshouldnotgivethecellreferencetoincomebeforetaxandthenmultiplythisby15%.Ifthetaxratechanges,findingallinstancesofwherechangesshouldbemadeisdifficult.Consequently,someinstancesofthetaxratewouldbechanged,butothersmightnotbe
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page10
changedbecausetheyarenotfound.Thispracticeofinsertinganumberinanequationisoftencalledhardcoding.
Panko[1988]suggestedthathardcoding,whilenotcreatingimmediateerrors,wouldresultinlatererrors.TeoandTan[32]testedthisconjecture.Theyfoundthatstudentswhodidhardcodingdid,infact,makemoreerrorsduringsubsequentwhat-ifanalyses.Reason[29]callserrorsthatdonotproduceanimmediatenumericalerrorbutthatarelikelytoproducesubsequentnumericalerrorslaterlatenterrors.Forexample,supposethedeveloperdoesnotturnoncellprotection(aqualitativeerror).Later,ausermaymistakenlytypeanumberinaformulacell.Now,thespreadsheet’scomputationsareincorrect(aquantitativeerror).
Thedistinctionbetweenquantitativeandqualitativeerrorsisnotthesameasthedistinctionbetweenseriousandnonseriouserrors.Manyquantitativeerrorsaresmall,whilequalitativeerrorscanleadtoextremelyseriouserrorslater.Inaddition,manyqualitativeerrorssuchaspoordesignreduceproductivityandcauseotherproblemseveniftheydonotresultinnumericalerrors.Ignoringqualitativeerrorsisnotanoptionforcorporations.Havingsaidthis,researchershavetendedtofocusonquantitativeerrorsbecausemuchspreadsheeterrorresearchhasbeendoneatleastinparttodocumentthatthereisaspreadsheetaccuracyproblem,andquantitativeerrorsaremoreconvincingthanqualitativeerrors.
FollowingAllwood[2]broadly,PankoandHalverson[18]dividedquantitativeerrorsintothreebasictypes:mechanical,logic,andomissionerrors.
Ø Mechanicalerrorsaretypingerrors,pointingerrors,andothersimpleslipsandlapses.Mechanicalerrorscanbefrequent,buttheyhaveahighchanceofbeingcaughtbythepersonmakingtheerror.
Ø Logicerrorsareincorrectformulasduetochoosingthewrongalgorithmorcreatingthewrongformulatoimplementthealgorithm.
Ø Omissionsarerequirementsleftoutofthemodel.Theyoftenresultfromamisinterpretationofthesituation.Humanfactorsresearchhasshownthatomissionerrorsareespeciallydangerousbecausetheyhavelowdetectionerrorrates[14,29].
PankoandHalverson’sfirststudyusingthetaxonomywasadevelopmentexperimentinwhichsubjectscreatedaspreadsheetworkingalone,ingroupsoftwo,oringroupsoffour[19].Theauthorsconductedaninter-raterreliabilitytestonthetaxonomy’stripartitedistinctionbetweenquantitativemechanical,logical,andomissionerrors.Thesubjectsmadethesame209quantitativeerrorsaccordingtobothresearchers,fora100%reliabilityrateinoverallerrorcounting.Withinthesequantitativeerrors,theresearchersinitiallydisagreedontheclassificationofasingleerrorthatoccurredinthreespreadsheets.Thisrepresented99.6%reliability.Thepointofdisagreementwasasingleerrormadebythreedifferentsubjectswhoaddedexpensestorevenuestogetincome,insteadofsubtractingexpensesfromrevenues.Oneresearcherclassifiedthisasalogicerror(believingthattheyshouldbeadded),theotherasamechanicalerror(typinga+insteadofa-).
Panko[16]laterconductedaninspectionstudy,usingamodificationoftheGallettaetal.[8]inspectiontaskandavariantoftheFagan[5]codeinspectionmethodology.Thistime,Pankotestedthedistinctionbetweenomissionerrorsandothertypesoferrors(mechanicalandlogical).
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page11
Consistentwithotherresearchonhumanerror,omissionerrorsweredetectedmuchlessfrequentlythanothertypesoferrors.Thestudyalsofoundthaterrorsinlongformulasweredetectedlessfrequentlythanerrorsinshorterformulas.
ThethirddimensioninthePanko–Halversonspreadsheetresearchissuescubewaslifecyclestage.Basedonthepriorspreadsheetliterature,PankoandHalverson[18]dividedthespreadsheetlifecycle(notjustthespreadsheetdevelopmentlifecycle)into5stages:requirementsanddesign,cellentry,thedraftstage(aftercarefuldevelopmentbutbeforetesting),debugging(testing),andoperation(useafterdevelopment)PankoandHalverson[18]suggestedthattheerrorratevariesstronglyacrossthislifecycle,asFigure5indicates.Throughthedraftstage,errorstypicallyincreasewithtime.Duringtestingandoperationaluseofthespreadsheetshasbegun,errorstendtodecrease(althougherrorssometimesincreaseduringoperationaluse,especiallyofcellprotectionisnotturnedon).
Figure5:ErrorDensitybyLifeCycleStage
AlthoughthePankoandHalverson[18]taxonomyhasbeenfairlywellvalidatedbyexperiments,somelimitationshavebecomeobviousovertime.First,althoughthetaxonomyhasbothanerrortypedimensionandaspreadsheetlifecycleperspective,PankoandHalversondidnotfleshoutthelifecycledimension.Theydidnotlookatthetypesoferrorsthatoccurduringinitialanalysisandrequirements.Moreconcretely,becausetheydidnotstudyongoingusetheywerenotawareuntillaterofoverwritingerrors,inwhichauseroverwritesaformulainanoperationalspreadsheetwithanumber.
Second,theyfocusedonomissionerrorsbecausethesewerethesubjectofearlierhumanerrorresearch.However,anomissionofarequirementisonlyonetypeofrequirementnoncompliance[12].
Third,thetaxonomydidnotrecognizetheimportantdistinctionbetweensensory-motorslipsandmemorylapses.Thisisimportantbecauseitislikelythatautomatederrordetectiontoolsseemmorelikelytocatchslipsthanlapsesthatoccurinsideaperson’shead.
4.4RAJALINGHAM
Rajalinghamledthecreationofontaxonomyin2000[26]andexpandedonthistaxonomyin2005[27].Theinitialtaxonomy[26],likethePankoandHalverson[18]taxonomy,makesthedistinctionbetweenqualitativeandquantitativeerrors.Itthenmakesadistinctionbetweenaccidentalandreasoningquantitativeerrors.ThisissimilartothePankoandHalverson[18]mechanicalversuslogicaldistinction,butitsterminology(accidentalversusreasoning)isbetterconnotatively.
Anotherimportantadditioninthistaxonomyisthedistinctionbetweendeveloperandend-useraccidentalerrors.PankoandHalverson[18]onlyfocusedondevelopererrors.Theydidnotconsiderthetypesoferrorsthatenduserswouldmakeafterdevelopment.Mostobviously,theyfailedtoconsiderdataentryerrors,whichcanbeveryimportant.Theseerrorscanincludeinputtingincorrectdataorevenoverwritingaformulawithanumber.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page12
Rajalingham,etal.[26]alsoconsiderserrorsthatusersmakeininterpretingtheresultsofspreadsheets,evenifthespreadsheetisnumericallycorrect.Thiswasamajorinsight.
4.5HOWEANDSIMKIN
Foracodeinspectionexperiment,HoweandSimkin[11]createdanewtaxonomyforspreadsheeterrors.
Ø Dataentryerrors.Outofrangevalues,negativevalues,avalueenteredasalabel.
Ø Clericalandnon-materialerrors.Incorrectdatesinlabels,misleadinglabels,andsoforth.(Previousstudieshaveignoredsucherrors.)
Ø Rulesviolations.Cellentrieswhichviolateastatedcompanypolicy.Theseviolationsdonothavetobedeliberate.
Ø FormulaErrors.Inaccuraterangereferences,embeddedconstants(hardcoding),illogicalformulas.
Violationsarepartsofthemodelthatviolaterequirements.Omissionerrorsdothis,butsodomanyothertypesoferrors,suchascomputingovertimepayforasalariedemployeewhoisnoteligibletoreceiveovertimepay.Thisisdifferentfromtheconceptofviolationsindriving,describedearlier,whichinvolvedeliberatemisconduct.
Oneconcernwiththetaxonomyisthatitmixesquantitativeandqualitativeerrors.Misleadinglabelsmightbeclassifiedaseither,whilehardcodingisnormallyseenasaqualitativeerrorbecauseitdoesnotmakeacomputedvalueincorrectimmediately.
4.6POWELL,LAWSON,ANDBAKER
Fortheirseriesofprojectsinvolvingthecreation,testing,anduseofaninspection(auditing)methodologyforoperationalspreadsheets,Powell,Lawson,andBaker[24,25]developedanothertaxonomyoferrors.
Ø Logicerrors:Formulaisusedincorrectly,leadingtoanincorrectresult.
Ø Referenceerrors:Aformulacontainsoneormoreincorrectreferencestoothercells.
Ø Hard-Coding:Oneormorenumbersappearinformulas,andthepracticeissufficientlydangerous.
Ø Copy/Paste:Aformulaiswrongdotoanincorrectcutandpaste.
Ø DataInput:Anincorrectdatainputisused.
Ø Omission:Aformulaiswrongbecauseoneofitsinputcellsisblank.
Whilelaboratoryexperimentsmayhaveenoughcontexttousetheory-informedtaxonomies,Powell,Lawson,andBaker[24,25]decidedthattheyusedaphenomenologicaltaxonomybasedontheformsoftheerrorstheyencountered.Asnotedearlier,movingtomore
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page13
purelyphenomenologicaltaxonomiesmaybedesirableaswemovefromlaboratoryexperimentstooperationalspreadsheets.
Thistaxonomy’suseofomissionerrorsisverydifferentfromtheuseofomissionerrorsinthePankoandHalverson[18]taxonomy.InthePankoandHalversonusage,somethingintherequirementsisleftoutofthespreadsheet.Thisisnotlikelytobedetectablebylookingatthespreadsheet.Incontrast,inthePowell,Lawson,andBaker[24,25]taxonomy,anomissionerrormeanspointingtoablankcell.ThistypeofomissionerrorprovedtoberareinthePowell,Lawson,andBakerstudy[25].Inthistaxonomy,thereisnothingliketheomissionerrorspositedbyPankoandHalverson[18].
HardcodingisdescribedasaqualitativeerrorinthePankoandHalverson[18]andtheRajalingham[2005]taxonomy.HoweandSimkin[11]alwaysclassifyitasaformulaerror,whichcaneitherbequantitativeorqualitative.InthePowell,Lawson,andBaker[22]taxonomy,hardcodingisusuallynotcountedasanerrorbutis,“unlessitissufficientlydangerous.”Thetaxonomy,then,doesnotfollowtheusualquantitative-versus-qualitativedistinction.Instead,itcountssomequalitativeerrorsiftheyareseriousbutcountsallquantitativeerrors,eveniftheyarenotserious.
5. A REVISED PANKO AND HALVERSON TAXONOMY
Basedonthepreviousdiscussion,wenowpresentourrevisedtaxonomyofspreadsheeterrors.
5.1MEASURINGERRORS
Asdiscussedearlier,itisimportanttohavecommonagreementabouthowtocountthenumberoferrors.Alsoasdiscussedearlier,countingthenumberoferrorsisnottrivial.Moststudiesusethe“originalsin”rule—onlycountinganerrorinthecellinwhichitoccurs.Althoughthisrulegenerallyisrelativelyeasytoapply,somemeasurementgoalsneedtotakedifferentapproaches.Forinstance,ifthegoalistoquantifytheimpactofanerror,thenthefocusfallsexplicitlyonvaluesinsubsequentcells[22].Ifarootcelliscopied,furthermore,thefactthatcopiedformulascreateinaccuraciesinmultiplebottom-linevariablescannotbeignored[22]instudiesofimpacts.
Anotherissueoccurswhencountingerrorsinaworkbookwithmultipleworksheets.Ifthesameerroroccursinmultipleworksheets,thereissomemerittocountingitasasingleerror,butifthegoalistoassesswhatpercentageofallworksheetsthatareincorrect,thenitwouldbebettertocounttheerroronceineachworksheet[25].
Theconceptofcellerrorrates(CERs),asnotedearlier,isderivedfromtheprogrammingconceptoffaultsperthousandlinesof(noncomment)sourcecode(faults/KLOC).Itisimportant,incountingcellerrorrates,tospecificthedenominatorprecisely.Insoftwaredevelopment,commentstatementstypicallyareexcludedfromthedenominator.Inspreadsheets,thiswouldcorrespondtoexcludinglabelcells.
Asnotedearlier,PankoandHalverson[18]usedthenumberofvaluecells—constantsandformulas—asthedenominatorintheirstudies.Somesubsequentstudies,however,usedallnon-emptycells(includingtextcells)intheirdenominator,whileothercellerrorrateshavebeenbased
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page14
onlyonformulacells.DifferencesindenominatorsforcalculatingtheCERcanmakeresearchresultsdifficulttocompareacrossstudies.
Figure6identifiessomepossiblewaysofdefiningcellerrorratesandgivessuggestednamestobeusedinfutureresearch.
Ø CERVisbasedonvaluecells.Thisincludesnumericalandformulacells.
Ø CERFisbasedonformulacells.Ifmosterrorsoccurinformulacells,thenCERFwillbelargerthanCERVforthesamenumberoferrors.
Ø CERNisbasedonnumericalcells.Itisusefulfordiscussingdatainputerrors.
Ø CERAisbasedonallnon-emptycells,includingformulacells,numericalcells,andlabelcells.
Figure6:TypesofCellErrorRates(CERs)
Researchersshouldspecifywhichformofcellerrorratetheyarereporting.Theyshouldalsoreportthenumberofnumerical,formula,andtextcellsseparatelytoallowotherstorebasetheirerrorratesforcomparisonwithresultsfromotherstudies.Forformulas,thenumberofuniqueformulasshouldbereportedaswell,forbothinthenumeratoranddenominator.
5.2VIOLATIONSANDINNOCENTERRORS
Figure7showsourrevisedtaxonomyofspreadsheeterrors.FollowingReason[29],thetaxonomyfirstdividesallerrorsintoviolationsandinnocenterrors.Mosterrorsareinnocenterrors,butsomeproblemsareduetodeliberateviolationsofcorporatestandardsorguidelinesforspreadsheetdevelopment.Worseyet,someincorrectspreadsheetsareincorrectbecauseofmoreseriousviolations,suchasoutrightfraudorpuffery(usingexaggeratedor“cooked”numberstoencouragepeopletomakepoordecisions).Whileemployeesshouldnotbepunishedforinnocenterrors,violationsdeservesanctions.
Figure7:RevisedTaxonomyofSpreadsheetErrors
Whataboutunknowingdeparturesfrompoliciesandspecifiedgoodpractices?Aretheyalsoviolations?Webelievethattheyarenot.Unlessadepartureisintentionalorischaracterizedbyconsiderablenegligence,itisnotaviolation.Thisfollowsthemensrearequirementforcriminalprosecutionsunderthelaw.
5.3QUALITATIVEVERSUSQUANTITATIVEERRORS
Forinnocenterrors,thistaxonomycontinuestousethedistinctionbetweenqualitativeandquantitativeerrors.Quantitativeerrors,quitesimply,areincorrectformulasordatacellsthatmakethemodelincorrect.Qualitativeerrors,inturn,mayleadtoquantitativeproblemslaterbutdonotmakethemodelincorrectimmediately.
IntheoriginalPankoandHalverson[18]taxonomy,quantitativeerrorsproducedimmediateincorrectresults.However,amodelcanbecomeincorrectwithoutimmediatelygivingthewrongnumber.Forinstance,ifauseroverwritesaformulawithanumber,bottom-linecalculationsmaybecorrectforthisusagealthoughthemodelisnolongergenerallycorrect.Togiveanotherexample,if
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page15
anincorrectcellreferencepointstoacellthathappenstohavethesamevalueasacorrectcell,thenthispointingerror,whileclearlyanerror,willnotresultinanincorrectvalue[22].
Evenwithspecialcases,itappearsthatquantitativeerrorscanbecountedfairlyunambiguously.Qualitativeerrors,incontrast,typicallyareviolationsofgoodspreadsheetdevelopmentpractice.However,thereisnotstrongconsensusforwhatconstitutesgoodspreadsheetpractices.
5.4PLANNINGERRORS(MISTAKES)VERSUSEXECUTIONERRORS(SLIPSANDLAPSES)
Thetaxonomydividesquantitativeerrorsintoplanningerrorsversusexecutionerrors.Thisdistinctionfocusesontheinstantwhentheuserbeginstoentertheformula.Anerrorbeforethatinstantisaplanningerror.Anerrormadeafterthatinstantisanexecutionerror.IntermsoftheNormanandReasondistinctionsdescribedearlier,aplanningerrorisamistake,whileanexecutionerrorisasliporalapse.
5.5DOMAINANDSPREADSHEETEXPRESSIONPLANNINGERRORS
Thetaxonomydividesplanningerrorsintodomainplanningerrorsandspreadsheetexpressionplanningerrors.Thisdistinctionarguesthatplanninghastwoaspects.First,planningforaformulaorsectionneedstohaveadomaincomponent.Ifthespreadsheetdealswithaircraftwingdesign,aerodynamicsislikelytobeimportantincreatinganalgorithm.
Inaddition,thedevelopermusthaveaplanforexpressingthedomainplanonaspreadsheet.Spreadsheetexpressionmayincludetheuseoffunctions.Italsomaymeanexpressingdomainconceptsthatdonotnaturallyfittherow/columndesignofspreadsheetsintoaspreadsheetsectionwithmultipleformulas.
Thisdistinctionisimportantbecauseautomatedspreadsheeterrordetectionprogramsseemmorelikelytofindspreadsheetexpressionplanningproblemsthandomainplanningproblems.Domainplanningproblemsmaynotbedetectiblewithoutdomainknowledge.
5.6SLIPANDLAPSEEXECUTIONERRORS
Executionerrorsfitthedistinctionbetweenslipsandlapsesdiscussedearlier.Thisdistinctionmayalsohaveimplicationsforautomatederrordetection.Slipsmayleadtoerrorsinpointingtothewrongcellandothererrorsthatleavedetectablepatternsonaspreadsheet.Lapses,whichoccurwithinthebrain,maybelesslikelytoleavesuchdetectiblepatterns.
5.7LIFECYCLESTAGESANDROLES
Figure7showsdevelopmentandtestingerrors.However,asthePankoandHalversontaxonomy[18]noted,wealsonotethatspreadsheetsgenerallygothroughasystemlifecyclethatbeginswiththeanalysisofthecurrentsituationandneedsandendswhenthespreadsheetisterminatedorreplaced.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page16
Thefirstpartofthislifecycleisthesystemdevelopmentlifecycle.However,mostofaspreadsheet’slifeisspentinoperationaluse,soweneedtofocusontheentiresystemlifecycle—notonlyonthesystemsdevelopmentlifecycle.
Differenttypesoferrorswilloccuratdifferentstagesofthesystemslifecycle.Forrequirementsanddesign,thesoftwareengineeringliteraturemayprovidegoodguidanceonwhattolookfor—includingthefactthatalargefractionofallerrorsoccurduringrequirementsanddesigninsteadofduringprogrammingandtesting[12].Inaddition,spreadsheetdevelopmentoftenusesaprocessmoreakintoagiledevelopmentthantraditionaldevelopment,sospreadsheetprofessionalsshouldlookforerrorresearchinthecontextofnontraditionaldevelopment.
Arguablythemostimportantstageisoperationaluse.Manyspecificerrors,suchasenteringthewrongnumberforavariableorincorrectlyimportingdata,occurprimarilyduringoperationaluse.Violationsalsomustbeanticipated,suchasviolationsofprivacyortheuseofspreadsheetstocommitfraud.Otheroperationaluseproblemsincludelackofmaintenanceofdocumentation,ofversioncontrol,andtransitionswhenthedeveloperormaintainerchangesjobs.
Anotheraspectoflifecyclethinkingisthatthereareseveralpossibleorganizationrolesinvolved.Duringdevelopment,forinstance,theremaybeseparatedevelopers,testers,managers,andorganizationalclients.Duringoperationaluse,theremaybeseparateowners,operatorswhoenterdataanddootherhands-ontasks,customersoftheinformation,andotherroles.Weneedtothinkaboutviolationsandinnocenterrorsthatmaybemadebyeachpotentialroleduringeachstageofthelifecycletounderstanderrormitigationneeds.
Ofcoursesomeoftheserolesmaybecombined—mostobviouslyifthedeveloperisalsothetester,clientanduserofthespreadsheet.However,evenwhenrolesarecombined,itmaystillmakesensetothinkintermsoflogicalrolestoconsiderpossibleerrors.Inaddition,whilecombiningrolesmaydecreasesomeerrors,suchascommunicationerrors,itmaymakeothersmorelikely,suchasthetendencytobecomefixatedinwaysthatmakeapersonlessabletoseetheerrorsthattheymade.
6. INTER-RATER RELIABILITY ANALYSIS
Taxonomies,likeanyotherresearchmethodology,shouldbejudgedonanumberofcriteria.Everytaxonomyshouldfacetheentirebatteryoftestsrequiredtoassessitsinternalandexternalvalidity,butaparticularconcernisreliability.Reliabilitymeansthatifdifferentpeopleusethetaxonomytoclassifythesameeventsoritems,theywillclassifyindividualitemsinthesameway.Ataxonomythatcannotbeappliedreliablybydifferentpeopleisafailedtaxonomy.Toassessthereliabilityofourtaxonomy,thetwoauthorsconductedaninter-raterreliabilitystudyinwhichtheyindependentlyclassifiederrorsinacorpusofspreadsheets.
Reliabilityneveris100%.Ingeneral,aninter-raterreliabilityof90%orhigheristhegoal,althoughaninter-raterreliabilityof60%to70%maymakeastudypublishableasanexploratorystudy.Infieldstudies,whichdealwithmessiersituations,somewhatlowerinter-raterreliabilityvalueswillbeacceptable.Eventhen,however,methodologydesignersmustusecoarsertaxonomieswhosebroadercategoriescanbeassignedrobustly,sothatinter-raterreliabilitywillstayhigh.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page17
Thecorpusofspreadsheetswascreatedforapreviousstudy[17].Inthatstudy,studentsdevelopedspreadsheetmodelsfromtheKookerwordproblem.Thistaskhadstudentsdevelopatwo-yearproforma(projected)incomestatement.Thefullcorpushas74spreadsheets.Forthisstudy,weusedthefirst50spreadsheetsinthiscorpusbutthrewoutsixthatcouldnotbeanalyzedwithatrialversionofSpreadsheetProfessional™.Thislimitationwasirrelevanttothisstudy,butwewishedtomaintaincommonalityforotherstudiesusingthecorpus.Ofthese44remainingspreadsheets,40containederrors.Thetotalnumberoferrorswas98.
Asapre-test,thetwoauthorsindependentlyclassifiederrorsinthefirst5spreadsheetsofthecorpus.Whentheycomparedtheresults,theyrealizedthattheywerenotfocusingpreciselyonwhethertheerrorhappenedduringformulaplanningorexecution.Afterclarifyingthattarget,thetwoauthorscategorizederrorsintheremaining39spreadsheets,whichcontainedatotalof86errors.
Errorsinthecorpushadbeenidentifiedpreviously.TheKookertaskhasanunambiguoussolution.Arater(differentfromtheonesinthisstudy)foundspreadsheetswithincorrectanswers,identifiedtheerror,andfixedtheerror.Ifthespreadsheetwasstillincorrect,herepeatedthisprocessuntilthespreadsheetwascorrect.Herecordedtheerrors.
Inthereliabilityprotocol,thefirstthingtodowastoclassifytheerrorasaplanningerror(mistakebeforeenteringtheformula)oranexecutionerrorinenteringtheformula.Thetwoauthorsdidthisbeforetheysub-classifiedplanningandexecutionerrorsintosubtypes.Theythenwentbacktoeacherror.Theyclassifiedeachplanningerrorasadomainplanningerrororasaspreadsheetexpressionplanningerror.Theyclassifiedeachexecutionerrorasalapseorasaslip.
Forthe39spreadsheetsusedinthisphase,theauthorsagreedonthetwo-phaseclassificationof85outofthe88errors,foraninter-raterreliabilityvalueof96.6%.Thisisacceptablereliability.Althoughclassificationmayseemtobedifficultintheabstract,thetwoauthorsnotedthatitfairlyeasytoclassifymosterrorswhentheywereseenincontext.Overall,thetaxonomyappearstobereliablewhenratersusedtheprotocoltoclassifyerrors.However,likeanytaxonomyitisnotperfect.Applyingthistaxonomyandprotocoltoothercorpusesmaydiscloseotherweaknesses.Inparticular,wesuspectthaterrorsinlongcomplexformulaswouldbeverydifficulttoclassify.
7. ERROR FREQUENCY
7.1ERRORFREQUENCY
Amajorbenefitoftaxonomiesistheabilityoftaxonomyuserstoexaminetherelativefrequenciesofdifferenttypesoferrors.Asnotedearlier,differenttypesoferrorsmaycallfordifferentavoidanceanddetectionstrategies.Ifarareerrortypeisextremelyexpensivetoaddress,addressingitmaynotbeworththeeffort.Incontrast,ifanerrortypethoughttoberareprovestobefrequent,moreattentionmaybeneededtoitsstrategiesforitsamelioration.
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page18
7.2SPREADSHEETERRORDETECTIONPROGRAMS
Apotentiallyimportanttoolfordetectingerrorsistheautomatedspreadsheeterrordetectionprogram,whichistypifiedbySpreadsheetProfessionalandExcelErrorCheck.Thesetoolsworkbyhighlightinganomalouspatternsinthespreadsheet,suchasacellwithnoprecedentsorachangeinthecopyingpatternofaformulaasitiscopiedacrosscolumnsorrows.Itisreasonabletoassumethatthesetoolswillworkbestforidentifyingsliperrors.Planningerrorsandlapseswithinthedeveloper’sheadmayleavenopatternforthesoftwaretoidentify.
7.3ERRORSINOURCORPUS
Althoughthepurposeofthereliabilityanalysiswastoassessthereliabilityofthetaxonomyandprotocol,itisinterestingtonotethedistributionoferrorsfoundinthestudy.Figure8summarizesthe85jointlyclassifiederrors.Thefigureshowsthat82%oftheerrorsweremistakes(planningerrors),andallbutoneofthesemistakeswasadomainplanningerror.Only18%oftheerrorswereexecutionerrors,andmoreofthesewerelapsesthanslips.
Figure8:ReliabilityStudy
ThispatternoferrorssuggeststhatSpreadsheetProfessionalandMicrosoftErrorCheckarenotlikelytobeeffectiveonthiscorpusofspreadsheets.Actually,onlysomespreadsheetsinthecorpuswereincludedinthereliabilitystudybecausethesespreadsheetshadpreviouslybeenusedinastudyoftheabilityofSpreadsheetProfessionalandExcelErrorChecktoflagknownerrorsinthespreadsheets[3].Inthatstudy,fivestudentsappliedSpreadsheetProfessionalandExcelErrorChecktothespreadsheetsinthecorpus.Therewere88errorsinthecorpus.Foreachtool,then,therewere440errorstoassessasbeingflaggedornotflagged.OnestudentjudgedthatExcelErrorCheckcorrectlyflaggedasingleerror,forasuccessrateof0.22%.Onestudentjudgedthatspreadsheetprofessionalcorrectlytagged4errors,forasuccessrateofalmostonepercent.
Itmaybethatthiscorpuswasmisleadingbecausethestudentsubjectsmadeaverylargenumberofdomainerrorsduetoignorance.However,anotherdevelopmentstudyonalmostthesametaskfoundthatundergraduatestudentswithoutspreadsheetworkexperienceandMBAstudentswithsubstantialspreadsheetdevelopmentandtestingexperiencemadeverysimilartypesoferrors.Inaddition,inhisthree-personcodeinspectionofanoperationalspreadsheet,Hicks[10]foundthatmosterrorswerelogicerrors.
Anotherconcernisthatthestudentsdidnotdoamapanalysisinwhichtheyvisuallycouldseepatternsinthespreadsheet.However,thefirstauthorofthispaperdidaSpreadsheetProfessionalmapanalysis.Itdidnothelpinfindinganyoftheknownerrorsinthespreadsheet.
7.4ERRORSINTHEPOWELL,BAKER,ANDLAWSONAUDITOF50SPREADSHEETS
Intheirauditingstudyof50operationalspreadsheets,Powell,Baker,andLawson[24]foundaverydifferentpatternoferrors.Inthatstudy,testerscollectedandrecordedinformationaboutthespreadsheet.TheythendidmapanalysiswithSpreadsheetProfessional,ranSpreadsheetProfessionalteststoflagerrors,ranXLanalystagainstthespreadsheet,anddidacodeinspectiononremainingformulas.Ignoringhardcodingerrors,whichweclassifyasaqualitativeerror,63%ofthe
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page19
errorswerereferenceerrors,copy/pasteerrors,andomissionerrors(referencinganemptycell).Thesecorrespondtoslipsinourclassification.Only35%oftheerrorsdiscoveredwerelogicerrors,mostofwhichwouldseemtobewhatwecallplanningerrors(mistakes).Finally,dataerrorsaccountedfor2%oftheerrors.
Inaddition,Powell,Baker,andLawson[24]foundmostoftheirerrorsusingerror-detectionprograms.Mapanalysis,SpreadsheetProfessionaltests,andXLAnalystfound81%oftheerrors.Codeinspectiononlyfound18.2%oftheerrorsdiscoveredinthestudy.
OnepossibleexplanationisthattheoperationalspreadsheetsthatPowell,Baker,andLawson[24]studiedreallydidhaveverydifferenterrorpatternsthanourstudent-generatedspreadsheets.AnotherexplanationisthatPowell,Baker,andLawson[24]’scodeinspectionwasineffectivesothatfewerrorswerefoundbeyondthosefoundbyusingtheautomatedtools.
Onespecificconcernisspeedofinspection.Fagan[5]foundthatrapidcodeinspectionfindsfewererrorsthanslowercodeinspection.Insomestudies,thefall-offindetectioneffectivenessisverylarge[14].ThemedianamountoftimespentinthePowell,Baker,andLawson[24]studyontheentireauditwas195minutes.Themediannumberofformulaswas1,294.Evenifalltheauditingtimewereusedforcodeinspection,thiswouldallowonly9secondperformula.Ofcourse,mostcodeinspection(butcertainlynotall)wouldfocusonrootformulas.Therewasamedianofonly105or193unique(root)formulas,givingmoretimeperformula.Evenso,giventhecomplexityoftheprotocol,codeinspectionprobablytookarelativelysmallpercentageofthetotaltime.
AnotherspecificconcernisthateachofthePowell,Baker,andLawson[24]inspectionsusedonlyasingleinspector.Insoftwaredevelopment,codeinspectionisdoneinteams[5,14].Inspreadsheetcodeinspectionexperiments,subjectsworkingaloneonlycaughtabouthalfofallerrors[15].Whenone-personinspectionisaddedtotheinspectionrateproblem,itseemsplausiblethatthecodeinspectionpartofthestudywasinefficient.Whilesoftwareauditingtoolsmightdowellinfindingsliperrors,inadequatecodeinspectionwouldtendtoundercountlogic(planningerrors).
AthirdspecificconcernisthatinthePowell,Baker,andLawson[24]audit,theinspectorsdidnotknowtherequirementsforthespreadsheettheywereinspecting.Thiswouldmakeitmoredifficulttoidentifymistakes.Intheirlaterstudyof25spreadsheets,Powell,Baker,andLawson[22]didhavetherequirements,butthedetaileddatafromthestudyexaminedinthissectionisnotavailableforthenewerstudy.
Forthesethreereasons,webelievethatthePowell,Baker,andLawson[24]studyprobablyundercountedplanningerrors.
8. PERSPECTIVE
8.1CHANGESINTHETAXONOMY
ThispaperhasrevisedandexpandedthePankoandHalverson[18]taxonomyofspreadsheeterrors.Thepurposeoftheearlytaxonomywastosupportquantitativeresearchstudiestodemonstratethatquantitativespreadsheeterrorsarefrequent,thatquantitativespreadsheeterrorsaredifficult
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page20
todetect,andthatmanyspreadsheeterrorsaresignificant.Figure7showstherevisedtaxonomy.Thistaxonomymakesanumberofnewdistinctions.
Ø First,thereisadistinctionbetweenblameless(innocent)errorsandculpableviolationsoflawsorrequiredcorporatepractices.
Ø Second,thedistinctionbetweenlogic,mechanical,andomissionerrorshasbeenreplacedbythemorecommondistinctionbetweendomainandspreadsheetexpressionplanningerrors(mistakes)ontheonehandandimplementationerrors(slipsandlapses)ontheotherhand[13,29].Planningerrorsareincorrectintentions.Implementationerrorsaretheincorrectimplementationofplans.
Ø Amongplanningerrors,domainplanningerrorsoccurwhenthedevelopermakesamistakeintheknowledgedomainofthemodel(finance,ecology,physics,etc.).Spreadsheetexpressionplanningerrorsoccurwhenthedeveloperplansanincorrectspreadsheetexpressionofthedomainalgorithm.
Ø Logicerrorsbecomemistakes,whilemechanicalerrorsaredividedintoslipsandlapses.Slipsaresensory-motorerrors,suchastypingandpointingerrors.Incontrast,lapsesarememoryerrors[13].
8.2RELATIVEERRORFREQUENCY
Weneedresearchtoassesstherelativefrequencyofvarioustypesoferrors.Inourcorpus,forwhichwehadunambiguousquantitativeerrordata,thatmosterrorswereplanningerrors,andmostoftheseweredomainplanningerrors.Amongtheexecutionerrors,morethanhalfwerelapsesoccurringinthedeveloper’shead.Powell,Baker,andLawson[24]foundverydifferentthingsintheirexaminationof50operationalspreadsheets,althoughwehaveconcernsabouttheabilityoftheirmethodologytodetectplanningerrorsandperhapslapses.
8.3TIMETOCHANGEOURRESEARCHFOCUS
Today,theideathatsignificantquantitativeerrorsarefrequenthasbeenbroadlyaccepted.Inanycase,peoplewhostillrejectthatexperimentalandfieldevidenceregardingthemarenotlikelytohavetheiropinionschangedbyfurtherquantitativeresearch.Itisnowtimetoshiftourfocustowardqualitativeerrors,whichmaybefarmorecommonthanquantitativeerrors,andidentifyingthelargenumberofdifferenttypesoferrorsthatarepossibleindifferentlifecyclestagesandbypeoplewithdifferentrolestoplay.
REFERENCES
[1] G.T.AllisonandP.Zelikow,EssenceofDecision:ExplainingtheCubanMissileCrisis,2ndEdition(Paperback)(LongmanPublishers,EnglewoodCliffs,NJ.,1999).
[2] C.M.Allwood,ErrorDetectionProcessesinStatisticalProblemSolving,CognitiveScience,8(4),(1984).
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page21
[3] S.AurigemmaandR.R.Panko,(2009).“ExperimentontheAccuracyofStaticTesting(Auditing)ProgramsinDetectingSpreadsheetErrors,”presentationattheworkshop“Spreadsheets:TheDarkMatterofIT”atTheForty-SecondHawaiiInternationalConferenceonSystemSciences,Waikoloa,Hawaii,January5,2009.
[4] B.Beizer,SoftwareTestingTechniques.2nded.(NewYork,VanNostrand,1990).
[5] M.E.Fagan,DesignandCodeInspectionstoReduceErrorsinProgramDevelopment,IBMSystemsJournal,15(3),(1976).
[6] L.A.FlowerandJ.R.Hayes,“TheDynamicsofComposing:MakingPlansandJugglingConstraints,”CognitiveProcessesinWriting.Eds.L.W.Gregg&E.R.Steinberg.Hillsdale,NJ:LawrenceErlbaumAssociates.198031-50.
[7] D.F.Galletta,K.S.Hartzel,S.Johnson,andJ.L.Joseph,SpreadsheetPresentationandErrorDetection:AnExperimentalStudy,JournalofManagementInformationSystems13(2)(Winter1997).
[8] D.F.Galletta,D.Abraham,M.ElLouadi,W.Lekse,Y.A.Pollailis,andJ.L.Sampler,AnEmpiricalStudyofSpreadsheetError-FindingPerformance.JournalofAccounting,Management,andInformationTechnology,3(2)(1993April-June).
[9] T.A.GrossmanandO.Özlük,(2003).“ResearchStrategyandScopingSurveyonResearchPractices,”ProceedingsofEuSpRIG2003,EuropeanSpreadsheetRisksInterestGroup,July24-25,TrinityCollege,Dublin,Ireland,pp.23-32.
[10] L.Hicks,NYNEX,personalcommunicationwiththefirstauthorviaelectronicmail,June21,1995.
[11] H.HoweandM.Simkin,MarkF.(2006,January),FactorsAffectingtheAbilitytoDetectSpreadsheetErrors,DecisionSciencesJournalofInnovativeEducation,4(1)(2006,January).Not2008?
[12] T.C.Jones,ProgrammingProductivity(McGraw-Hill,NewYork,1986).
[13] D.A.Norman,CategorizationofActionSlips,PsychologicalReview,88(1981).
[14] R.R.Panko,HumanErrorWebsite.(http://panko.shilder.hawaii.edu/panko/HumanErr/).Honolulu,HI:UniversityofHawai`i(2009a).
[15] R.R.Panko,SpreadsheetResearch(SSR)Website.(http://panko.shilder.hawaii.edu/panko/ssr/).Honolulu,HI:UniversityofHawai`i(2009b).
[16] R.R.Panko,ApplyingCodeInspectiontoSpreadsheetTesting,JournalofManagementInformationSystems,16(2)(1999,Fall).
[17] R.R.Panko,TwoExperimentsinReducingOverconfidenceinSpreadsheetDevelopment,JournalofOrganizationalandEndUserComputing19(1)(2007,January-March).
[18] R.R.PankoandR.P.Halverson,Jr.,AnExperimentinCollaborativeSpreadsheetDevelopment,JournaloftheAssociationforInformationSystems2(4)(2001,July).
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page22
[19] R.R.PankoandR.P.Halverson,Jr.,AreTwoHeadsBetterthanOne?(AtReducingErrorsinSpreadsheetModeling),OfficeSystemsResearchJournal15(1)(1997,Spring).
[20] R.R.PankoandR.P.Halverson,Jr.,“SpreadsheetsonTrial:AFrameworkforResearchonSpreadsheetRisks,”ProceedingsoftheTwenty-NinthHawaiiInternationalConferenceonSystemSciences,VolumeII,Kihei,Maui,January,1996,pp.326-335.
[21] R.R.PankoandR.H.Sprague,Jr.,HittingtheWall:ErrorsinDevelopingandCodeInspectinga“Simple”SpreadsheetModel,DecisionSupportSystems,22(4)(1998,April).
[22] S.G.Powell,K.R.Baker,andB.Lawson,(2007,July).“ImpactofErrorsonOperationalSpreadsheets,”ProceedingsoftheEuropeanSpreadsheetRisksInterestGroup,EuSpRIG2007Conference,UniversityofGreenwich,London,57-68.
[23] S.G.Powell,K.R.Baker,andB.Lawson,ACriticalReviewoftheLiteratureonSpreadsheetErrors,DecisionSupportSystems46(2008a).
[24] S.G.Powell,K.R.Baker,andB.Lawson,AnAuditingProtocolforSpreadsheetModels,Information&Management45(2008b).
[25] S.G.Powell,K.R.Baker,andB.Lawson,ErrorsinOperationalSpreadsheets,JournalofOrganizationalandEndUserComputing,21(3)(2009,July-September).
[26] Rajalingham,Kamalasen;Chadwick,DavidR.;&Knight,Brian.(2000,July17-18).“ClassificationofSpreadsheetErrors,”SymposiumProceedingsEuSpRIG2000,UniversityofGreenwich,London,UK,EuropeanSpreadsheetRisksInterestGroup,pp.23-34.
[27] K.Rajalingham,(2005,July).“ARevisedClassificationofSpreadsheetErrors,”Proceedingsofthe2005EuropeanSpreadsheetRisksInterestGroup,EuSpRIG2005,Greenwich,London,185-199.
[28] J.Rasmussen,Skills,Rules,Knowledge:Signals,SignsandSymbolsandOtherDistractionsinHumanPerformanceModels,IEEETransactions:Systems,Man,andCybernetics,SMC-13(1983).
[29] J.T.Reason,HumanError,(CambridgeUniversityPress,Cambridge,England,1990).
[30] J.T.Reason,andK.Mycielska,Absent-Minded?ThePsychologyofMentalLapsesandEverydayErrors(PrenticeHall,EnglewoodCliffs,N.J.,1982).
[31] J.W.SendersandN.P.Moray,HumanError:Cause,Prediction,andReduction(LawrenceErlbaum,Hillsdale,NH,1991).
[32] T.S.H.TeoandM.Tan,SpreadsheetDevelopmentand“What-If”Analysis:QuantitativeversusQualitativeErrors,Accounting,ManagementandInformationTechnologies,9(1999).
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page23
FIGURES
Figure1:MistakesversusSlipsandLapses
Stage of Error Type of Error
Error in Planning Mistake Logic or mathematical error, etc.
Error in Execution Slip Sensory-motor error
Lapse Error cause by memory overload
Sources: Norman [13]; Reason [29].
Figure2:ContextPyramidinWriting
Figure3:PankoandHalversonSpreadsheetRisksResearchCube
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page24
Figure4:PankoandHalverson1996TaxonomyofDevelopmentandTestingErrorTypes
Figure5:ErrorDensitybyLifeCycleStage
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page25
Figure6:TypesofCellErrorRates(CERs)
Acronym Denominator Use
CERV Value cells (numbers and formulas)
Cell error rates have traditionally been measured this way
CERF Formula cells Focuses on formula error rates, which usually are much higher than value error rates
CERN Number cells Good for looking at input errors
CERT Text cells Good for looking at documentation
CERA All nonempty cells (label and value cells)
Not very useful, but some studies use it
Figure7:RevisedTaxonomyofSpreadsheetErrors
Figure8:ReliabilityStudy
Number Percent
Total Errors 88
Total Errors Jointly Classified* 85 100%
Planning Errors (Mistakes) 70 82%
Domain 69 81%
Spreadsheet Expression 1 1%
Execution Errors 15 18%
Slip 6 7%
RevisingthePanko–HalversonTaxonomyofSpreadsheetErrors
Page26
Lapse 9 11%
top related