Error value

Definition

Whenthespreadsheetsoftware(suchasExcel)cannotcalculatetheformulaforacell,thespreadsheetsoftwaredisplaysthevalueinthiscell.Itspurposeistopromptcertainplaces——Eithertheformulaitself,orthecellreferencedbytheformula,ispreventingtheformulafromreturningthecorrectvalue.

Theerrorvaluestartswith#,followedbytheuppercaseerrormessage.

Results

Theworstresultoferrorvalues​​isthattheycanbetransmittedtootherformulas.Ifaformulareturnsanerrorvaluetoonecell,anotherformulainanothercellwillreferenceThevaluecalculatedbythefirstformula,thesecondformulareturnsthesameerrorvalue,andispasseddowninorder.

Errorflag

Whenacellcalculatesanerrorvalue,Anerrorindicator(usuallyasmalldarkgreentriangle)willappearintheupperleftcorner.Whenthiscellisselected,apromptbuttonwillappearnexttoit.Whenthemouseismovedtothebutton,acorrespondingerrormessagewillpopup.Afterclicking,amenuwillpopupwiththeerrornameandotherrelatedinformationdisplayedonit.

Whenusing"EvaluationofFormulas",inthe"EvaluationofFormulas"dialogbox,whentheevaluationreachestheunderlinedpart(Tobeevaluated)Whenanerroroccurs,thedialogboxwillprompt"anerrorwilloccurinthenextevaluation"atthebottom,sothattheuserknowswheretheerroroccurredanditscause.

Whenusing"trackerror"Whenthetime,atrackingarrowwillappearonthecurrentworksheet.Thearrowpointsfromthecellthatprovidesthedatatoothercells,usuallyblue;ifthecellcontainsanerrorvalue,thetrackingarrowisred.

Excelerrorvalue

#DIV/0!

Errorname:"dividebyzero"error

errormessage:formulaorfunctioniszerooremptycellDivision.

Thereasonandsolutionoftheerror:Whenthenumberisdividedby0,anerroroccurs.

One,theformulacontainsanobviousdivisionbyzero,suchas5/0.

Changethedivisortoanon-zeronumber.

Second,useareferencetoablankcelloracellcontainingzeroasthedivisor.

Note:IfyouoperateIfthenumberisablankcell,Excelwillinterpretitas0.

Changethecellreferencetoanothercell.

Enteranon-zeronumberinthecellThevalueisusedasthedivisor.

Youcanenterthevalue#N/Ainthecellreferencedasthedivisor,whichwillchangetheresultoftheformulafrom#DIV/0!to#N/A,indicatingthatthedivisorisnotavailableUse.

UsetheIFfunctiontopreventincorrectvalues​​frombeingdisplayed.Forexample,iftheformulathatproducestheerroris=A5/B5,youcanuse=IF(B5=0,"",A5/B5).,Twoquotationmarksrepresentanemptytextstring.

#N/A

Errorname:"Valueisnotavailable"error

Errormessage:Avalueisnotavailablefortheformulaorfunction.

Thereasonandsolutionoftheerror:Whenthevaluepairfunctionorformulaisnotavailable,anerrorwilloccur.

One,missingdata,replaceitwith#N/AorNA().

Note:Itcanbeinthedata.Enter#N/Ainthecellused.Whentheformulareferstothesecells,itwillnotbecalculated,but#N/Awillbereturned.

Second,thelookup_valueparameterofthelookupfunctionisassignedwithnoAppropriatevalue.

Pleasemakesurethatthetypeofthelookup_valueparametervalueiscorrect.Forexample,thevalueorcellshouldbereferenced,nottherange.

Three,intheunsorteddatatable,UsetheVLOOKUP,HLOOKUP,orMATCHfunctiontolocatethevalue.

Bydefault,thedatatableinwhichthesefunctionsareusedtofindinformationmustbesortedinascendingorder,buttheVLOOKUPandHLOOKUPworksheetfunctionsalsoincludearange_lookupParameter,allowsthefunctiontofindtheexactmatchingvalueintheunsorteddatatable.Tofindtheexactmatchingvalue,pleasesettherange_lookupparametertoFALSE.

TheMATCHfunctioncontainsthematch_typeparameter,whichspecifiesthesortedvalueTheorderofthelisttofindthematchingresult.Ifthefunctioncannotfindthematchingresult,pleasechangethematch_typeparameter.Tofindtheexactmatchresult,pleasesetthematch_typeparameterto0.

