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

Solved
Jump to Solution
1504 0
cancel
Showing results for
Search instead for
Did you mean:
7 - App Architect

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!

1 Solution

Accepted Solutions
18 - Pluto

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.

11 Replies 11
16 - Uranus

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)

That would be

``````{Formula 1} + {Formula 2} + {Formula 3} ...
``````
7 - App Architect

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!

18 - Pluto

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

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}
)
``````
7 - App Architect

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!

18 - Pluto

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

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}
)
)
``````
7 - App Architect

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!

18 - Pluto

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

7 - App Architect

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:

18 - Pluto

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.

7 - App Architect

Hurray!!! That did the trick! Thank you so much! This will be great!

7 - App Architect

As a bonus for anyone that’s interested in replicating, here’s a shot from the interface I built for this: