Conditional Formatting Question

Emily B.

We use a Project Timeline Template that tracks the progress of each individual project. There are a number of parent rows with child rows beneath. I've created a status column so that each task can reflect the current progress via a harvey ball. I would like the parent rows to reflect the overall current status based on the average of its children's status. Is this possible to do via conditional formatting?

 

J. Craig Williams

Emily,

 

No directly with Conditional Formatting.

Conditional Formatting is display, not calculations.

 

What you'll need to do is convert the Harvey Balls to numbers like this:

 

=COUNTIF(CHILDREN(), "Quarter")*25

 

That counts the number of children that have the quarter fill and multiplies by 25.

You will need to sum all the different possibiltiies and divide by the total

 

shorhand CC is COUNTIF(CHILDREN())

 

(CC-quarter*25 + CC-half*50 + etc...) / (CC-empty+CC-quarter+... etc...)

 

nasty but possible.

I would add another column and put this in the children

 

pseudo code:

 

if harvery ball quarter = 25

else if harvey ball half = 50

etc...

 

and then in the parent

=sum(children()) / count(children())

 

and the run your Conditional Formatting off that.

 

Craig

 

 

 

Emily B.

Thank you, Craig, for the very thorough explanation! Very much appreciated. 

I will give this a try tomorrow and see how it works...I may come back to you with more questions. Hope that's ok!

J. Craig Williams

Emily,

 

No problem. If I have spare minutes, I often check here.

 

Craig