Creating a %Complete Field based on selections in several single-select fields

[Joel_Andrews1]

Hi all!

I’m trying to create a %Complete column in a project table that includes several single-select fields used to track 1. whether or not a particular team has tasks associated with the project, and 2. what that team’s progress is on those tasks if so.

I’m thinking what I need to do is create a formula column for each one of these, assigning a number to the values selected, a column that totals these up, and a column that calculates the % complete by dividing the total complete by the total that needs to be complete.
Maybe use the SWITCH formula to assign numeric values?
I just don’t know how to set up conditional formulas that complex at this point. Can anyone help me?

Thanks very much!

[Kamille_Parks]

Joel_Andrews1:

I’m thinking what I need to do is create a formula column for each one of these, assigning a number to the values selected

The formula for that could be:

SWITCH(
{Impl. Project?},
"No", 0.10,
"Reviewing", 0.20,
"Yes", 0.30,
"In Progress", 0.5,
"Complete", 1
)

(I used decimals because you want percentages)

Joel_Andrews1:

a column that totals these up

That would be

{Formula 1} + {Formula 2} + {Formula 3} ...

[Joel_Andrews1]

Thanks for the help, Kamille!

Here’s the formula I ended up using for each of the SWITCH fields:
SWITCH(
{Impl. Project?},
“Yes”, 0.01,
“Reviewing”, 0.05,
“In Progress”, 0.50,
“On Hold”, 0.50
“No”, 1,
“NA”, 1,
“Complete”, 1,
)

I entered “1” for “No”, “NA”, and “Complete”, since I didn’t want No and NA to reduce the total.

Then for the % Complete field I used the following (formatted as a percentage):
(
{Impl. % value}+
{Config. % value}+
{Bill. % value}+
{Tr. % value}+
{Rep. % value}
)
/5

It works perfectly! I can see that SWITCH will really come in handy. The only thing I don’t understand yet is how to set the default value.

Thanks again for your help!

[kuovonne]

Joel_Andrews1:

The only thing I don’t understand yet is how to set the default value.

You can set the default in a SWITCH function by including an additional value at the end after all the pairs.

SWITCH( {Impl. Project?},
    "Yes", 0.01,
    "Reviewing", 0.05,
    "In Progress", 0.50,
    "On Hold", 0.50
    "No", 1,
    "NA", 1,
    "Complete", 1,
    0
)

---

Joel_Andrews1:

I entered “1” for “No”, “NA”, and “Complete”, since I didn’t want No and NA to reduce the total.

Having 1 for “No” and “NA” will not reduce the total, but it will artificially inflate the percent complete.

Instead you can leave the % blank for “No” and “NA”, and divide by a count of non-blank values.

SWITCH( {Impl. Project?},
    "Yes", 0.01,
    "Reviewing", 0.05,
    "In Progress", 0.50,
    "On Hold", 0.50
    "No", BLANK(),
    "NA", BLANK(),
    "Complete", 1,
    0
)

For the average of all the percentages …

SUM(
    {Impl. % value},
    {Config. % value},
    {Bill. % value},
    {Tr. % value},
    {Rep. % value}
)
/
COUNT(
    {Impl. % value},
    {Config. % value},
    {Bill. % value},
    {Tr. % value},
    {Rep. % value}
)

[Joel_Andrews1]

kuovonne:

For the average of all the percentages …

SUM(
    {Impl. % value},
    {Config. % value},
    {Bill. % value},
    {Tr. % value},
    {Rep. % value}
)
/
COUNT{
    {Impl. % value},
    {Config. % value},
    {Bill. % value},
    {Tr. % value},
    {Rep. % value}
}

Ok, I understand what you mean about inflating the % complete, and I updated the formulas with the BLANK values as you suggested, but when I entered the formula above to calculate the % complete, I get Infinity and NaN as the results. What am I missing?
Here’s exactly what I entered:

SUM(
{Impl. % value},
{Config. % value},
{Bill. % value},
{Tr. % value},
{Rep. % value}
)
/
COUNT(
{Impl. % value},
{Config. % value},
{Bill. % value},
{Tr. % value},
{Rep. % value}
)

The only difference I can see from yours is that you used curly brackets around the COUNT rather than parens, which I assume was a typo, because the field wouldn’t accept it as written.

In case I messed up the formula in the other fields, here’s what I used:

SWITCH(
{Impl. Impacts?},
“No”, BLANK(),
“NA”, BLANK(),
“Complete”, 1,
“Yes”, 0.01,
“Reviewing”, 0.05,
“In Progress”, 0.50,
“On Hold”, 0.50
)

Thanks!

[kuovonne]

Joel_Andrews1:

which I assume was a typo

Yup. Typo from trying to type the formula on my tiny phone screen. Thanks for fixing it.

Joel_Andrews1:

get Infinity and NaN as the results

Do you have actual values in your records? If all of the values are blank or zero, then you are dividing by zero, which is not mathematically allowed and Airtable shows the result as “NaN” (for zero divided by zero) or “Infinity” for any other number divided by zero. This support article has more info.

One solution is to wrap the whole thing in an IF function.

IF(
    OR(
        {Impl. % value} & "",
        {Config. % value} & "",
        {Bill. % value} & "",
        {Tr. % value} & "",
        {Rep. % value} & ""
    ),
    SUM(
        {Impl. % value},
        {Config. % value},
        {Bill. % value},
        {Tr. % value},
        {Rep. % value}
    )
    /
    COUNT(
        {Impl. % value},
        {Config. % value},
        {Bill. % value},
        {Tr. % value},
        {Rep. % value}
    )
)

[Joel_Andrews1]

kuovonne:

Do you have actual values in your records? If all of the values are blank or zero, then you are dividing by zero, which is not mathematically allowed and Airtable shows the result as “NaN” (for zero divided by zero) or “Infinity” for any other number divided by zero. This support article has more info.

One solution is to wrap the whole thing in an IF function.

Hmmmm… Still not working yet. I’m still not really solid on the use of IF, AND, and OR. I assume you meant for me to enter the formula exactly as you have it? Or were the pairs of quotes in the OR section implying that I should put something else there? If the former, I don’t understand the function of the quotes there.

Either way, I’m still getting NaN and Infinity, even when I’ve entered values for each of the 5 teams.

Here’s what I’m going for. The person entering the ticket needs to identify whether or not each of the 5 teams needs to take any action in order to resolve it. If they enter “No”, then it should not count against the percentage of completion. If they enter “Yes”, then it should. (I set the value for “Yes” to .01 rather than 0, because I thought that would be necessary for calculating the percentage total, but perhaps not.) I do want there to be an error message if a value is not entered for each of the teams, because I want to ensure that their piece is considered for each ticket. But if “No” or “NA” is selected, then it shouldn’t result in an error or count against the %complete.

Thanks again for the help!

[kuovonne]

Can you post a screenshot showing all the relevant input and formula fields?

[Joel_Andrews1]

Here are the options for each of the 5 team fields:

Here’s the SWITCH formula used for each team:

And here’s the % Complete formula:

[kuovonne · Accepted solution]

I did a bit more digging.

Try converting the COUNT to COUNTA.

It looks like the inclusion of BLANK() is throwing off the formula and forcing the formula result of the SWITCH to a string, instead of a number. This is really weird because SUM is still treating the value as number and not a string.