Skip to main content
Solved

Conditional IF Statement Syntax Help

  • April 23, 2020
  • 2 replies
  • 33 views
D

Hi there,

I’ve been working on this and cannot seem to figure it out. I’m trying to write a conditional IF statements that essentially says -

IF Field A= 1 and Field B=2- output 3
IF Field B=1 and Field A=2- output 4
IF Field C = X- Output X

Here is the current syntax I have written- I’ve gotten it to do the first two parts, but cant get the last part running. Any help is wildly appreciated!!

IF(AND({Are you in recovery for a mental health disorder?}=‘Yes’,{Are you in recovery from a substance use disorder?}=‘No’),‘MH’,IF(AND({Are you in recovery from a substance use disorder?}=‘Yes’,{Are you in recovery for a mental health disorder?}=‘No’),‘SUD’),IF {Field 60}=’CO’,’Co’,’Z’)

Best answer by Jeremy_Oglesby

Hi @Diana_Kirby,

Question for you – do you want that last part ({Field 60}) to process its output all the time, regardless of the output of the other two fields above it? Or do you want it to only output if both of the conditionals before it fail (ie, there is no result from either of the two preceding conditionals – for example, if both of the fields = ‘Yes’ or both of them = ‘No’)?

If you want {Field 60} to always output its result, then this is what you want:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD'
  )
) &
IF(
  {Field 60} = 'CO',
  'Co',
  'Z'
)

But, if you want {Field 60} to output only in the case where the first two conditionals fail (which is what your current logic looks like it’s trying to do), then you want this:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD',
    IF(
      {Field 60} = 'CO',
      'Co',
      'Z'
    )
  )
)

2 replies

J
Forum|alt.badge.img+18

Hi @Diana_Kirby,

Question for you – do you want that last part ({Field 60}) to process its output all the time, regardless of the output of the other two fields above it? Or do you want it to only output if both of the conditionals before it fail (ie, there is no result from either of the two preceding conditionals – for example, if both of the fields = ‘Yes’ or both of them = ‘No’)?

If you want {Field 60} to always output its result, then this is what you want:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD'
  )
) &
IF(
  {Field 60} = 'CO',
  'Co',
  'Z'
)

But, if you want {Field 60} to output only in the case where the first two conditionals fail (which is what your current logic looks like it’s trying to do), then you want this:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD',
    IF(
      {Field 60} = 'CO',
      'Co',
      'Z'
    )
  )
)
D
D
  • Diana_Kirby
  • Author
  • New Participant
  • April 23, 2020

Hi @Diana_Kirby,

Question for you – do you want that last part ({Field 60}) to process its output all the time, regardless of the output of the other two fields above it? Or do you want it to only output if both of the conditionals before it fail (ie, there is no result from either of the two preceding conditionals – for example, if both of the fields = ‘Yes’ or both of them = ‘No’)?

If you want {Field 60} to always output its result, then this is what you want:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD'
  )
) &
IF(
  {Field 60} = 'CO',
  'Co',
  'Z'
)

But, if you want {Field 60} to output only in the case where the first two conditionals fail (which is what your current logic looks like it’s trying to do), then you want this:

IF(
  AND(
    {Are you in recovery for a mental health disorder?} = 'Yes',
    {Are you in recovery from a substance use disorder?} = 'No'
  ),
  'MH',
  IF(
    AND(
      {Are you in recovery for a mental health disorder?} = 'No',
      {Are you in recovery from a substance use disorder?} = 'Yes'
    ),
    'SUD',
    IF(
      {Field 60} = 'CO',
      'Co',
      'Z'
    )
  )
)

Complete Lifesaver. Thank you so much! First round or two of learning IF statements and kept scratching my head on this. Appreciate it so much.

J
Terms & ConditionsAccessibility statement