Task completion (%)

malanto

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

Nick Korna

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! 🙂

Nick Korna

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! 🙂

malanto

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!

malanto

https://community.smartsheet.com/discussion/comment/400877#Comment_400877

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!

Nick Korna

Are you getting an error message when the formula is entered?

malanto

https://community.smartsheet.com/discussion/comment/400889#Comment_400889

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.

Nick Korna

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. 🤔

malanto

https://community.smartsheet.com/discussion/comment/400892#Comment_400892

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.

Nick Korna

I've not seen something like that before - are you using some premium addons (which I don't have)?

malanto

https://community.smartsheet.com/discussion/comment/400894#Comment_400894

Do you know this https://help.smartsheet.com/articles/765755-modifying-project-settings ?