Contents
Yesterday in the marathon 30 Excel functions in 30 days we were looking for values using the function LOOKUP (VIEW). Today we will use this function again to work on the bugs.
We will devote the 17th day of the marathon to the study of the function ERROR.TYPE (TYPE.ERROR). It is able to recognize types of errors, and you, in turn, can use this information to eliminate them.
So, let’s look at the information and examples of using the function ERROR.TYPE (ERROR TYPE) in Excel. If you have additional information or examples, please share them in the comments.
Function 17: ERROR.TYPE
Function ERROR.TYPE (ERROR.TYPE) determines the type of error by number or returns #AT (#N/A) if no error is found.
How can the ERROR.TYPE function be used?
By means of ERROR.TYPE (ERROR.TYPE) You can:
- identify the type of error.
- help users correct errors that occur.
Syntax ERROR.TYPE
Function ERROR.TYPE (ERRORTYPE) has the following syntax:
ERROR.TYPE(error_val)
ТИП.ОШИБКИ(значение_ошибки)
- error_val (error_value) is the same error that needs to be identified.
- codes returned by the function ERROR.TYPE (TYPE.ERROR):
- 1 … #NULL! (#EMPTY!)
- 2 … # DIV / 0! (#DEL/0!)
- 3 … #VALUE! (#SO!)
- 4 … #REF! (#SSYL!)
- 5 … #NAME? (#NAME?)
- 6 … #ON ONE! (#NUMBER!)
- 7 … #AT (#N/A)
- #AT (#N/A) … any other value
Traps ERROR.TYPE
If the value of the argument error_val (error_value) is not an error, the result of a function ERROR.TYPE (ERROR.TYPE) will be an error message #AT (#N/A). You can avoid this if you use the function ISERROR (ISERROR) to check for an error, as shown in example 2.
Example 1: Determining the type of error
Using functions ERROR.TYPE (ERROR.TYPE) You can check a cell to determine what kind of error it contains. If there is no error in the cell, then instead of a numeric error code, the value will be returned #AT (#N/A).
=ERROR.TYPE(B3)
=ТИП.ОШИБКИ(B3)
In this example, cell B3 contains #VALUE! (#VALUE!), so the error type is 3.
Example 2: Helping Users Deal with Errors
By combining ERROR.TYPE (ERROR TYPE) with other functions, you can help users correct errors that appear in a cell. In this example, numbers must be entered in cells B3 and C3. If text is entered, the result in D3 will be an error message #VALUE! (#VALUE!). If zero is entered in cell C3, the result will be an error message #DIV/0 (#SECTION/0).
In cell D4, the function ISERROR (ISERROR) checks for an error, and ERROR.TYPE (ERROR.TYPE) returns the number of this error. Function LOOKUP (LOOKUP) finds an appropriate message in the error code table with a hint on how to fix the error and displays it to the user.
=IF(ISERROR(D3),LOOKUP(ERROR.TYPE(D3),$B$9:$B$15,$D$9:$D$15),"")
=ЕСЛИ(ЕОШИБКА(D3);ПРОСМОТР(ТИП.ОШИБКИ(D3);$B$9:$B$15;$D$9:$D$15);"")
Here is a table of correspondence between numerical error codes and displayed messages: