Nested If Statement problem

1. What is the problem? Be very detailed.

I am trying to return one of the five values below using a if statement in the attached form. I keep getting the following error message when converting to XML.

Error: ODK Validate Errors:

XForm is invalid. See above for the errors.
: Invalid calculate for the bind attached to "${suitability}" : Mismatched brackets or parentheses in expression if(( ${Score} <0.5),'Poor',if(( ${Score} >0.5 and <0.59),'Below Average',if(( ${Score} >0.6 and <0.69), 'Average',if(( ${Score} >0.7 and <0.79), 'Good',if(( ${Score} >0.8),'Excellent', 'error'))

The following files failed validation:

Result: Invalid

<0.5 = poor
0.5 – 0.59 = below average
0.6 – 0.69 = average
0.7 – 0.79 = good

0.8 = excellent

2. What app or server are you using and on what device and operating system? Include version numbers.

I am using ODK collect on andriod. Via google sheets. Converting form via

3. What you have you tried to fix the problem?

I have tried modifying the if statement. I have not got much experience with if statements so any help would be much appreciated. Thanks in advance

HSI_V01.xlsx (13.3 KB)

There are a number of 'issues' in your calculation, mis-matching closed parens being one of them... :slight_smile:

So instead of this:

if((${Score} <0.5),'Poor',if((${Score} >0.5 and <0.59),'Below Average',if((${Score} >0.6 and <0.69), 'Average',if((${Score} >0.7 and <0.79), 'Good',if((${Score} >0.8),'Excellent', 'error'))

try this:

if(${Score} < 0.5,'Poor', if(${Score} < 0.59,'Below Average', if(${Score} < 0.69, 'Average', if(${Score} < 0.79, 'Good','Excellent'))))

To explain why, for starters:

if((${Score} >0.5 and <0.59)

is syntactically incorrect. The XPath and operator combines two boolean (ie true or false) subexpressions, but '<0.59' isnt a boolean expression. Instead you have to be explicit in what you are comparing against; ie

if((${Score}>0.5) and (${Score}<0.59), ...

Second, since you are checking ranges,

if((${Score} <0.5),'Poor', if((${Score} >0.5 and (${Score}<0.59), ...

is redundant, because the only way the second if will ever get evaluated is if ${Score} > 0.5, so checking it is a bit of a waste of time [I'm ignoring the fact strictly speaking it should have been >=0.5 ... :wink: ]

Ditto the rest of the comparisons...

Take a look at my revised expression. This is a good template if you are checking a bunch of numeric ranges. Note, I dropped the final 'error' outcome because its effectively impossible to get to this point: you effectively check < 0.5 and >= 0.8, and everything in-between, and AFAIK there are no other numbers outside this infinite range... :grin:

1 Like

Thank you for such a detailed reply. It is much appreciated. Worked like a charm cheers