Select to view content in your preferred language

# if and or statement in Calculate

240
1
05-17-2024 01:35 PM
New Contributor II

Ive done a lot of work trying to figure out how to write this nest if then, or and statement to use in my calculate field of my survey. Essentially this field is to calculate a risk rating of a culvert based on answers to questions in the survey so I can automate this without having to do it all at the end of the season. Maybe there is an easier way to do this as well. Im going to attach a notepad file of one of my many attempts to get this correct. Any help is appreciated. Ive looked at a lot of documents and other community responses but just cant seem to get into this type of depth.

by
MVP Regular Contributor

IF( ) statements have 3 components, no exceptions:

1. Logical Test = Your question which results in true of false.
2. Value if True = happens if the logic test is true.
3. Value if False = happens if the logic test is false.

IF([logical test],[value if true],[value if false])

TIP: When working with IF() statements in Survey123 Connect, you can trick Excel into helping you write them. If you add "=" to the front of your IF() statement, Excel will give you 1) colour-coded formatting for brackets (it sometimes disappears though), and 2) when you mouse over a bracket it will highlight the matching bracket (if any).

Looking at your IF() statement, you have some incorrect formatting. For example:

if(\${Ponding_Overflow}, 'Yes' and \${Channel_Alignment},

1. Your logic test appears non-existant. Now, if \${ponding_overflow} results in "true" or "false", it would work, but I can't tell that from this statement alone.
2. The second part is supposed to be your value if true, which consists of two separate things: a 'yes' and \${channel_alignment}, which don't go together.

Nesting IF() statements is just a regular IF() statement with extra steps. When it comes to very complex nested IF() statements, I usually write them out and then fill in the blanks to ensure I don't miss anything. E.g.,

if(111,222,if(111,222,if(111,222,if(111,222,if(111,222,if(111,222,3333))))))