What formula to use in Status column based on % Complete column?
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.
Best Answer

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
Answers

See if this might work for you.
=IF([% Complete]@row < 1, "Not Started", IF([% Complete]@row < 100, "In Progress", "Complete"))

@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?

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

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

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

@Genevieve P. Thank you! That worked perfectly and the explanation was so helpful.
Help Article Resources
Categories
Check out the Formula Handbook template!