AVALUE

[schardt679]

AVALUE converts yes's/TRUE's to 1's, no's/FALSE's to 0's, empty cells to a value, errors to a value, numbers stored as text to numbers, and leaves text alone.
Optional arguments require comma to work. AVALUE is an improved version of NUMTEXT.

=LAMBDA(Array,Convert_YesNo,Empty_Value,Error_Value,
      LET(E, "",
          Arr, Array&E,
          ConYN?, Convert_YesNo,
          Empty, Empty_Value&E,
          Error, Error_Value&E,
          ConScan, AND(ConYN?<>{1,0}),
          ConMSG, "Convert=1,0",
          Error?,IFERROR(Arr, SWITCH(Error,  E, E,  "#Error", Arr,  Error)),
          Empty?, IF(Error?=E, Empty, Error?),
          YesNo?, IF(ConYN?, SWITCH(Empty?,  "YES", 1,  "NO", 0,  Empty?), Empty?),
          TrueFalse?, SWITCH(YesNo?,  "TRUE", 1,  "FALSE", 0,  YesNo?),
          Result, IFERROR(--(TrueFalse?), TrueFalse?),
          Return, IF(ConScan, ConMSG, Result),
          Return
      )
 )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G
1		AVALUE
2
3		Original Data			Result
4		Quesition	Response		Quesition	Response
5		What is your name?	Patrick Schardt		What is your name?	Patrick Schardt
6		Did you go to college?	Yes		Did you go to college?	Yes
7		Did you dorm?	No		Did you dorm?	No
8		Did you live in an on/off campus apartment?	No		Did you live in an on/off campus apartment?	No
9		That means you were a commuter, right?			That means you were a commuter, right?	No Data
10		Did you graduate?	Yes		Did you graduate?	Yes
11		When?	2017		When?	2017
12		How many degrees do you have?	3		How many degrees do you have?	3
13		What are they?	Mechatronics BS, Computer Engineering AS, & Robotics AS		What are they?	Mechatronics BS, Computer Engineering AS, & Robotics AS
14		Were any 1st run majors?	#N/A		Were any 1st run majors?	No Data
15
16		Yes's & No's are not converted
17		Empty Cells are converted to "No Data"
18		Errors are converted to the empty cell value
19		Formula in cell E4☛ =AVALUE(B4:C14,, "No Data",)
20
21
22
23		Original Data			Result
24		Product	Ordered?		Ordered?	Total Orders
25		Pepper Deseeder	Yes		1	4
26		Knife Set	No		0
27		Cutting Board	YES		1
28		Peeler
29		Spatula	NO		0
30		Meat Tenderizer	TRUE		1
31		Cast Iron 5-piece Set	1		1
32		Serving Platter	FALSE		0
33		Dinnerware 16-piece Set	#N/A		0
34
35		Yes's & No's are converted to 1's & 0's
36		Empty cells are returned
37		Errors are converted to 0
38		Formula in cell E25☛ =AVALUE(C25:C33, 1,, 0)
39
40
41
42		Original Data		Result
43		Date		Date
44		3/22/2021		03/22/21
45		#VALUE!		#VALUE!
46		3/22/2021		03/22/21
47		03/23/21		03/23/21
48
49		#REF!		#REF!
50		03/24/21		03/24/21
51
52		Yes's & No's aren't converted
53		Empty cells are returned
54		Errors are returned
55		Formula in cell D43☛ =AVALUE(B43:B50,,, "#Error")
56
AVALUE

