Documentation/Calc Functions/ERROR.TYPE

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    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)
    [tdf#154167][tdf#154915] #NULL!     		1
    #DIV/0! 2
    #VALUE! 3
    #REF! 4
    #NAME? 5
    #NUM! 6
    #N/A 7
    No error, or
    error not covered by the list above     		#N/A
    • Until LibreOffice 7.5 (included), when Expression resulted in Err:511, the result of ERROR.TYPE was 1. This result was not in accordance with the ODF standard. This was corrected for LibreOffice 7.6. See the examples below.
    • Until LibreOffice 7.5 (included), there was no formula resulting in the #NULL! error. Instead, the "no intersection reference" error case generated a #REF! error code. This was corrected for LibreOffice 7.6. See the examples below.

    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.
    • ERROR.TYPE does not propagate errors, in contrast to most other functions.
    • Other spreadsheet tools might generate different error codes than Calc for the same formula. For instance, Calc might generate some of its own "Err:nnn" error codes, whereas other implementations might generate the #NUM! error for the same formulas. Take care therefore if you need compatibility.
    • For formulas generating "Err:nnn" error codes, Calc's ERROR.TYPE returns #N/A, the same result as for non-errors. Other implementations differentiate between these two cases, generating different results. Take care therefore if you need compatibility.
    • Other implementations accept error codes typed-in directly as strings for Expression, generating the same ERROR.TYPE result as an actual/real Expression. This helps in testing formulas involving ERROR.TYPE. LibreOffice Calc does not fully support this usage of Expression. In other words, Expression is not expected to be an error code string by itself, but rather the result of some expression, or a reference to an expression.
    • Other spreadsheet tools generate numeric results other than [1...7] for ERROR.TYPE. Take care therefore if you need compatibility.
    • If the ERROR.TYPE function is used as condition of an IF function call, and the ERROR.TYPE call returns #N/A, then the IF function returns #N/A as well. Use ISERROR to avoid this, as shown in the examples below.

    Examples:

    The following table is used as data (showing results and formulas) for the examples below.

    Data for examples
    A B C D E
    1 Returns Description Formula
    2 Err:511 Missing argument =SQRT()
    3 Err:502 =SQRT(-2)
    4 3.14 π =PI()
    5 10
    6 2 1 4
    7 5 0
    8 3 A5:D6 intersect with A6:B7 [1] =SUM(A5:D6!A6:B7)
    9 #NULL! No intersection =SUM(A5:A6!B6:B7)
    10 #REF! ⇐ copy =B10 =#REF!10
    1. ↑ The space character is used as the intersection operator in some spreadsheet tools.
    Formula Description Returns
    =ERROR.TYPE(A2)

    =ERROR.TYPE(SQRT())

    		Err:511 Missing argument 1 (LO < 7.6) #N/A
    =ERROR.TYPE(A9)

    =ERROR.TYPE(SUM(A5:A6!B6:B7))

    		#NULL! No intersection 1
    =ERROR.TYPE(B6/D7)

    =ERROR.TYPE(1/0)

    		#DIV/0! Division by zero 2
    =ERROR.TYPE( "string" + 0 ) #VALUE! No (valid) value 3
    =ERROR.TYPE(A10) #REF! Not a valid reference 4
    =ERROR.TYPE(unknown_name) #NAME? Invalid name 5
    =ERROR.TYPE(1000^500) #NUM! Overflow / invalid numeric value 6
    =ERROR.TYPE(NA()) #N/A Value not available / not found 7
    =ERROR.TYPE(A3)

    =ERROR.TYPE(SQRT(-2))

    A negative number is an invalid argument for SQRT. Err:502 is not allocated to a specific error type.

    		#N/A
    =ERROR.TYPE(D6) There is no error in the specified cell. #N/A

    =IF(ISERROR(A5/A6);   IF(ERROR.TYPE(A5/A6)=2;   "*** Unable to divide by 0 ***");   A5/A6)

    If the division can be carried out, its result is displayed. If the value of the denominator is zero (or empty), a user-friendly error message is displayed instead. Other potential errors are not caught by this formula/example.

    		10/2=5

    =IF(ISERROR(A5/D7);   IF(ERROR.TYPE(A5/D7)=2;   "*** Unable to divide by 0 ***");   A5/D7)

    		*** Unable to divide by 0 ***

    Related LibreOffice functions:

    ERRORTYPE

    IF

    IFERROR

    IFNA

    ISERR

    ISERROR

    ISNA

    ISNUMBER

    ISTEXT

    NA

    ODF standard:

    Section 6.13.11, part 2

    Related (or similar) Excel functions:

    ERROR.TYPE


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/ERROR.TYPE&oldid=713385"