Error.Type–
You can use ERROR.TYPE to check for specific kinds of errors. The Microsoft Excel ERROR.TYPE function returns the numeric number as a result for all types of errors in Excel. it returns a number that corresponds to a specific error value. See table and example below for the error codes returned by ERROR.TYPE.
| ERRRO_VALUE | ERROR.Type Returns | DESCRIPTION |
|---|---|---|
| #NULL! | 1 | This Error Generates When You Refer To An Intersection Of Two Ranges That Do Not Intersect. |
| #DIV/0! | 2 | Divided By Zero Value |
| #VALUE! | 3 | Incomplete Data Value Or Wrong Data Value In Formula And Function. |
| #REF! | 4 | Incorrect Cell Reference Or Cell Deleted Refer To Reference. |
| #NAME? | 5 | Incorrect Name Of Cell Or Define Name Of Cell Is Incorrect. |
| #NUM! | 6 | Unsupported Number In Excel. This Error Generate Occurs When Excel Encounter An Invalid Number. |
| #N/A | 7 | This Error Indicates That A Value Is Not Available Or Missing To A Formula. |
| SYNTAX | ERROR.TYPE (error_val) |
1. The Excel #NULL! Error
Excel generate the #NULL! error when you attempt to intersect two ranges that don’t intersect. For example, the formula =SUM(C3:C6 A4:D6) will return the sum of the values in the range C4:C6 (the intersection of the ranges C3:C6 and A4:D6). However, if you entered the formula =SUM(C3:C6 D4:D6) you would get the #NULL! error, because the ranges C3:C6 and D4:D6 do not intersect.
- The Excel #DIV/0! Error
The Excel #DIV/0! is generate when a formula attempts to divide the number by zero. A division by zero is equal to infinity, which cannot be represented by a spreadsheet value, so Excel returns the #DIV/0! error.
For example, if cell C4 contains the value 0, then the formula: =A3/C4 will return the #DIV/0! error.
- The Excel #VALUE! Error
The #VALUE! error is generated when one of the cell value in a formula is of the wrong type.
For example, formula =A3+B3 depend on cells A3 and B3 containing numeric values. Therefore, if either A3 or B3 contains a text value will result in the #VALUE! error.
- The Excel #REF! Error
This indicates an invalid cell reference or the formula previously referenced a cell is not available or it has been deleted and because of this Excel generate #REF! error.
For example Cell F4 contains the formula: =C3+D3
If you now delete column D of the spreadsheet it will give you #REF! error.
- The Excel #NAME? Error
When in a formula applied to a cell and if there is text inside the formula, Excel will try to interpret the text as a reference, a named range, or a function name. If the text is not recognized as any of these, the excel will return #NAME? error. OR if you have a spell a formula name incorrect like this example the excel will return the #NAME? error.
For example, if you intended to type in the function =SUM(A3:D6), but you accidentally type =SU(A3:D6), Excel will fail to recognise the function name “SU” and it will generate the #NAME? error.
- The Excel #NUM! Error
The #NUM! Excel formula error is return by excel when Excel encounters an invalid number in a formula.
For example, all square numbers are positive numbers in a range A3:D6, so there is no such thing as a square root of a negative number. As such, we have a positive number in cell C3 to get the correct result. But if we have a negative number in Cell(C3) as show in image we get a #NUM error.
- The Excel #N/A Error
The #N/A Excel formula error is return by excel when a value is not available to the formula.
For example, As per image below shows an attempt to use the Vlookup function to find the value “excelhelp” in column C of the spreadsheet range, and to return the associated result from column A. However, as “excelhelp” does not available in column A, the VLOOKUP function is unable to find this value and so it returns the #N/A error.
