Formula for parent row that calculates % of child rows, based upon date durations

LisaB:-)

Hi all

I use a manual formula in my parent rows to calculate % Complete, because we chose to have the % Complete column update automatically based upon the Status column ("Complete" = 100%). The parent rows have the following formula:

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, AVG(CHILDREN()))

Which calculates the %, worked out by adding together the % of the child rows and dividing them by how many child rows there are, so a standard average calculation.

The problem occurs when I have, say, 3 child rows but the first is a duration of 5 days, and the second and third is a duration of 30 days. If the first child row is Complete, then current formula would return something like 33%, which is not correct, because only 5 days have elapsed out of a total of 65. Could somebody please suggest a more accurate formula?

Many thanks

Lisa

L_123

In that case you can still use the formula, just without the parent check, and just post the formula to the parent cells.

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, avgw(children(),children(duration@row)

this will allow the duration of the task to act as the "weight" of the task.

https://help.smartsheet.com/function/avgw

David Joyeuse

Hi @LisaB:-)

I'm not sure what you are trying to do here. If I read you right it seems that in your % Complete column you have your parent in the parent row then some duration in the children rows?

Could you provide us a screenshot or two with no sensitive data or confidential one on it so we get a better clue of what you're trying to accomplish here. I think an helper column or two would help here.

But right now I would say you need to have this formula in your child rows (considering you have a starting date column :

=IF(OR(Status@row="Complete", Status@row="Not Required"),1, VALUE((TODAY()-[Starting Date]@row)/[Duration]@row))

Then in the parent row:

=IF(OR(Status@row="Complete",Status@row="Not Required"),1,SUM(CHILDREN())/COUNT(CHILDREN([Main Column]@row)))

LisaB:-)

Hi, thanks for the reply, I think you have interpreted correctly. This is a screenshot of my original sheet. Note that for Task 1, it's saying 50% complete, when it's actually only 11% complete, because it's only 1 day out of 11.

Here's a shot where I have substituted your formulas. It appears to ALMOST work, but it returns a negative % value for dates in the future, which gives a false reading in the parent row.

I really appreciate your help, thank you. I have a meeting for a couple of hours, so if I go quiet, I'll be back around later.

Genevieve P.

Hi @LisaB:-)

What if you added in a statement that output a 0 if the status is Not Started?

Try this for your child rows:

=IF(OR(Status@row="Complete", Status@row="Not Required"),1, IF(Status@row = "Not Started", 0, VALUE((TODAY()-[Starting Date]@row)/[Duration]@row)))

David Joyeuse

@LisaB:-)

Either @Genevieve P 's solution, or you could also use this:

=IF(OR(Status@row="Complete", Status@row="Not Required"),1, IF(TODAY() > [Starting Date]@row, 0, VALUE((TODAY()-[Starting Date]@row)/[Duration]@row)))

Really up to you on how you want this to work :)

LisaB:-)

Hi, @Genevieve P and @David Joyeuse adding both of those last formulas into the child rows hasn't made any difference to the problem, which is that the parent row does not calculate the % completion accurately. Or am I misunderstanding?

David Joyeuse

@LisaB:-)

We just removed the fact that it was calculating negatives values. What is it that not still acurate in the formula?

LisaB:-)

Hi, thanks for re-engaging.

This is the using this formula in the child rows:

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, IF(Status@row = "Not Started", 0, VALUE((TODAY() - Start@row) / Duration@row)))

and this formula in the parent rows:

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, SUM(CHILDREN()) / COUNT(CHILDREN([Task Name]@row)))

The percents are all positive, but the original problem remains, which is the parent % complete is not an accurate representation of the child rows, still working out the average but not referencing duration. I think the duration part of the calc needs to be in the parent row, not the child row.

David Joyeuse

@LisaB:-)

Ah, think I got why it is not accurate enough to you.

That's because in your parent row you're using MIN and MAX Date from children as starting and end dates.

I believe you can run with the same formula in the parent row than in children. But it's not exactly what you asked for originally :)

LisaB:-)

Hi, this is the result using this formula in both child and parent rows, unfortunately it miscalculates both.

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, IF(Status@row = "Not Started", 0, VALUE((TODAY() - Start@row) / Duration@row)))

My original post is an exact statement of what I'm asking for. I need the parent row to show the total of the % complete of the child rows, taking into consideration Status and Duration. Thanks in anticipation.

L_123

It sounds to me from the original question you need a weighted average

=avgw(children(duration@row),children()

You said you take into consideration the status when calculating the percent, so by utilizing the associated % in the row, we don't need to pull the status of said row into the parent calculation. We can take this a step further by doing what I call a parent check so you can drag the formula through the entire column

=IF(COUNT(CHILDREN()) = 0, IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, IF(Status@row = "Not Started", 0)), AVGW(CHILDREN(Duration@row), CHILDREN()))

LisaB:-)

Hi, thanks for replying. I'm afraid that won't work:

We update the Status column with a drop-down, reflecting the status of the task. Drop-downs are: Not Started/ In Progress/ On Hold/ Not Required/ Complete. The child row % has a formula which returns 0% for "Not Started" and 100% for "Complete", "On Hold" or "Not Required". If the row is "In Progress", the % column currently shows the % of progress, based upon duration (see Task 2a). This is 25%, because we're about three days into a 10-day task. I don't think the child row formula needs to change.

The parent row formula is currently =IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, AVG(CHILDREN())), which returns an average of the %s of the child rows. This is not accurate because in the case of the parent row of Task 1 below, an 11-day task cannot be 50% complete because only 1 day has elapsed.

I think 'duration' needs to be somehow incorporated into the calculation for the parent row, but I don't know how. Would very much appreciate any help.

L_123

In that case you can still use the formula, just without the parent check, and just post the formula to the parent cells.

=IF(OR(Status@row = "Complete", Status@row = "Not Required"), 1, avgw(children(),children(duration@row)

this will allow the duration of the task to act as the "weight" of the task.

https://help.smartsheet.com/function/avgw

LisaB:-)

@L@123 That's it! It now works perfectly, thank you so much!

L_123

NP. Glad we could get it sorted