XERROR allows for conveniently generating most of the Excel errors as output to functions
With XERROR, it is very easy to generate all but four of the Excel errors as output to functions. Error types 0, 1, 2, 3, 4, 5, 6, 7, 8, 13, and 14 (i.e. #EXTERNAL!, #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #GETTING_DATA, #FIELD!, and #CALC!, respectively) are currently supported. For error types 9, 10, 11, and 12 (i.e. #SPILL!, #CONNECT!, #BLOCKED!, and #UNKNOWN!, respectively), XERROR returns the "Error not supported!" message. For any other invalid error number, XERROR returns the "Invalid error number!" message.
If anyone can come up with tricks to add support for the remaining four errors, please feel free to post an update here.
This work is based on the discussion at the following thread:
www.mrexcel.com
With XERROR, it is very easy to generate all but four of the Excel errors as output to functions. Error types 0, 1, 2, 3, 4, 5, 6, 7, 8, 13, and 14 (i.e. #EXTERNAL!, #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #GETTING_DATA, #FIELD!, and #CALC!, respectively) are currently supported. For error types 9, 10, 11, and 12 (i.e. #SPILL!, #CONNECT!, #BLOCKED!, and #UNKNOWN!, respectively), XERROR returns the "Error not supported!" message. For any other invalid error number, XERROR returns the "Invalid error number!" message.
Excel Formula:
=LAMBDA(error_type,
LET(e,error_type+1,
IF(OR(e<1,e>15,MOD(e,1)<>0),
"Invalid error number!",
IF(ISERROR(MATCH(e,{1,2,3,4,5,6,7,8,9,14,15},0)),
"Error not supported!",
CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA(""))
)
)
)
)| Blank power workbook1 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | |||||||
| 2 | #CALC! | ||||||
| 3 | error # | type | |||||
| 4 | 0 | #EXTERNAL! | 16 | ||||
| 5 | 1 | #NULL! | 16 | ||||
| 6 | 2 | #DIV/0! | 16 | ||||
| 7 | 3 | #VALUE! | 16 | ||||
| 8 | 4 | #REF! | 16 | ||||
| 9 | 5 | #NAME? | 16 | ||||
| 10 | 6 | #NUM! | 16 | ||||
| 11 | 7 | #N/A | 16 | ||||
| 12 | 8 | #GETTING_DATA | 16 | ||||
| 13 | 9 | Error not supported! | 2 | ||||
| 14 | 10 | Error not supported! | 2 | ||||
| 15 | 11 | Error not supported! | 2 | ||||
| 16 | 12 | Error not supported! | 2 | ||||
| 17 | 13 | #FIELD! | 16 | ||||
| 18 | 14 | #CALC! | 16 | ||||
| 19 | -1 | Invalid error number! | 2 | ||||
| 20 | 2.2 | Invalid error number! | 2 | ||||
| 21 | 17 | Invalid error number! | 2 | ||||
| 22 | 17.3 | Invalid error number! | 2 | ||||
| 23 | |||||||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =LAMBDA(error_type,LET(e,error_type+1,IF(OR(e<1,e>15,MOD(e,1)<>0),"Invalid error number!",IF(ISERROR(MATCH(e,{1,2,3,4,5,6,7,8,9,14,15},0)),"Error not supported!",CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,Sheet1!#REF!,#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA("")))))) |
| C4 | C4 | =XERROR(0) |
| D4:D22 | D4 | =TYPE(C4) |
| C5 | C5 | =XERROR(1) |
| C6 | C6 | =XERROR(2) |
| C7 | C7 | =XERROR(3) |
| C8 | C8 | =XERROR(4) |
| C9 | C9 | =XERROR(5) |
| C10 | C10 | =XERROR(6) |
| C11 | C11 | =XERROR(7) |
| C12 | C12 | =XERROR(8) |
| C13 | C13 | =XERROR(9) |
| C14 | C14 | =XERROR(10) |
| C15 | C15 | =XERROR(11) |
| C16 | C16 | =XERROR(12) |
| C17 | C17 | =XERROR(13) |
| C18 | C18 | =XERROR(14) |
| C19 | C19 | =XERROR(-1) |
| C20 | C20 | =XERROR(2.2) |
| C21 | C21 | =XERROR(17) |
| C22 | C22 | =XERROR(17.3) |
If anyone can come up with tricks to add support for the remaining four errors, please feel free to post an update here.
This work is based on the discussion at the following thread:
How to generate #VALUE! error as output for a LAMBDA function?
Hello, Is there a way to generate #VALUE! error for the output of a LAMBDA function? For built-in functions, if the wrong values are entered for the parameters, the functions return the #VALUE! error, and I want to mimic that behavior for my LAMBDAs. (Essentially, I need something like the...
Upvote
0
