What formula to use in Status column based on % Complete column?

amber.lange

Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work.

Status column: Not Started, In Progress, or Complete

% Complete column: any number (column is set to %'s)

This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In Progress, IF % Complete = 100, Status=Complete.

This is the last example of what I tried=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 100), "In Progress", IF([% Complete]@row = 100, "Complete")))

Thanks in advance.

Genevieve P.

Hi @amber.lange & @John Hamilton

Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.

Try John's same formula but with decimals instead:

=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

You could also write this another way around:

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))

Cheers,

Genevieve

John Hamilton

See if this might work for you.

=IF([% Complete]@row < 1, "Not Started", IF([% Complete]@row < 100, "In Progress", "Complete"))

amber.lange

@John Hamilton Thank you, I believe I already tried that, but did test again. The issue keeps coming up that when the % Complete is 100%, the status column will not update to Complete. If I remove the formatting of percentages from the % Complete column, the formulas seem to work, but we would prefer to keep the format as a percentage. Make sense?

John Hamilton

I set up some test data and it seemed to work for me using numbers only with no % sign.

Genevieve P.

Hi @amber.lange & @John Hamilton

Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.

Try John's same formula but with decimals instead:

=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

You could also write this another way around:

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))

Cheers,

Genevieve

John Hamilton

@Genevieve P. Thanks for the clarification. Always great to learn new "stuff"

amber.lange

@Genevieve P. Thank you! That worked perfectly and the explanation was so helpful.