ERROR.TYPE

Returns a number corresponding to an error value, otherwise returns #N/A if no error exists in the formula

Syntax:

ERROR.TYPE(value)

value is the value that is being checked.

Example:

If value contains #INVALID!:

ERROR.TYPE("#INVALID!")

returns 0

If value contains #NULL!:

ERROR.TYPE("#NULL!")

returns 1

If value contains #DIV/0!:

ERROR.TYPE("#DIV/0!")

Returns 2

If value contains #VALUE!:

ERROR.TYPE("#VALUE!")

returns 3

If value contains #REF!:

ERROR.TYPE("#REF!")

returns 4

If value contains #NAME?:

ERROR.TYPE("#NAME?")

returns 5

If value contains #NUM!:

ERROR.TYPE("#NUM!")

returns 6

If value contains #N/A:

ERROR.TYPE("#N/A")

returns 7

If value contains A:

ERROR.TYPE("A")

returns #N/A

Value:

Result:

0

Application:

Imagine you are managing a product inventory spreadsheet. You have a list of products, their current stock level, and their cost per unit. You want to calculate the total value of the stock for each product.

Sometimes, data entry errors can occur. A user might accidentally enter text into a cell where a number is expected, or a product might be marked as "discontinued," causing a division by zero error in a formula.

Scenario: Inventory Management Spreadsheet

	Product ID	Product Name	Stock Level	Cost per Unit	Total Stock Value	Error Type	Error Description
	A	B	C	D	E	F	G
1	P001	Widget A	150	$12.50	$1,875.00	No error found	No error found
2	P002	Widget B	200	$8.75	$1,750.00	No error found	No error found
3	P003	Widget C	120	$0.00	Produced error	2	#DIV/0! Error
4	P004	Widget D	out of stock	$25.00	Produced error	3	#VALUE! Error
5	P005	Widget E	75	discontinued	Produced error	3	#VALUE! Error
6	P006	Widget F	50	$15.00	$750.00	No error found	No error found

Formulas Used:

Total Stock Value Column (E): The formulas in this column are intentionally set up to produce errors in some rows to demonstrate the ERROR.TYPE function.
- IFERROR(C1*D1, "Produced error")
- IFERROR(C2*D2, "Produced error")
- IFERROR(C3/D3, "Produced error") (This will produce a #DIV/0! error)
- IFERROR(C4*D4, "Produced error") (This will produce a #VALUE! error)
- IFERROR(C5*D5, "Produced error") (This will produce a #VALUE! error)
- IFERROR(C6*D6, "Produced error")
Error Type Column (F): This is where the ERROR.TYPE function is used. The IFERROR function is wrapped around it to prevent an error from showing if the calculation is successful.
- IFERROR(ERROR.TYPE(C1*D1), "No error found")
Error Description Column (G): This column uses the number from ERROR.TYPE to display a user-friendly description of the error.
- IF(ISNUMBER(F1), IF(F1=0,"#INVALID! Error", IF(F1=1,"#NULL! Error", IF(F1=2,"#DIV/0! Error", IF(F1=3,"#VALUE! Error", IF(F1=4,"#REF! Error", IF(F1=5,"#NAME? Error", IF(F1=6,"#NUM! Error", IF(F1=7,"#N/A Error", "No error found")))))))), "No error found")

What Happens:

For Widget A and Widget B, the calculations are successful, and the ERROR.TYPE function returns an No error found (due to the IFERROR wrapper).
For Widget C, the formula tries to divide by zero (D3 is $0.00), which causes a #DIV/0! error. The ERROR.TYPE function returns 2.
For Widget D, the Stock Level is "out of stock" (text), which causes a #VALUE! error when trying to multiply. The ERROR.TYPE function returns 3, which is then described as "VALUE! Error".
For Widget E, the Cost per Unit is "discontinued" (text), also causing a #VALUE! error. ERROR.TYPE again returns 3.
For Widget F, the calculation is successful.