Help

IF contains nested formula help

Topic Labels: Formulas
Solved
Jump to Solution
1584 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Addie_Dunham
4 - Data Explorer
4 - Data Explorer

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

 

 

1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

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

See Solution in Thread

3 Replies 3
Ben_Young1
11 - Venus
11 - Venus

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

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().

Thank you so much !