I need help with the Formula Field (If And, If Not)

Question

Hi there!
I’m trying to build out a formula that allows me to use checkboxes to update an application status column. As of now, this is my current formula:
IF(
AND({Reviewed}, {Accepted}),
‘Accepted’,
IF(
{Accepted},
‘Error!’,
IF(
NOT({Reviewed}),
‘Pending Review’,
‘Rejected’
)
)
)

My hope is to have a three-column system where if you check {Reviewed} it displays the formula column as ‘Reviewed’, {Reviewed} and {Accepted} as ‘Accepted’, {Reviewed} and {Rejected} as ‘Rejected’, and {Reviewed} and {Accepted} and {Rejected} as ‘Error!’. Please let me know how to go about this. Major thanks!

JonathanBowen · Accepted Answer

Hi @Julian_Aguilar - your screenshot is cut off I think. You’re saying you want to show the status based on 3 checkboxes - Reviewed, Accepted and Rejected, yes?

Can I suggest an alternative approach? Accepted and Rejected are part of the same thing, so ideally you’d cover this in a single field. The problem with having these as two separate fields is there’s many opportunities for them to conflict, leading to a lot of error states - not a big deal, but ideally they would be engineered out. In addition, this single field has the concept of “neither accepted or rejected as we haven’t got to that stage of the process”.

So, instead of 3 binary state fields, you might be better off with one binary state field (Reviewed/Not Reviewed) and a 3-state field (Accepted/Rejected/Neither). You’d end up with something like this:

where the status formula is:

IF(
  AND(NOT({Reviewed}), NOT({Accepted})),
  'Pending review',
  IF(
    AND({Reviewed}, NOT({Accepted})),
    'Reviewed',
    IF(
      AND(NOT({Reviewed}), {Accepted} = 'accepted'),
      'Error',
      IF(
        AND({Reviewed}, {Accepted} = 'accepted'),
        'Accepted',
        IF(
          AND(NOT({Reviewed}), {Accepted} = 'rejected'),
          'Error',
          'Rejected'
        )        
      )      
    )    
  )  
)

Thanks! Just an FYI, my SS was just for what I had already but thanks for this tip.

Julian_Aguilar · Answer

Hi @Julian_Aguilar - your screenshot is cut off I think. You’re saying you want to show the status based on 3 checkboxes - Reviewed, Accepted and Rejected, yes?

Can I suggest an alternative approach? Accepted and Rejected are part of the same thing, so ideally you’d cover this in a single field. The problem with having these as two separate fields is there’s many opportunities for them to conflict, leading to a lot of error states - not a big deal, but ideally they would be engineered out. In addition, this single field has the concept of “neither accepted or rejected as we haven’t got to that stage of the process”.

So, instead of 3 binary state fields, you might be better off with one binary state field (Reviewed/Not Reviewed) and a 3-state field (Accepted/Rejected/Neither). You’d end up with something like this:

where the status formula is:

IF(
  AND(NOT({Reviewed}), NOT({Accepted})),
  'Pending review',
  IF(
    AND({Reviewed}, NOT({Accepted})),
    'Reviewed',
    IF(
      AND(NOT({Reviewed}), {Accepted} = 'accepted'),
      'Error',
      IF(
        AND({Reviewed}, {Accepted} = 'accepted'),
        'Accepted',
        IF(
          AND(NOT({Reviewed}), {Accepted} = 'rejected'),
          'Error',
          'Rejected'
        )        
      )      
    )    
  )  
)

Thanks! Just an FYI, my SS was just for what I had already but thanks for this tip.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded