Jan 07, 2022 07:16 AM
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!
Solved! Go to Solution.
Jan 09, 2022 08:13 AM
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.
Jan 07, 2022 02:10 PM
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} ...
Jan 07, 2022 03:48 PM
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!
Jan 07, 2022 04:36 PM
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}
)
Jan 08, 2022 06:51 AM
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!
Jan 08, 2022 05:50 PM
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}
)
)
Jan 09, 2022 06:41 AM
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!
Jan 09, 2022 06:53 AM
Can you post a screenshot showing all the relevant input and formula fields?
Jan 09, 2022 07:25 AM
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:
Jan 09, 2022 08:13 AM
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.