ERROR.TYPE function

Returns the error type of the given value. ERROR.TYPE(Field1 / Field2)ERROR.TYPE(Field1 / Field2) returns 2, indicating a #DIV/0! ("division by zero") error, if the value of Field2 is zero or blank.

These are the values returned by this function:

Errors are never displayed to your users

When you preview your app, any errors are shown in red. This is meant to help you, the author of the app, determine the cause of potentially unexpected behavior. Errors are also shown when you sign into Calcapp Connect with your credentials and run your own apps.

However, these errors are not shown in shared apps, meaning that errors are never shown to your users. Instead, a field with an error looks like a blank field in shared apps.

If you want to hide errors from showing up in the preview and in Calcapp Connect, use a formula like the following:

IFERROR([your formula], BLANK())IFERROR([your formula]; BLANK())

Detecting all errors versus only #N/A errors

There is generally little need to determine the exact type of an error, except for the #N/A error ("not available"). Lookup functions like XLOOKUP return #N/A errors when a value cannot be found.

There is a specialized function for detecting #N/A errors: ISNA. ISNA is simpler to use than ERROR.TYPE, as it returns TRUE if the error is an #N/A error and FALSE otherwise.

The IFNA function is used to return a specific value if a value is an #N/A error, or the value itself otherwise. This formula returns 500, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP:

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

IFERROR versus IFNA

IFERROR is a more generic version of IFNA, because it detects all errors, and not just #N/A errors. This formula also returns 500:

IFERROR(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFERROR(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

However, it is probably a mistake to use IFERROR here instead of IFNA, because it catches all errors, rather than only #N/A errors.

For an example of why using IFERROR instead of IFNA can be a mistake, consider this formula:

IFERROR(XLOOKUP(Field1 / Field2, { 20, 30 }, { 200, 300 }), 500)IFERROR(XLOOKUP(Field1 / Field2; { 20; 30 }; { 200; 300 }); 500)

If the value of Field2 is zero, or is blank, this will cause a #DIV/0! ("division by zero") error to be returned from the / operator, which XLOOKUP will pass on to IFERROR, which in turn will cause IFERROR to return 500 instead of the #DIV/0! error.

If the intent is to only return 500 if the sought value cannot be found in the lookup array, catching all errors is a mistake. It is better to use the formula IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500) and let the #DIV/0! error slip past IFNA.

To detect if an error is any error except for an #N/A error, use the ISERR function.

ERROR.TYPE and arrays

Applying ERROR.TYPE to an array only yields a single value, related to the array value itself and not its constituent elements.

This formula returns 4, indicating a #REF! error due to the arrays not being the same size (which GROWTH requires):

ERROR.TYPE(GROWTH({ 1 }, { 1, 2 }))ERROR.TYPE(GROWTH({ 1 }; { 1; 2 }))

Use MAP to invoke ERROR.TYPE once for every array element and have the results returned as a number array.

This formula returns { 4, 7 }{ 4; 7 }, indicating that the first array element is a #REF! error and the second array element is an #N/A error:

MAP({ GROWTH({ 1 }, { 1, 2 }), NA() }, ERROR.TYPE(Element))MAP({ GROWTH({ 1 }; { 1; 2 }); NA() }; ERROR.TYPE(Element))

Examples

ERROR.TYPE(Field1)ERROR.TYPE(Field1)

Returns the error type of Field1.ValueField1,Value.

ERROR.TYPE(Field1 / Field2)ERROR.TYPE(Field1 / Field2)

Returns 2, indicating a #DIV/0! ("division by zero") error, if the value of Field2 is zero or is blank.

IFERROR(Field1 / Field2, 0)IFERROR(Field1 / Field2; 0)

Returns Field1 / Field2Field1 / Field2 if this calculation does not result in an error, or 0 otherwise. In other words, if the value of Field2 is 0 or blank, 0 is returned, otherwise the result of the division operation is returned.

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

Returns 500, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP. The IFNA function is used to return a specific value if a value is an #N/A error, or the value itself otherwise.

ERROR.TYPE({ NA() })ERROR.TYPE({ NA() })

Returns an #N/A error (and not 7 or { 7 }{ 7 }), as the given array is not, in and of itself, an error.

MAP({ NA() }, ERROR.TYPE(Element))MAP({ NA() }; ERROR.TYPE(Element))

Returns the array { 7 }{ 7 }, to indicate that the sole array element has an error type of 7, corresponding to an #N/A error.