Documentation/Calc Functions/ERROR.TYPE

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

ERROR.TYPE

Category:

Spreadsheet

Summary:

Generates a code number representing the type of error found in the argument.

Syntax:

ERROR.TYPE(Expression)

Returns:

Returns a positive integer in the range [1, 7] if the argument represents one of seven defined error conditions. Returns the #N/A error if the argument does not represent an error, or represents an error outside the set of seven common errors that have been mapped to error types.

The mapping of Calc’s errors to the seven error types is given by the following table.

Mapping errors to error types
Error Returns
Err:511 (variable missing) 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
No error, or error not covered by list above #N/A

Arguments:

Expression is an expression, or a reference to a cell containing an expression, whose error type is to be determined.

Additional details:

  • The integer error type value may help you generate bespoke error messages that are helpful to the intended users of your spreadsheet.
  • If the ERROR.TYPE function is used as condition of an IF function call and the ERROR.TYPE call returns #N/A, the IF function returns #N/A as well. Use ISERROR to avoid this, as shown in the final example below.

Examples:

Formula Description Returns
=ERROR.TYPE(E1) where cell E1 contains the formula =SQRT(). Since the SQRT function expects a single numeric argument, cell E1 contains the Err:511 error (variable missing). Hence, ERROR.TYPE returns the value 1. 1
=ERROR.TYPE(#DIV/0!) Here the function returns 2, the type of the #DIV/0! error. 2
=ERROR.TYPE(#VALUE!) Here the function returns 3, the type of the #VALUE! error. 3
=ERROR.TYPE(#REF!) Here the function returns 4, the type of the #REF! error. 4
=ERROR.TYPE(#NAME?) Here the function returns 5, the type of the #NAME? error. 5
=ERROR.TYPE(#NUM!) Here the function returns 6, the type of the #NUM! error. 6
=ERROR.TYPE(#N/A) Here the function returns 7, the type of the #N/A error. The formula =ERROR.TYPE(NA()) also returns 7. 7
=ERROR.TYPE(E2) where cell E2 contains the formula =SQRT(-2). Since the SQRT function expects a non-negative argument, cell E2 contains the Err:502 error (invalid argument). However, as this error is not one of those allocated to a specific error type, ERROR.TYPE returns the #N/A error. #N/A
=ERROR.TYPE(E3) where cell E3 contains the formula =PI(). Since there is no error in the specified cell, ERROR.TYPE returns the #N/A error. #N/A
=IF(ISERROR(A1/A2); IF(ERROR.TYPE(A1/A2)=2; "*** Error - unable to divide by 0. ***"); A1/A2) where cells A1 and A2 contain the numbers 10 and 2 respectively. Here the formula is designed to divide the contents of cell A1 by the contents of cell A2. However, the formula uses the IF and ISERROR functions to ensure that if cell A2 contains 0, the division is not carried out and a user-friendly error message is displayed instead. With the example as described, the formula returns the value 5. However, if the number in cell A2 is changed to 0, then the error message "*** Error - unable to divide by 0. ***" is displayed. 5

Related LibreOffice functions:

ERRORTYPE

IF

ISERROR

NA

ODF standard:

Section 6.13.11, part 2

Equivalent Excel functions:

ERROR.TYPE