Percentage Complete Parent/Child

jyoung_autosoftdms

I have several child rows that are marked either Assigned or Completed. I need the parent row to tell me what the percentage complete is.

So somehow counting the total number of total child rows and then counting the number of Assigned rows and the number of Completed rows then given me a percentage of Assigned rows.

How do I do that?

Mike TV

@jyoung_autosoftdms

For Augusta Hohbach column:

=COUNTIF(CHILDREN([Augusta Hohbach]@row), ="Assigned") / COUNT(CHILDREN([Augusta Hohbach]@row))

For African Grant column:

=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))

For Taylor Rivera column:

=COUNTIF(CHILDREN([Taylor Rivera]@row), ="Assigned") / COUNT(CHILDREN([Taylor Rivera]@row))

Make sure these three columns are set as percentage so your formula will display a percentage. It wont affect the drop down selections any, just the number value of your formulas.

Mike TV

@jyoung_autosoftdms

Here's an example:

Status formula:

=COUNTIF(CHILDREN(Status@row), ="Assigned") / COUNT(CHILDREN(Status@row))

jyoung_autosoftdms

@Matt C. That is exactly what I want to do but when I put that formula in the parent row I get #UNPARSEABLE

jyoung_autosoftdms

Is that because Assigned and Completed are dropdowns?

jyoung_autosoftdms

Mike TV

@jyoung_autosoftdms

Mine was also a dropdown single-select column. It could be because the column you have your Child rows in is called something other than Status. What's the name of the column with your Assigned and Completed statuses in or can you provide a screenshot?

jyoung_autosoftdms

Mike TV

@jyoung_autosoftdms

For Augusta Hohbach column:

=COUNTIF(CHILDREN([Augusta Hohbach]@row), ="Assigned") / COUNT(CHILDREN([Augusta Hohbach]@row))

For African Grant column:

=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))

For Taylor Rivera column:

=COUNTIF(CHILDREN([Taylor Rivera]@row), ="Assigned") / COUNT(CHILDREN([Taylor Rivera]@row))

Make sure these three columns are set as percentage so your formula will display a percentage. It wont affect the drop down selections any, just the number value of your formulas.

jyoung_autosoftdms

Yes this was it! Thank you so much for the quick and and the quick help. Now I can keep working!

jyoung_autosoftdms

@Matt C. One more question on this... If I want to average the 3 percentages to an overall average in a column to the right? How does that work?

Mike TV

@jyoung_autosoftdms

=AVG([Augusta Hohbach]4, [African Grant]4, [Taylor Rivera]4)

The above formula assumes these were on Row 4. Replace the number 4 with whichever row they're really on. Format the new column as percentage will be needed, I think.

jyoung_autosoftdms

Ok got it. I have got to get my Excel brain to think another way. Thank you again!

jyoung_autosoftdms

@Matt C. Hi...found another thing. How do I add an error option for the average formula here? some cells will need to be left blank because they don't apply to a specific column, so I need to make it so the n/a is ignored. Possible?

=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))

Mike TV

@jyoung_autosoftdms

=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNTIF(CHILDREN([African Grant]@row), <>"n/a")

melissamedina1

Hi Team,

I've tried everything on here. Still can't get it to work.

I have % Complete using (0%, 25%, 50%, 75%, 100%) however the parent doesn't calculate the % of the rows below it. How can this be calculated for the parent?

Genevieve P.

Hi @melissamedina1

Are you looking for the Average?

Try:

=AVG(CHILDREN())

Cheers,

Genevieve