IF contains nested formula help

Question

We have a system for reviewing new tools where they are checked for meeting accessibility standards and security standards. These are two separate teams with different workflows. The integration team needs to know when a tool review is considered complete.I have two columns; one for the accessibility phase, one for security phase. I'm trying to make a formula for overall review status. There are multiple decision statuses for each. I would like to make the formula display either "Awaiting Decision" or "Review Complete". Review Complete would show if both columns have a value that indicates a decision was made.For example, for tool A, the accessibility status = Testing Not Required and security status = Complete, it should be marked Review Completed in the formula column.For tool B, the accessibility status = Completed and security status = Intake, it should be marked as "Awaiting Decision".I've tried a variety of IF(FIND( arrangements but I can't seem to get it right. I only saved the most recent attempt, which was more simplified than what I'd like, but I'm not sure where I went awry.Any help would be extremely appreciated. IF(
  FIND(
    'Completed',
    {Accessibility Status Phase}, 
    IF(
      FIND(
        'Completed',
        {Security Campus Phase},
        ),
      'Review Completed',
      )
      'Awaiting Decision'
    )
   'Awaiting Decision'
)

Ben_Young1 · Accepted Answer

Hey @Addie_Dunham!

Here are two different formulas that should get you what you're looking for based on the details you've provided:

IF(
    AND(
        {Accessibility Status Phase} = "Completed",
        {Security Campus Phase} = "Completed"
    ),
    "Review Completed",
    IF(
        OR(
            {Accessibility Status Phase},
            {Security Campus Phase}
        ),
        "Awaiting Decision"
    )
)

IF(
    AND(
        FIND(
            "Completed",
            {Accessibility Status Phase}
        ),
        FIND(
            "Completed",
            {Security Campus Phase}
        )
    ),
    "Review Completed",
    IF(
        OR(
            {Accessibility Status Phase},
            {Security Campus Phase}
        ),
        "Awaiting Decision"
    )
)

Thank you so much !

kuovonne · Answer

The formula that you posted has parentheses in the wrong places and is missing commas.

Since your fields are single selects, you should be able to use = instead of FIND(), which might make things simpler. You can also consider using SWITCH().

IF contains nested formula help

1 Attachments

3 replies

1 Attachments

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded