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
Check out the Formula Handbook template!