Cell Formulas
Range	Formula
E4:F14	E4	=AVALUE(B4:C14,, "No Data",)
C14,C33	C14	=NA()
B19	B19	=AFORMULATEXT(E4)
E25:E33	E25	=AVALUE(C25:C33, 1,, 0)
F25	F25	=SUM(E25#)
B38	B38	=AFORMULATEXT(E25)
D43:D50	D43	=AVALUE(B43:B50,,, "#Error")
B55	B55	=AFORMULATEXT(D43)
Dynamic array formulas.

 

[schardt679]

With the release of ISOMITTED, I updated this function.

AVALUE converts yes's/TRUE's to 1's, no's/FALSE's to 0's, empty cells to a value, errors to a value, numbers stored as text to numbers, and leaves text alone.

AVALUE is an improved version of NUMTEXT.
If skipping arguments, a comma is required. Calls IFBLANK.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =AVALUE(Array☛ range; TRUE/FALSE»1/0; ?❿»❿, [Convert_YesNo]☛ 1=Yes/No»1/0; 0/=don't, [Empty_Value]☛ ""»; =keep, [Error_Value]☛ errors»; ""=Empty_Value; =keep) ⁂[]=optional; =default; =omit; »=to; ""=empty string; =value; ?/❿=text/number

The syntax is the same: AVALUE(Array,[Convert_YesNo],[Empty_Value],[Error_Value])

The arguments are the same. To keep errors omit [Error_Value], and to convert them to the {Empty_Value] use ""; otherwise errors are converted to a specified value..
Array: Required: Array to convert TRUE/FALSE to 1/0 and numbers stored as text to numbers.
[Convert_YesNo]: Optional. 1☛ convert Yes's to 1's/ No's to 0's; 0 or ignored☛ don't.
[Empty_Value]: Optional. Convert empty cells to value; ignored☛ keep empty cells.
[Error_Value]: Optional. Convert errors to value; empty string☛ Empty_Value; ignored☛ keep errors.

=LAMBDA(Array,[Convert_YesNo],[Empty_Value],[Error_Value],
      LET(Arr, IFBLANK(Array, ""),          ConYN?, Convert_YesNo,          EmpVal, Empty_Value,          ErrVal, Error_Value,
             ConScan, AND(ConYN?<>{1,0}),          ConMSG, "Convert_YesNo=1,0",
             Err?, IFERROR(Arr, IF(ISOMITTED(ErrVal), Arr, ErrVal)),
             Emp?, IFBLANK(Err?, IF(ISOMITTED(EmpVal), Err?, EmpVal)),
             YN?, IF(ConYN?, SWITCH(Emp?,  "YES", 1,  "NO", 0,  Emp?), Emp?),
             Result, IFERROR(--(YN?), YN?),
             Return, IF(ConScan, ConMSG, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G
58		Original Data			Result
59		#N/A			#N/A	Omitted or
60					#N/A	not specified
61						Empty string
62					No Data	specified value
63
AVALUE

Cell Formulas
Range	Formula
E59	E59	=AVALUE(B59:B59,,)
E60	E60	=AVALUE(B59:B59,,,)
E61	E61	=AVALUE(B59:B59,,, "")
E62	E62	=AVALUE(B59:B59,,, "No Data")

 

[schardt679]

With the release of ISOMITTED and my function IFYESNO, I updated this function again. I have been experimenting with ISOMITTED. It seems ISOMITTED is good for changing default argument values from empty strings or 0's to something else while still allowing empty strings and 0's. Using ISOMITTED for Empty_Value is a needless extra function call since the default value is an empty string. Whereas the default value for Error_Value are the errors themselves, so ISOMITTED can work here.

AVALUE converts yes's/TRUE's to 1's, no's/FALSE's to 0's, empty cells to a value, errors to a value, numbers stored as text to numbers, and leaves text alone. Uses ISOMITTED!

AVALUE is an improved version of NUMTEXT.
If skipping arguments, a comma is required. Calls IFBLANK and IFYESNO.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =AVALUE(Array☛ range; TRUE/FALSE»1/0; ?❿»❿, [Convert_YesNo]☛ 1=Yes/No»1/0; 0/=don't, [Empty_Value]☛ ""»; =keep, [Error_Value]☛ errors»; ""=Empty_Value; =keep) ⁂[]=optional; =default; =omit; »=to; ""=empty string; =value; ?/❿=text/number

Syntax: AVALUE(Array,[Convert_YesNo],[Empty_Value],[Error_Value])

Arguments:
Array: Required: Array to convert TRUE's/FALSE's to 1's/0's and numbers stored as text to numbers.
[Convert_YesNo]: Optional. 1☛ convert Yes's/No's to 1's/ 0's; 0 or ignored☛ don't.
[Empty_Value]: Optional. Convert empty cells to value; ignored☛ keep empty cells.
[Error_Value]: Optional. Convert errors to value; empty string☛ Empty_Value; ignored☛ keep errors.

=LAMBDA(Array,[Convert_YesNo],[Empty_Value],[Error_Value],
      LET(Arr, IFBLANK(Array,  ""),          ConYN?, IFERROR(--(Convert_YesNo),  2),          EmpVal, Empty_Value,          ErrVal, Error_Value,
             ConScan, AND(ConYN?<>{1,0}),          ConMSG, "Convert_YesNo=1,0",
             Text?, IFERROR(--(Arr),  Arr),          YesNo?, IF(ConYN?, IFYESNO(Text?),  Text?),
             Error?, IFERROR(YesNo?,  IF(ISOMITTED(ErrVal), YesNo?,  IFBLANK(ErrVal,  ""))),
             Result, IFBLANK(Error?,  IFBLANK(EmpVal,  "")),          Return, IF(ConScan, ConMSG,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	B	C	D	E	F	G	H	I
21	AVALUE
22
23	Original Data			Convert_YesNo:	1		Result
24	Product	Ordered?		Empty_Value:			Ordered?	Total Orders
25	Pepper Deseeder	Yes		Error_Value:	0		1	4
26	Knife Set	No					0
27	Cutting Board	YES					1
28	Peeler
29	Spatula	NO					0
30	Meat Tenderizer	TRUE					1
31	Cast Iron 5-piece Set	1					1
32	Serving Platter	FALSE					0
33	Dinnerware 16-piece Set	#N/A					0
34
35	Yes's & No's are converted to 1's & 0's
36	Empty cells are returned
37	Errors are converted to 0
38	Formula in cell H25☛ =AVALUE(C25:C33, F23,, F25)
39
AVALUE

Cell Formulas
Range	Formula
H25:H33	H25	=AVALUE(C25:C33, F23,, F25)
I25	I25	=SUM(H25#)
C33	C33	=NA()
B38	B38	=AFORMULATEXT(H25)
Dynamic array formulas.