How to generate #VALUE! error as output for a LAMBDA function?

[Rnkhch]

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 NA() function that generates the #N/A! error.)

Thanks for any input!

 

[smozgur]

Something like this?

=LAMBDA(val,IF(val=1,"OK",NA()))(2)

I don't think there is a function to generate #VALUE!, but I think we can trick Excel by simply generating a #VALUE! error.

=LAMBDA(val,IF(val=1,"OK",VALUE("")))(2)

 

[Rnkhch]

Yes, thanks, that's exactly what I was looking for; simple and compact.

 

[Rick Rothstein]

Something like this?

=LAMBDA(val,IF(val=1,"OK",NA()))(2)

I don't think there is a function to generate #VALUE!, but I think we can trick Excel by simply generating a #VALUE! error.

=LAMBDA(val,IF(val=1,"OK",VALUE("")))(2)

What about giving it a function call that will generate a #VALUE! error, for example...

=LAMBDA(val,IF(val=1,"OK",LN("A")))(2)

 

[smozgur]

Yes, thanks, that's exactly what I was looking for; simple and compact.

Glad to hear it helps.

What about giving it a function call that will generate an #VALUE! error, for example...

=LAMBDA(val,IF(val=1,"OK",SUM("A")))(2)

Exactly. I also had the same intention with VALUE("") function since the VALUE function will return #VALUE! error with blank parameter. I just wanted to indicate VALUE indicated in the formula somehow. SUM will work as well.

 

[Rnkhch]

OK, I just cooked this up:

=LAMBDA(error_type,CHOOSE(error_type,#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,,,,,,,,,,,,#EXTERNAL!))

It works with errors 1 through 7 and 19, as listed above. However, 8 didn't come out as expected (so I removed it), and 9 through 14 were not possible to include in the formula as it was giving me a function error upon hitting ENTER.

Any idea how to get this to work with all error types? Will be a great LAMBDA to post in the LAMBDA section.

 

[smozgur]

If it is ok to return the error type as string for the ones after #9 then you can use text instead (only shown for #SPILL! below).
(#8 works for me by the way)

=LAMBDA(error_type,CHOOSE(error_type+1,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,#GETTING_DATA,"#SPILL!"))(8)

Note: #EXTERNAL! is actually 0, so we need to make CHOOSE to work with zero, that's why I used error_type + 1 for the index number.

 

[Rnkhch]

If it is ok to return the error type as string for the ones after #9 then you can use text instead

Thanks! The text versions aren't as cool, but for now I'll live with that until I or anyone else finds a way

#8 works for me by the way

Yeah, I was also able to hit ENTER with 8, but the output was only text and the little error triangle at the top left didn't appear. But perhaps this particular error doesn't have a triangle?

#EXTERNAL! is actually 0

I see, thanks for letting me know. I was going based off the argument list that appears when you type =ERROR.TYPE( which shows external to be 19. So is it only 0 or both 0 and 19?

 

[smozgur]

I see, thanks for letting me know. I was going based off the argument list that appears when you type =ERROR.TYPE( which shows external to be 19. So is it only 0 or both 0 and 19?

Yes, the constant index is 19 but for some reason ERROR.TYPE(#EXTERNAL!) returns 0 (mysterious Excel Development Team!), and this is supposed to be the constant value. So, if there is an #EXTERNAL! error/notification in a cell, then its values should be 0, at least according to ERROR.TYPE.

 

[Rnkhch]

OK, I managed to add two more errors to the function, i.e. #FIELD! and #CALC!. I added a "not supported" message for the four errors that currently don't work with the function, i.e. #SPILL!, #CONNECT!, #BLOCKED!, and #UNKNOWN!:

=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)),
            "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	Not supported!	2
14		10	Not supported!	2
15		11	Not supported!	2
16		12	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)),"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)

At this point the function covers all of the most common errors, so I'm gonna go ahead and post this in the LAMBDA section, and if I or anyone figures out tricks for the remaining four errors, we can update it over time.