Multiple IF/AND/OR/ Functions

Taylor Riley

Hi! I thought I had figured this out... but midway through making changes, realized that it's still not working quite as effectively has I thought. I'm currently using the below formula to change the Offer Phase parent row from "In Progress" to "Complete" if the following conditions are met:

• If the Offer phase in row 770 (representing a task for Team 1) is either "Complete" or "Reviewed: No Action Required From Team" AND if the Offer Phase in row 775 (representing a task for Team 2) is either "Complete" or "Reviewed: No Action Required From Team". If True, offer phase should change to "Complete", if false to "In Progress"

It's working as expected for the following scenarios:

• Both Rows have status - "Reviewed: No Action Required From Team" (Changes to Complete)
• Both Row have status - "Complete" (Changes to Complete)
• Team 1 has status "Complete", Team 2 has status "Reviewed: No Action Required From Team" and vice versa (Changes to Complete)
• Team 1 has status "Complete", Team 2 has status "In Progress" and vice versa (Stays as In Progress)

It's not working as expected in the following scenario:

• Team 1 has status "In Progress", Team 2 has status "Reviewed: No Action Required From Team" and vice versa (Changes to complete)

Current Formula:

=IF(OR(AND([Offer Phase]770 = "Complete", [Offer Phase]775 = "Complete"), OR([Offer Phase]770 = "Reviewed: No Action Required From Team", [Offer Phase]775 = "Reviewed: No Action Required From Team")), "Complete", "In Progress")

Any suggestions would be greatly appreciated!

David Joyeuse

Hi @Taylor Riley

If I got you right:

If both rows are either "Complete" or "Reviewed: No Action Required From Team", then you want the parent phase to show: "Complete".

But if either one of them is "In Progress", then you want the parent row to display "In Progress".

In your formula, you're using one big OR function, that is right if either row is in "Reviewed: No Action Required From Team". So obviously, this will return "Complete" most of the time.

I would suggest to do this backward, as the main important status here is "In Progress". If that ever appear, then you have to display "In Progress".

So you could short your formula like this:

=IF(OR([Offer Phase]$770 = "In Progress", [Offer Phase]$775 = "In Progress"), "In Progress", "Complete")

The OR function will return False if no cell is "In Progress", meaning that either one is "Complete" or "Reviewed: No Action Required From Team".

Which should cover all of your scenarii :)

Hope it helped!

David Joyeuse

Hi @Taylor Riley

If I got you right:

If both rows are either "Complete" or "Reviewed: No Action Required From Team", then you want the parent phase to show: "Complete".

But if either one of them is "In Progress", then you want the parent row to display "In Progress".

In your formula, you're using one big OR function, that is right if either row is in "Reviewed: No Action Required From Team". So obviously, this will return "Complete" most of the time.

I would suggest to do this backward, as the main important status here is "In Progress". If that ever appear, then you have to display "In Progress".

So you could short your formula like this:

=IF(OR([Offer Phase]$770 = "In Progress", [Offer Phase]$775 = "In Progress"), "In Progress", "Complete")

The OR function will return False if no cell is "In Progress", meaning that either one is "Complete" or "Reviewed: No Action Required From Team".

Which should cover all of your scenarii :)

Hope it helped!

Taylor Riley

That worked beautifully!! Thank you!