# Survey123 Connect Nested Formula for Values

351
2
08-06-2020 01:08 PM
New Contributor II

I believe I have a fairly straight forward nested 'if' statement here, but I'm missing something as I keep receiving errors. Any help would be appreciated. Thanks! Any thoughts James Tedrick?

Here is the expression within a calculate field type. The field name is {risk_rating_calculate}:

if(\${percent_rating}>=80,'SUSTAINABLE', if(\${percent_rating}<80 and >=50,'AT RISK', if(\${percent_rating}<50,'NOT SUSTAINABLE','')))

The error being thrown is this:

{percent_rating} is an integer field that references a previous calculate expression (see attachment screenshot).

-Corey

Tags (3)
1 Solution

Accepted Solutions
MVP Notable Contributor

This seems to come up a lot.  With an and or or you have to give it the entire expression with field name not just a second value.  You can combine anything not just multiple values so need to be explicit.  Also you do not need the last if since it is the only option left (unless you have something else going on).

if(\${percent_rating}>=80,'SUSTAINABLE', if(\${percent_rating}<80 and \${percent_rating} >=50,'AT RISK', 'NOT SUSTAINABLE'))

Hope that helps

2 Replies
MVP Notable Contributor

This seems to come up a lot.  With an and or or you have to give it the entire expression with field name not just a second value.  You can combine anything not just multiple values so need to be explicit.  Also you do not need the last if since it is the only option left (unless you have something else going on).

if(\${percent_rating}>=80,'SUSTAINABLE', if(\${percent_rating}<80 and \${percent_rating} >=50,'AT RISK', 'NOT SUSTAINABLE'))

Hope that helps

New Contributor II

Thank you, Doug Browning! This works perfectly :)

Just the answer I was looking for. Thank you for your insight regarding this expression and your quick response! I was running into a wall on this one and you saved me some valuable time. Much appreciated!

Corey