% Complete Parent Row

CAMILA

I just started using Smartsheet and I was wondering if someone could help me. I want to basically calculate the % complete, as the dependency setting does for you automatically.  

However since I entered a formula in my % complete column (if status is "done" then automatically give 100%, if status is "in progress" then automatically give 50%, etc.), I can no longer use the dependency setting. Is there a formula to get % complete based on the percentages in the children rows?

I have tried a couple of things but they all led to #cannot divide by zero errors

 

Would appreciate any help! 

Paul Newcome

Try something like this...

 

=AVERAGEIF(CHILDREN(), ISNUMBER(@cell))

CAMILA

Hey Paul, thank you for your suggestion! It was definitely a step in the right direction, however it just seems to only be taking into account the line below it and ignoring all others. Sorry maybe a bit of an redundant post, but I just cannot seem to figure it out!

Andrée Starå

Hi Camila,

Is the line below a child and then next after a parent? How's your hierarchy?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots of the sheet with the hierarchy? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

As Andree suggested... Double check your hierarchy.

 

If that does not correct the issue, a screenshot including the Primary Column would definitely be extremely useful.

CAMILA

Hey Paul and Andree,

I uploaded a screenshot of the smartsheet and also shared it with you Andree.

I think it does indeed have something to do with my hierarchy, but since I am very new to smartsheet i am not sure where its going wrong

Andrée Starå

Thanks!

I'll take a look and get back to you!

Paul Newcome

I am sure Andree will be able to help you figure it out, but a quick tip when dealing with hierarchies...

 

When you need to double check how far a row has been indented or outdented, entering placeholder data into the Primary column will really help to see it. It doesn't even have to be anything in particular. Just randomly punching a few letters in will allow you to see where the left justification falls for each cell which will tell you where each row is lying within the hierarchy.

Andrée Starå

The formula looks correct.

Try adding it to all parent cells in the Complete column and then save and refresh the sheet.

Did it work?

Andrée Starå

Glad we got it working!

Have a fantastic weekend!

Best,

Andrée