Multiple IF/AND/THEN formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello community,
I have a multiple if/and statment with the logic in the image below. If Resident is selected Yes, and Visitor is selected Yes, No or blank, then return "Yes". And the other way around, if Visitor is selected Yes, and Resident is selected either Yes or No, return as "Yes". The only time it should return "No" is if both Resident and Visitor are both No, or is visitor is blank, and resident is No. I hope this makes sense. I played around with the formula below, but it is not returning desired output.
IF [Resident] IN ("Y") AND [Visitor] IN ("Y","N","Blank") THEN
"Yes"
ELSEIF [Resident] IN ("N","Blank") AND [Visitor] IN ("Y") THEN
"Yes"
ELSE
"No"
ENDIF
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
would it not be:
if resident = 'yes' or ( resident ='No' and Visitor='Yes')
then
'yes'
else
'No'
endif
you only have 6 possible outcomes...if you look at this as a table:
Resident | Visitor | Outcome |
Y | Y | Y |
Y | N | Y |
Y | B | Y |
N | Y | Y |
N | N | N |
N | B | N |
Fix my statement to ensure that the syntax works.
let me know it if works.
-prpatel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It may be easier to code for your No case and allow others to be Yes.
I used this formula to make a result column. if [Resident] = "N" and ([Visitor] = "N" OR [Visitor]="Blank") then "No" else "Yes" endif
Or you can use this if you still want to use the IN function
if [Resident] = "N" and [Visitor] IN("N","Blank") then "No" else "Yes" endif
When only checking against a single value though, = is probably the better operation to use which is why I didnt use it for the [Resident] = "N" case.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This worked, thank you!