Four,useinthearrayformulaThenumberofrowsorcolumnsoftheparameterofisinconsistentwiththenumberofrowsorcolumnsoftheareacontainingthearrayformula.

Ifyouwanttoenteranarrayformulainmultiplecells,pleaseconfirmtheareareferencedbytheformulaandthearrayTheareaoccupiedbytheformulahasthesamenumberofrowsandcolumns,orreducesthenumberofcellscontainingthearrayformula.Forexample,ifthearrayformulaisenteredina15-rowhigharea(C1:C15),buttheformularefersto10rowsInthehigharea(A1:A10),#N/AwillbedisplayedintheareaC11:C15.Tocorrectthiserror,pleaseentertheformulaintoasmallerarea(e.g.C1:C10),orchangetheformulareferenceMakeithavethesamenumberofrowsastheareawheretheformulais(forexample,A1:A15).

Five.Oneormorenecessaryparametersaremissingintheinternalfunctionorthecustomworksheetfunction.

Enterallparametersinthefunction.

Six.Thecustomworksheetfunctionusedisnotavailable.

ConfirmthattheworkbookcontainingthisworksheetfunctionhasbeenopenedandThefunctionisworkingproperly.

#VALUE!

Errorname:Valueerror

Errormessage:Avalueusedintheformulaisthewrongdatatype.

Thereasonfortheerrorandthesolution:Thiserroroccurswhentheparameteroroperandtypeusedisthewrongtype.

1.Whenaformularequiresanumberoralogicalvalue(suchasTRUE/FALSE),buttextisentered.

Excelcannotconvertthetexttothecorrectdatatype.ConfirmtheoperatororoperatorrequiredbytheformulaorfunctionTheparametersarecorrectandthecellreferencedbytheformulacontainsavalidvalue.Forexample,ifcellA5containsanumberandcellA6containsthetext"Notavailable",theformula=A5/A6willreturntheerrorvalue#VALUE!.

Two.Enteroreditthearrayformula,andthenpressEnter.

Selectthecellorrangeofcellsthatcontainsthearrayformula,pressF2toedittheformula,andthenpressCtrl+Shift+Enter.

3.Enterthecellreference,formulaorfunctionasanarrayconstant.

Confirmthatthearrayconstantisnotacellreference,formulaorfunction.

Four.Aregionisprovidedforoperatorsorfunctionsthatrequireasinglevalue(notaregion).

Changetheregiontoasinglevalue.

ChangethenumericregionsothatitcontainsThedataroworcolumnwheretheformulaislocated.

5.Aninvalidmatrixis​​usedinamatrixworksheetfunction.

Confirmthatthedimensionofthematrixis​​correctforthematrixparameters.

#NAME

Errorname:"Invalidname"error

Errormessage:Theformulacontainsunrecognizabletext.

ErrorReasonsandsolutions:WhenExceldoesnotrecognizethetextintheformula,anerroroccurs.

First,usethefunctionofthe"AnalysisToolLibrary"add-inpart,butnotloadtheadd-in.

Installandloadthe"AnalysisToolLibrary"add-in.

Second,useanamethatdoesnotexist.

Confirmthatthenameuseddoesexist.Onthe"Insert"menu,pointto"Name",thenclick"Define".Ifthedesirednameisnotlisted,pleaseusethe"Define"commandtoaddthecorrespondingname.

Three,thenameisspelledincorrectly.

Verifyspelling.Selectthenameintheformulabar,pressF3,clickthenameyouwanttouse,andclick"OK".

Four.Usetheflagintheformula,butthereisnoallowedflag.

Allowtheuseofflags.Onthe"Tools"menu,click"Options",andthenclickthe"Recalculation"tab.Under"Workbookoptions",selectthe"Acceptformulaflags"checkboxBox.

5.Thefunctionnameisspelledincorrectly.

Correctthespelling.Insertthecorrectfunctionnameintheformulabyclicking"Function"onthe"Insert"menu.

Six.Doublequotationmarksarenotusedwhenenteringtextinformulas.

(Excelinterpretsitasanameandignoresyouroriginalintentiontouseitastext.)

Enclosethetextintheformulaindoublequotes.Forexample,Thefollowingformulaconnectsthetext"Thetotalamountis"withthevalueincellB50:

