Formula to update a cell

Terry Bonet

I want my column for progress (Not started - In progress - Complete) to update when someone adds the % complete. So is "0" then read "Not Started", If < the 0 but not 100% then "In Progress" and if 100% then "Complete". Does anyone know that formula?

Mathieu PERSICO

Hi Terry,

Let suppose you have a column "%" which is your input and a column "Status" where is your formula.

The formula to enter would be someting like:

=IF(ISBLANK([%]1), "", IF([%]1 = 0, "Not Started", IF([%]1 < 100, "In Progress", "Completed")))

Hope this helps. Have a nice day!

Mathieu @evolytion

formula to update a cell.PNG

Andrée Starå

Hi @Terry Bonet

To add to Mathieu's excellent answer.

You'll need to change the < 100 to < 1 for it to work with a percentage formatted column.

Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1. You'd need to use decimal values instead for it to work 

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1 

Depending on your country/region, you'll need to exchange the comma to a period.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!