Site icon Kaizen.Personal computer work.

Circumstances in which an error occurs and the decision functions.(Microsoft Excel)

Japanese version.

Excel cells can contain errors.
If an error cell is incorporated into a formula, the formula will not work properly.

Types of errors and conditions under which they occur.

ErrorOverviewExample
#NULL!Cell range is invalid.Incorrect cell range specification, e.g., B3:B4 as B3 B4.
#DIV/0!division by zero.Division with zero as the denominator, such as 1/0, is occurring.
#VALUE!Data type incorrect.Character is mixed in the calculation.
A numerical value is specified for an argument that should be a string, etc.
#REF!cell reference error.If the cell to which the formula refers is deleted or otherwise no longer available for cell referencing.
#NAME?Name Incorrect.Function name or cell range name does not exist.
#NUM!Numerical Fraud.Numerical value is too large or too small.
or out of the appropriate range as a function specification.
#N/ANo search resultsThere is no corresponding search result for XLOOKUP or VLOOKUP functions.
Can be generated by NA function.
#GETTING_DATAData acquisition in progress.In CUBESETCOUNT function. Occurred temporarily during data acquisition.
Resolved after data acquisition.
####Cell width shortageInsufficient cell width for number of digits displayed for numbers and dates.
Not determined as an error.
#SPILLSpill cannot be auto-expanded.A value already exists in the cell that is automatically expanded by the spill.

Error-determining functions.

There are four functions to determine errors.

The usage is all the same. Specify the cell or value to be judged as an argument and obtain the judgment result.

Error and error judgment function execution result correspondence table.

ErrorISERRORISERRISNAERROR.TYPE
#NULL!TRUETRUEFALSE1
#DIV/0!TRUETRUEFALSE2
#VALUE!TRUETRUEFALSE3
#REF!TRUETRUEFALSE4
#NAME?TRUETRUEFALSE5
#NUM!TRUETRUEFALSE6
#N/ATRUEFALSETRUE7
####FALSEFALSEFALSE#N/A
#SPILLTRUETRUEFALSE9
No ErrorFALSEFALSEFALSE#N/A

The #SPILL decision is valid only for cells with spill formulas.

Dealing with Errors.

IF Function

A typical example is to incorporate a decision function into an IF function to set the result of the formula in the event of an error.

=IF(ISERROR(cell), "Error", "Not error")

IFERROR and IFNA functions

The IFERROR function may be used to set only the result in case of error.

=IFERROR(Formula, Description in case of error)

For example, if you are performing division where the denominator can be zero, it is safe to include an IFERROR function.

There is also an IFNA function that only works on #N/A errors.

It is effective to use IFNA functions for XLOOKUP and VLOOKUP functions.

---

Links.

Microsoft Excel Functions Information

Examples of Use

How to reverse text.

How to extract only numbers from text.

How to find multiple texts with the FIND function.

Exit mobile version