="Thetotalamountis"&B50

7.MissingThecolon(:)intheareareference.

Confirmthatalltheareareferencesusedintheformulausethecolon(:);forexample,SUM(A1:C10).

8.Refertoanotherworksheetthatisnotenclosedinsinglequotes.

Iftheformulareferstovalues​​orcellsinotherworksheetsorworkbooks,andthenamesofthoseworkbooksorworksheetscontainNon-lettercharactersorspaces,thenyoumustenclosethischaracterinsinglequotes(').

#NULL!

Errorname:"Nullvalue"error

Errormessage:Therangesintheformuladonotintersect.

Thereasonfortheerrorandthesolution:Thiserroroccurswhentheintersectionoftwoareasthatdonotintersectisspecified.UseaspacetoindicatetworeferencesIntersectionoperatorbetweencells.

One.Anincorrectrangeoperatorisused.

Torefertoacontinuouscellrange,pleaseuseacolon(:)toseparateReferstothefirstandlastcellintherange.Forexample,SUM(A1:A10)referstotherangefromcellA1tocellA10.ItincludestwocellsA1andA10.

Ifyouwanttorefertotwoareasthatarenotintersecting,usetheunionoperator,thatis,thecomma(,).Forexample,iftheformulasumstwoareas,makesuretoseparatethetwoareaswithacomma(SUM(A1:A10,C1:C10)).

Second,theareasdonotintersect.

Changethereferencetomakeitintersect.

#NUM!

Errorname:Numbererror

Errormessage:Thereisaproblemwithanumberusedintheformula.

Thereasonfortheerrorandthesolution:Whenaninvalidnumbervalueisusedinaformulaorfunction,Thiskindoferroroccurs.

One.Anunacceptableparameterisusedinafunctionthatrequiresanumericparameter.

Makesurethattheparameterusedinthefunctionisanumber.Forexample,evenifyouneedtoenterThevalueofis$1000,and1000shouldalsobeenteredintheformula.

Second,aniterativecalculationfunction,suchasIRRorRATE,isused,andthefunctioncannotgetavalidresult.

Theworksheetfunctionsusedifferentinitialvalues.

ChangethenumberofExceliterationformulas.

ThenumbergeneratedbytheenteredformulaistoolargeortoosmalltoberepresentedinExcel.

Changetheformulasothattheresultisbetween-1E+307and1E+307.

#REF!

Errorname:"Invalidcellreference"error

Errormessage:Movingordeletingacellcausesaninvalidcellreference,orthefunctionreturnsareferenceerrormessage.

Thereasonfortheerrorandthesolution:Thisappearswhenthecellreferenceisinvalid.Kindoferror.

1.Deletethecellsreferencedbyotherformulas,orpastethemovedcellsontothecellsreferencedbyotherformulas.

Changetheformula,orClick"Undo"immediatelyafterdeletingorpastingthecelltorestorethecellintheworksheet.

Second,theprogrampointedtobythelinkisnotrunning.

Starttheprogram.

Three,linktotheunavailabledynamicdataexchange(DDE)themesuchas"system".

MakesurethatthecorrectDDEthemeisused.

Processingmethod

Onceanerrorvalueappearsinthecell,theusermustknowwhatcausedtheerrorandedittheformulaintheabovemethodtoeliminatetheerror.

Theerrorpromptbuttonnexttothecellwillpopupamenu.Thenselect"showcalculationsteps"or"trackerror"touse"formulaevaluation"or"trackerror"tofindoutthecauseandlocationoftheerror(oruse"formulaevaluation""Toolbarorusethetoolinthemenubar==>Formulareview),whenthe"Evaluationoftheformula"prompts"thenextevaluationwillcauseanerror"atthebottom,youneedtoseetheerrorandedittheformula;whentrackingerrors,Thetrackingarrowwillbedisplayedandpointtothecellreferencedbythecurrentformula.Double-clickthetrackingarrowtomovebetweenthecellsinvolvedintheerror.Usethe"trackerror"functiontofindoutthecauseoftheerrorandedittheformulacorrectly.Soastoeliminatetheerror.

Youcanalsousectrl+`toviewtheoriginalformulatoseewheretheerroroccurredandeliminatetheerror.Butafterediting,youmustpressctrl+`toreturntheformularesult.