Help

Conditional IF Statement Syntax Help

Topic Labels: Formulas
Solved
Jump to Solution
989 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Diana_Kirby
4 - Data Explorer
4 - Data Explorer

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’)

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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'
    )
  )
)

See Solution in Thread

2 Replies 2
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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.