% Complete Parent Row

CAMILA · 11/13/19

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!

Capture SMARTSHEET QUESTION.PNG

Paul Newcome · 11/13/19

Try something like this...

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

CAMILA · 11/14/19

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!

Capture SMARTSHEET QUESTION_0.PNG

Andrée Starå · 11/14/19

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 · 11/14/19

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 · 11/14/19

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

Capture SMARTSHEET QUESTION_1.PNG

Andrée Starå · 11/14/19

Thanks!

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

Paul Newcome · 11/14/19

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å · 11/14/19

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å · 11/15/19

Glad we got it working!

Have a fantastic weekend!

Best,

Andrée

% Complete Parent Row

Comments

Help Article Resources

Categories