Help

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

Topic Labels: Formulas
Solved
Jump to Solution
598 11
cancel
Showing results for 
Search instead for 
Did you mean: 

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.
image

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

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.

See Solution in Thread

11 Replies 11

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} ...

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!

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

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!

Labels