Excel cells can contain errors.
If an error cell is incorporated into a formula, the formula will not work properly.
- 1. Types of errors and conditions under which they occur.
- 2. Error-determining functions.
- 3. Dealing with Errors.
- 4. Links.
Types of errors and conditions under which they occur.
|#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/A||No search results||There is no corresponding search result for XLOOKUP or VLOOKUP functions.|
Can be generated by NA function.
|#GETTING_DATA||Data acquisition in progress.||In CUBESETCOUNT function. Occurred temporarily during data acquisition.|
Resolved after data acquisition.
|####||Cell width shortage||Insufficient cell width for number of digits displayed for numbers and dates.|
Not determined as an error.
|#SPILL||Spill cannot be auto-expanded.||A value already exists in the cell that is automatically expanded by the spill.|
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.
The ＃SPILL decision is valid only for cells with spill formulas.
Dealing with Errors.
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.