Task completion (%)
Hello everyone,
is there a way to set the % complete based on the task status?
I have a table
[state ; % complete]
I tried with "VLOOKUP" function but isn't possible to insert formulas in the field.
Help me please!
Thanks
Best Answer
-
Hi @malanto,
This is possible, but you would need to define the criteria.
You would use a nested IF statement. A very basic example:
Status % Complete
Not Started 0
In Progress 50
Complete 100
Would have the following formula in the % Complete column:
=IF([Status]@row="Not Started",0,IF([Status]@row="In Progress",0.5,IF([Status]@row="Complete",1,"")))
This assumes you've set the column to be %, otherwise you'd want to increase the numbers by 100 (0.5 to 50).
Hopefully this gives you some ideas, but if you're still unsure or have any problems/questions then just ask! 🙂
Answers
-
Hi @malanto,
This is possible, but you would need to define the criteria.
You would use a nested IF statement. A very basic example:
Status % Complete
Not Started 0
In Progress 50
Complete 100
Would have the following formula in the % Complete column:
=IF([Status]@row="Not Started",0,IF([Status]@row="In Progress",0.5,IF([Status]@row="Complete",1,"")))
This assumes you've set the column to be %, otherwise you'd want to increase the numbers by 100 (0.5 to 50).
Hopefully this gives you some ideas, but if you're still unsure or have any problems/questions then just ask! 🙂
-
Hi @Nick Korna ,
thank you.🤗
I had thought about creating a table [status ; % complete] in a separate sheet and using the VLOOKUP function.
If I change the % to a status, thanks to the separate table I don't have to change the formula in all the sheets.
I couldn't insert the formula because I was trying to do it on a line that contains secondary activities.😑
Now I understand how to do it.😊
Thank you ! you are fantastic ! 😃
Have a nice day!
-
Hi @Nick Korna
I tried to enter the formula in the field but unfortunately I can't get the desired result.
In the project settings I set % Complete column = % complete
When I try to insert a formula into one of the fields of "% complete" column, the formula is not recognized@Nick Korna
Can you help me please?
Thank you!
-
Are you getting an error message when the formula is entered?
-
No nothing.
The formula appears as normal text.
While I was typing or pasting the formula I noticed that at the beginning there is a ' symbol which I didn't insert and which I can't delete.
-
The ' is what is causing the formula to appear as text, but I'm not sure what makes this happen even if you're typing the formula in. 🤔
-
Could it be because I set that column as % complete column in the project parameters?
I need this so that the "PARENTS" fields are filled in correctly way
Thank you.
-
I've not seen something like that before - are you using some premium addons (which I don't have)?
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
