AVALUE

AVALUE(Array,Convert_YesNo,Empty_Value,Error_Value)
Array
Required. Array to convert TRUE/FALSE to 1/0 and numbers stored as text to numbers.
Convert_YesNo
Optional. Convert Yes's to 1's & No's to 0's= 1; or don’t= 0 or ignored
Empty_Value
Optional. Converts empty cells to value; ignored = empty string
Error_Value
Optional. Converts errors to value; ignored = empty cell value, "#Error" = original error

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.

schardt679

Board Regular
Joined
Mar 27, 2021
Messages
58
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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.

Excel Formula:
=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
ABCDEFG
1AVALUE
2
3Original DataResult
4QuesitionResponseQuesitionResponse
5What is your name?Patrick SchardtWhat is your name?Patrick Schardt
6Did you go to college?YesDid you go to college?Yes
7Did you dorm?NoDid you dorm?No
8Did you live in an on/off campus apartment?NoDid you live in an on/off campus apartment?No
9That means you were a commuter, right?That means you were a commuter, right?No Data
10Did you graduate?YesDid you graduate?Yes
11When?2017When?2017
12How many degrees do you have?3How many degrees do you have?3
13What are they?Mechatronics BS, Computer Engineering AS, & Robotics ASWhat are they?Mechatronics BS, Computer Engineering AS, & Robotics AS
14Were any 1st run majors?#N/AWere any 1st run majors?No Data
15
16Yes's & No's are not converted
17Empty Cells are converted to "No Data"
18Errors are converted to the empty cell value
19Formula in cell E4☛ =AVALUE(B4:C14,, "No Data",)
20
21
22
23Original DataResult
24ProductOrdered?Ordered?Total Orders
25Pepper DeseederYes14
26Knife SetNo0
27Cutting BoardYES1
28Peeler
29SpatulaNO0
30Meat TenderizerTRUE1
31Cast Iron 5-piece Set11
32Serving PlatterFALSE0
33Dinnerware 16-piece Set#N/A0
34
35Yes's & No's are converted to 1's & 0's
36Empty cells are returned
37Errors are converted to 0
38Formula in cell E25☛ =AVALUE(C25:C33, 1,, 0)
39
40
41
42Original DataResult
43DateDate
443/22/202103/22/21
45#VALUE!#VALUE!
463/22/202103/22/21
4703/23/2103/23/21
48
49#REF!#REF!
5003/24/2103/24/21
51
52Yes's & No's aren't converted
53Empty cells are returned
54Errors are returned
55Formula in cell D43☛ =AVALUE(B43:B50,,, "#Error")
56
AVALUE
Cell Formulas
RangeFormula
E4:F14E4=AVALUE(B4:C14,, "No Data",)
C14,C33C14=NA()
B19B19=AFORMULATEXT(E4)
E25:E33E25=AVALUE(C25:C33, 1,, 0)
F25F25=SUM(E25#)
B38B38=AFORMULATEXT(E25)
D43:D50D43=AVALUE(B43:B50,,, "#Error")
B55B55=AFORMULATEXT(D43)
Dynamic array formulas.
 
Upvote 0
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.

Excel Formula:
=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
ABCDEFG
58Original DataResult
59#N/A#N/AOmitted or
60#N/Anot specified
61 Empty string
62No Dataspecified value
63
AVALUE
Cell Formulas
RangeFormula
E59E59=AVALUE(B59:B59,,)
E60E60=AVALUE(B59:B59,,,)
E61E61=AVALUE(B59:B59,,, "")
E62E62=AVALUE(B59:B59,,, "No Data")
 
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.

Excel Formula:
=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
BCDEFGHI
21AVALUE
22
23Original DataConvert_YesNo:1Result
24ProductOrdered?Empty_Value:Ordered?Total Orders
25Pepper DeseederYesError_Value:014
26Knife SetNo0
27Cutting BoardYES1
28Peeler
29SpatulaNO0
30Meat TenderizerTRUE1
31Cast Iron 5-piece Set11
32Serving PlatterFALSE0
33Dinnerware 16-piece Set#N/A0
34
35Yes's & No's are converted to 1's & 0's
36Empty cells are returned
37Errors are converted to 0
38Formula in cell H25☛ =AVALUE(C25:C33, F23,, F25)
39
AVALUE
Cell Formulas
RangeFormula
H25:H33H25=AVALUE(C25:C33, F23,, F25)
I25I25=SUM(H25#)
C33C33=NA()
B38B38=AFORMULATEXT(H25)
Dynamic array formulas.
 

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top