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

Nick Korna

Oct 20, 2023

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

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

Nick Korna

Oct 20, 2023

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.

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

Nick Korna

Oct 20, 2023

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.

Nick Korna

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

malanto

Nick Korna

Oct 20, 2023

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

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