#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Conditional Formatting Question

Options
edited 12/09/19

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?

• ✭✭✭✭✭✭
edited 05/08/16
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

Emily,

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

Craig

This discussion has been closed.