AutoStatus Column based on Team Assignments

[Joel_Andrews1]

I’m guessing this scenario has already been covered, but I couldn’t find it anywhere. Feel free to point me to the relevant article if so.
I’ve got 5 status fields, one for each of 5 teams.

When a new project is added, the person adding needs to identify whether or not each team has any tasks to do in association with the project. If so, they pick “Yes”; if not, “No”.
Then each team is responsible to update the status in the field from there.
I want to create an AutoStatus column that put out the following:

• “In Progress”: if any of the team statuses is “In Progress”
• “On Hold”: if any of the team statuses is “On Hold”
• “Complete”: if all 5 statuses are either “Complete” or “No” (ignore "NA "- I’m taking that one out of the list)
• “Not Started”: if all 5 statuses are either “Yes” or “Reviewing”

This level of complexity in formulas is beyond me at this point, but I’m confident that someone here will know how to do what I’m looking for!

This will be an awesome step forward from what we’re doing now, using a manual status column, which allows someone to mark the whole project as “Complete” without getting confirmation from each of the teams involved.

Thanks in advance!

Joel

[Joel_Andrews1]

I should mention, in case it’s any help, that I also have %complete columns associated with each status, and a total %Complete column, that were created with lots of help from @Kamille_Parks and @kuovonne in this post.

[kuovonne]

What should the combined status be if ins team is “on hold” but another team is “in progress”?

[Joel_Andrews1]

Ideally, it would be “In Progress” if any of the teams are still “In Progress”, but “On Hold” if all other teams are “Complete”, “On Hold”, or “No”.

[kuovonne · Accepted solution]

There might be some typos in field names and values, but this should give you a start.

IF(
    OR(
        {Impl. Tasks?} = "In Progress",
        {Config Tasks?} = "In Progress",
        {Billing Tasks?} = "In Progress",
        {Tr. Tasks?} = "In Progress",
        {Rptg Tasks?} = "In Progress"
    ),
    "In Progress",
IF(
    OR(
        {Impl. Tasks?} = "On Hold",
        {Config Tasks?} = "On Hold",
        {Billing Tasks?} = "On Hold",
        {Tr. Tasks?} = "On Hold",
        {Rptg Tasks?} = "On Hold"
    ),
    "On Hold",
IF(
    AND(
        OR(
            {Impl. Tasks?} = "Complete",
            {Impl. Tasks?} = "No"
        ),
        OR(
            {Config Tasks?} = "Complete",
            {Config Tasks?} = "No"
        ),
        OR(
            {Billing Tasks?} = "Complete",
            {Billing Tasks?} = "No"
        ),
        OR(
            {Tr. Tasks?} = "Complete",
            {Tr. Tasks?} = "No"
        ),
        OR(
            {Rptg Tasks?} = "Complete",
            {Rptg Tasks?} = "No"
        )
    ),
    "Complete",
IF(
    AND(
        OR(
            {Impl. Tasks?} = "Yes",
            {Impl. Tasks?} = "Reviewing"
        ),
        OR(
            {Config Tasks?} = "Yes",
            {Config Tasks?} = "Reviewing"
        ),
        OR(
            {Billing Tasks?} = "Yes",
            {Billing Tasks?} = "Reviewing"
        ),
        OR(
            {Tr. Tasks?} = "Yes",
            {Tr. Tasks?} = "Reviewing"
        ),
        OR(
            {Rptg Tasks?} = "Yes",
            {Rptg Tasks?} = "Reviewing"
        )
    ),
    "Not Started",
    "Unknown"
))))

[Joel_Andrews1]

:exploding_head:
You. Are. The Best.

[kuovonne]

Thanks. I started writing the formula on my phone then decided that I really need a keyboard to complete it. You can see that the logic is a direct translation of what you wrote. The formatting style helps get the commas and parenthesis in the right places (although I still sometimes have typos with them).

[Joel_Andrews1]

Ok, I’m realizing there are a couple of gaps in my logic. Maybe you can help.
If any of the team status fields is blank, the output should be “ :question: :woman_shrugging: :question: ” (what I used in place of “Unknown”) - so it forces the person adding the project to choose “Yes” or “No” for each team.
I’m also getting “ :question: :woman_shrugging: :question: ” for a lot of other cases where I’d want something different:

If some answers are “No”, and others are either “Yes” or “Reviewing”, then the output should be “Not Started”.
If some are “Complete”, and others are still “Yes”, “Reviewing”, or “In Progress”, then the output should be “In Progress”.

Is there a way to make use of the %Complete calculation columns you helped me build to make this work?
Here’s the current version formula I use for each of the teams:
SWITCH({Rptg Tasks?},“No”, BLANK(), “Complete”, 1, “Yes”, 0.001, “Reviewing”, 0.05, “In Progress”, 0.50, “On Hold”, 0.50, 0)

And here’s the formula for calculating the totals (%Complete):
SUM({Impl. % value},{Config. % value},{Bill. % value},{Tr. % value},{Rep. % value})/COUNTA({Impl. % value},{Config. % value},{Bill. % value},{Tr. % value},{Rep. % value})

I tried the following, but Airtable didn’t like it (probably because I missed something or didn’t format it correctly):
IF({% Complete}>0,“ :stop_sign: Not Started”,(
IF({% Complete}>0.005,“ :mag: Reviewing”),(
IF({% Complete}>0.25,“ :building_construction: In Progress”),(
IF({% Complete}=1,“ :heavy_check_mark: Complete​:+1:”),
“ :question: :woman_shrugging: :question: ”)

[kuovonne]

Joel_Andrews1:

Is there a way to make use of the %Complete calculation columns you helped me build to make this work?

Probably, but I do not have time to tackle this now. As you can see the more complex the logic gets, the longer the formula is, and it is difficult to ensure that the logic is complete.

[Joel_Andrews1]

Sorry, I’m realizing that my math is totally wonky in that last formula (and formatting). I need to play around with this a bit more. I don’t think the number ranges will work, but maybe someone else has an idea.