topic nesting if AND formula in XLSform. in ArcGIS Survey123 Questions

nesting if AND formula in XLSform.

Alex_Dale — Thu, 02 Jan 2020 20:11:07 GMT

I am having trouble nesting multiple if AND statements in an XLSform for Survey123.

I am getting the follwing error: cannot handle function 'if' requires 3 arguments. Only 1 provided.

Here is my formula:

if(AND(${homelessness}="1to2yrs", ${mhDis}="Yes",1,if(AND(${homelessness}="1to2yrs", ${PhyDevOther}="Yes",1,if(AND(${homelessness}="1to2yrs", ${AlcDrugs}="Yes",1,if(AND(${homelessness}="1to2yrs", ${HIVAIDS}="Yes",1,if(AND(${homelessness}="other", ${mhDis}="Yes",1,if(AND(${homelessness}="other", ${PhyDevOther}="Yes",1,if(AND(${homelessness}="other", ${AlcDrugs}="Yes",1,if(AND(${homelessness}="other", ${HIVAIDS}="Yes",1,if(AND(${homeless_times}="4", ${mhDis}="Yes",1,if(AND(${homeless_times}="4", ${PhyDevOther}="Yes",1,if(AND(${homeless_times}="4", ${AlcDrugs}="Yes",1,if(AND(${homeless_times}="4", ${HIVAIDS}="Yes",1))))))))))))))))))))))))

Any help is greatly appreciated.

Re: nesting if AND formula in XLSform.

Jim-Moore — Fri, 03 Jan 2020 02:36:16 GMT

Hi Alex

If statements require three arguments: a condition; the result if the condition is true; and the result if the condition is false. It looks like the final false argument is missing (i.e. what to return if all conditions are false).

Also, the XLSForm syntax for and statements is to use the word "and" between two expressions. For example:

${homelessness}='1to2yrs' and ${mhDis}='Yes'

XLSForm functions are detailed here: Formulas—Survey123 for ArcGIS | Documentation

Hope this helps.

Cheers,

Jim

Re: nesting if AND formula in XLSform.

Alex_Dale — Sun, 05 Jan 2020 21:11:43 GMT

Thank you Jim,

The new syntax somewhat worked. Now, however, the first IF function

overrides the rest of the nested IF functions:

if($="1to2yrs"and $="Yes",1,2) or if($="1to2yrs"and $="Yes",1,2) or if($="1to2yrs"and $="Yes", 1,2) or if($="1to2yrs"and $="Yes",1,2) or if($="other"and $="Yes",1,2) or if($="other"and $="Yes",1,2)

or if($="other"and $="Yes", 1,2) or if($="other"and $="Yes",1,2) or if($="Yes"and $="Yes",1,2) or if($="Yes"and $="Yes",1,2)or if($="Yes"and $="Yes",1,2)or if($="Yes"and $="Yes",1,2)

Any thoughts?

Alex Dale

Data Manager

Partners In Care, Oahu’s Continuum of Care

200 North Vineyard Boulevard, Suite 210

Honolulu, HI 96817

Phone: (808) 543-2242

Email: adale@auw.org

www.partnersincareoahu.org

TOGETHER, WE CAN END HOMELESSNESS

Partners in Care’s mission is to eliminate homelessness through open and

inclusive participation and the coordination of integrated responses.

Re: nesting if AND formula in XLSform.

Jim-Moore — Mon, 06 Jan 2020 02:54:44 GMT

Hi Alex

The way you had nested the if statements in your original post looks correct, besides the syntax for the and function and the missing false argument for the final if statement. As a simple example:

if(${fruit}='banana','yellow',if(${fruit}='apple','green',if(${fruit}='strawberry','red','no fruit selected')))

Hope this helps

Cheers,

Jim