Cuprins
Ieri la maraton 30 de funcții Excel în 30 de zile we were looking for values using the function PRIVEȘTE ÎN SUS (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 EROARE.TIP (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 EROARE.TIP (ERROR TYPE) in Excel. If you have additional information or examples, please share them in the comments.
Function 17: ERROR.TYPE
Funcţie EROARE.TIP (ERROR.TYPE) determines the type of error by number or returns #LA (#N/A) if no error is found.
How can the ERROR.TYPE function be used?
Prin intermediul EROARE.TIP (ERROR.TYPE) You can:
- identify the type of error.
- help users correct errors that occur.
Syntax ERROR.TYPE
Funcţie EROARE.TIP (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 EROARE.TIP (TYPE.ERROR):
- 1 ... #NUL! (#EMPTY!)
- 2 ... # DIV / 0! (#DEL/0!)
- 3 ... #VALOARE! (#SO!)
- 4 ... #REF! (#SSYL!)
- 5 ... #NAME? (#NAME?)
- 6 ... #ON ONE! (#NUMBER!)
- 7 ... #LA (#N / A)
- #LA (#N/A) … any other value
Traps ERROR.TYPE
Dacă valoarea argumentului error_val (error_value) is not an error, the result of a function EROARE.TIP (ERROR.TYPE) will be an error message #LA (#N/A). You can avoid this if you use the function EEROARE (ISERROR) to check for an error, as shown in example 2.
Example 1: Determining the type of error
Utilizarea funcțiilor EROARE.TIP (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 #LA (#N / A).
=ERROR.TYPE(B3)
=ТИП.ОШИБКИ(B3)
In this example, cell B3 contains #VALOARE! (#VALUE!), so the error type is 3.
Example 2: Helping Users Deal with Errors
Prin combinare EROARE.TIP (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 #VALOARE! (#VALUE!). If zero is entered in cell C3, the result will be an error message # DIV / 0 (#SECȚIUNEA/0).
În celula D4, funcția EEROARE (ISERROR) checks for an error, and EROARE.TIP (ERROR.TYPE) returns the number of this error. Function PRIVEȘTE ÎN SUS (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: