If calculated value is between give it a text value Survey123

[DavidBuehler]

In my Survey123 form, I have a calculation that totals the values of several questions give the calculation a total score. What I would like to do next is calculate if the value is between 0 and 5 give it a text description of X, if the value is between 6 and 2 give it a different text description and so on and so forth.

Is this possible, and if so how would I do that?

[JamesTedrick]

Hi David,

This can be done using nested if statements.  I assume in the above statement you wanted 6-12:

if(${q} >= 0 and ${q} <= 5, "0-5 value", if(${q} >= 6 and ${q} <= 12, "6-12 value", "value is less than 0, greater than 12, or between 5.01 and 5.99").  Depending on the exact circumstances (which values are possible, whether this in an integer or decimal), this statement can be simplified (for example, removing the upper and lower bounds checks if it is impossible that a score exceeds the range).

[DavidBuehler]

Hi James,

The best way I can describe one of them is parallel calculations.  Based on the way the questions are answered gives a total score.  based on that total score tell me if it is a high priority, medium priority, etc.  based on a value range.  I would like to display the high, medium, low, etc. message based on the value ranges.  These fields will be integer scores.

The other calculation is going to give the user a option message.  Based on the values of the questions give some secondary fields values then calculate those into another message based a value range.

my calculation would  if(${spalling} = 1, "1"), if(${spalling} = 0 , "0")  and there will be a series of these values that get added up

Is that possible?

[JamesTedrick]

Hi David,

Yes this should be possible- in that case the "0-5 value" could be though of as "Low" and the next as "medium", etc.  You can use the results of these calculations in the calculation field of a note question to display the results in the form.

[DavidBuehler]

Hi James,

I figured out a simple calculation based on the value entered from the previous question, but I am having trouble with my nested if statement.

if(${joint_separation}=16, 2, if(${joint_separation}=8, 1, if(${joint_separation}=0,0)))

basically if the value from joint separation is 16 give it a value of 2, if it is 8 give it a 1, and if it is 0 give it 0.  I keep getting an error of "the problem was located in calculate expression for ${join_calc}.  if requires 3 arguments, only 2 provided.

What am I missing?

[DavidBuehler]

Nevermind, apparently I needed an extra zero at the end.

[DavidBuehler · Accepted solution]

If figured it out based referencing this: Excel formula: Nested IF function example | Exceljet  

My examples

if(${workorder_calc}> 1, "REPLACE", if(${workorder_calc} = 1, "OPTION TO REPAIR", if(${workorder_calc}=0,"REPAIR",'')))

if(${joint_seperation}=16, 2, if(${joint_seperation}=8, 1, if(${joint_seperation}=0,0,0)))