Formula for Status Balls

Marissa C

Hello!

I'm hoping someone will be able to help! I'm trying to determine the total percentage complete of children under a parent category. Criteria for percentage complete using the status balls would be:

Green = 1 (deliverable met)

Yellow = .5 (deliverable partially met)

Null/Blank field/no status ball selected = 0 (deliverable not met, should be included in percentage calculation)

Grey = N/A (item will not be delivered, not applicable, should not be calculated in percentage)

Then I'm hoping to have overall percentage complete for delivery column based on percentages of parent categories calculated above.

This is what has been attempted so far but didn't work:

=AVERAGEIF(CHILDREN([Data Cut 2 Status]@row)), IF([Data Cut 2 Status]@row = "Green", 1, IF([Data Cut 2 Status]@row = "Yellow", .5, IF([Data Cut 2 Status]@row = "Blank", 0)))

=AVERAGEIF(CHILDREN([Data Cut 3 Status]@row)), IF([Data Cut 3 Status]@row = "Green", 1, IF([Data Cut 3 Status]@row = "Yellow", .5, IF([Data Cut 3 Status]@row = "Red", 0, IF([Data Cut 3 Status]@row = "Gray", 0)))))

I appreciate

any help!!

Thank you!

Genevieve P.

Hi @Marissa C

Try something like this:

=(COUNTIF(CHILDREN(), "Green") + (COUNTIF(CHILDREN(), "Yellow") * 0.5)) / COUNT(CHILDREN())

This counts how many of the rows below are Green (valuing each green ball as 1). It then counts how many yellow balls there are, multiplying that by 0.5.

It then divides that number by the total number of status balls, regardless of colour. This COUNT(CHILDREN()) automatically excludes blank cells from the count, but includes grey.

For your overall percentage, once you have the percentages of each parent row, you can simply AVG the Children of that top-level parent:

=AVG(CHILDREN())

Cheers!

Genevieve

LMF

Thank you so much for asking this question Marissa!!

And thank you for the input Genevieve!!