ERROR.TYPE

What it does

ERROR.TYPE(error_val)

ERROR.TYPE(error_val) maps an error value to a number identifying its code, and returns #N/A for anything that is not an error.

Microsoft's table, verbatim:

Two features of this table are worth pausing on.

The last row is a deliberate confusion. ERROR.TYPE(5) returns #N/A, and ERROR.TYPE(#N/A) returns 7. The function answers "this is not an error" by producing an error, which means the naive test ERROR.TYPE(x) > 0 cannot distinguish "no error" from "the error is #N/A". Guard ERROR.TYPE with ISERROR or ISNA before trusting its result — Microsoft's own description recommends exactly that pattern: "You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value."

#GETTING_DATA has a code but no ISERROR sentence. Microsoft's ISERROR row enumerates seven codes and stops at #N/A; this table adds an eighth. The two documentation pages were written at different times and the Handbook does not know which is currently true of the running product for that code.

Arguments

error_val — required, exactly one. The published signature is ERROR.TYPE(error_val).

Microsoft's argument description: "The error value whose identifying number you want to find. Although error_val can be the actual error value, it will usually be a reference to a cell containing a formula that you want to test."

That sentence describes the normal use. ERROR.TYPE(A1) where A1 holds a broken formula is how the function is meant to be called; ERROR.TYPE(#DIV/0!) with a literal error is legal and mostly appears in tests.

Try it

Quirks

Result and edge cases

Returns a Number for an error input (1–8, or 0 for #BUSY!; see FN-ERROR.TYPE-001), or the Error #N/A for everything else.

• Errors do not propagate through this function. Like the IS classifiers, ERROR.TYPE inspects rather than forwards, and that is what makes it usable at all.
• #N/A in, 7 out. The one input for which the function's success value and its failure value are related — and the reason the guard is needed.
• Empty, text, numbers, logicals: #N/A. All "anything else".

Errors

ERROR.TYPE returns #N/A as its ordinary "not an error" answer — that is a result, not a failure.

Note that the function can never return #VALUE! from a value — every value has an answer.

How to implement

Notes for implementers

1. Emit the published numbers literally. The codes are Excel's, not an internal enum's ordinals, and the mapping must not drift if the internal enum is reordered.
2. The "anything else" row must return #N/A, not 0 and not an error of its own. The temptation to return 0 for "no error" is strong and wrong.
3. Decide the array question explicitly. Whole-argument classification and elementwise lifting are both defensible; the documentation settles neither, so whichever is chosen should be recorded as a decision awaiting evidence.
4. Any error code the engine can produce but cannot name must fall to #N/A deliberately. Silently returning a made-up number for #SPILL! would be worse than the documented gap. #BUSY! is the recorded exception: Excel gives it 0 (FN-ERROR.TYPE-001).
5. The error must reach the kernel rather than being short-circuited by the dispatch layer.

Rust code

unary(ERROR_TYPE, "ERROR.TYPE", ERROR_VAL, error_type)

/// ERROR.TYPE: the number of an error, and #N/A for any other value. Microsoft's page lists #NULL!
/// to #GETTING_DATA as 1 to 8 and anything else as #N/A; Excel gives #SPILL! 9, #FIELD! 13, #CALC!
/// 14 and #BUSY! 0 instead. The number of #BLOCKED! and #CONNECT! is unknown, so the call is
/// refused as not modelled (see the module documentation).
fn error_type(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(ERROR_TYPE, services);
    let step = scalar(&site, &arguments[0]).and_then(|core| {
        use WorksheetErrorCode as W;
        let number = match core {
            CoreRef::Error(code) => match code {
                W::Null => 1.0,
                W::Div0 => 2.0,
                W::Value => 3.0,
                W::Ref => 4.0,
                W::Name => 5.0,
                W::Num => 6.0,
                W::NA => 7.0,
                W::GettingData => 8.0,
                W::Spill => 9.0,
                W::Field => 13.0,
                W::Calc => 14.0,
                W::Busy => 0.0,
                W::Connect => 10.0,
                W::Blocked => 11.0,
            },
            _ => return Err(Exit::Error(W::NA)),
        };
        Ok(CalcValue::number(number))
    });
    Site::finish(step)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• ISERROR / ISERR / ISNA answer whether; ERROR.TYPE answers which. The idiomatic pairing is IF(ISERROR(x), CHOOSE(ERROR.TYPE(x), "…", "…", …), x), and the guard is not optional for the reason given above.
• TYPE returns 16 for every error without distinguishing them; ERROR.TYPE is its refinement.
• IFERROR / IFNA replace the whole diagnostic pattern when you only want a fallback rather than a message. ERROR.TYPE survives because a fallback is not a diagnosis: a monitoring sheet that reports how many #REF!s appeared after a restructure needs the code, not a substitute value.
• NA() manufactures the value that maps to 7.
• ERROR.TYPE's ordering and Excel's error precedence are the same sequence; a page or a test that needs the precedence order can read it off this table.

Named variants

No named variants are recorded yet.