Formula Help

Lauren Smith

Hi all -

Our team is trying to change our color bubble formula but we are struggling and need some help from those of you that are really good with formulas.

We use red, yellow, green, blue bubbles. The Column is called health.

First formula question: How do we get the main parent row to average the color bubbles and give us the health of the project. I tried =AVERAGEIF(CHILDREN()) but it just gave me a blank cell.

Second formula question:

We are trying to use this logic:

BLUE

 100% in ‘% Complete’ column

GREEN

>0% in ‘% Complete’ column + within the ‘BASELINE start date’ and ‘BASELINE end date’

YELLOW

 0% in ‘% Complete’ column + past the ‘BASELINE start date’

RED

<100% in ‘% Complete’ Column + past the ‘BASELINE end date’

<100% in ‘% Complete’ Column + past the ‘end date’

We use the column names:

Start Date

End Date

Baseline Start

Baseline Finish

Thank you in advanced!!

Jeff Reisman

Try this:

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, OR(TODAY() > [Baseline Finish]@row, TODAY() > [End Date]@row)), "Red", IF(AND([% Complete]@row = 0, TODAY() > [Baseline Start]@row), "Yellow", IF(AND([% Complete]@row > 0, [% Complete]@row < 1, TODAY() >= [Baseline Start]@row, TODAY() <= [Baseline Finish]@row), "Green", ""))))

As far as your average for product status goes, you'll want to add a helper column to give your status balls numeric values. Call it "HealthScore":

=IF(Health@row = "Blue", 4, IF(Health@row = "Green", 3, IF(Health@row = "Yellow", 2, IF(Health@row = "Red", 1))))

Then, on your Parent row, in the HealthScore column, put:

=AVG(CHILDREN())

This will give you a numerical average of the health scores.

Then, on the Parent row in the Health column, put:

=IF(HealthScore@row = 4, "Blue", IF(HealthScore@row >= 3, "Green", IF(HealthScore@row >= 2, "Yellow", "Red")))

"IF" formulas work from left to right, and stop processing once they come to a true condition. So if all the child rows are complete, your avg health score will be 4, and the IF will put a blue ball in. If it's not 4, the formula goes to the negative condition, which is another IF to consider, and so on.

Jeff Reisman

@Lauren Smith

Since you have multiple criteria columns for your status ball, and you want an average, I would say use a helper column that you can hide which will do all the calculations. It's going to be a long formula with nested IFs.

Before I start, some questions on your criteria:

Green: >0% in ‘% Complete’ column + within the ‘BASELINE start date’ and ‘BASELINE end date’

What date value should be between the [Baseline Start] column date and the [Baseline Finish] column date?

Yellow: 0% in ‘% Complete’ column + past the ‘BASELINE start date’

Same question: What date value should be past the [Baseline Start] date value?

Red: <100% in ‘% Complete’ Column + past the ‘BASELINE end date’

<100% in ‘% Complete’ Column + past the ‘end date’

What date for this one? Also, does that date need to be both after the [Baseline Finish] AND after the [End Date], or should it be Red if either condition or both conditions are met?

Lauren Smith

HI!! Thank you for trying to help me! my answers are in ALL CAPS for you:

Green: >0% in ‘% Complete’ column + within the ‘BASELINE start date’ and ‘BASELINE end date’

What date value should be between the [Baseline Start] column date and the [Baseline Finish] column date? I HONESTLY DO NOT KNOW, I AM TRYING TO SHOW THAT IF IT IS WITHIN THE ORIGINAL BASELINE START AND FINISH TIMEFRAME IT SHOULD BE GREEN MEANING WE ARE ON TRACK BECAUSE WE HAVE WORK GOING ON DURING THE TIME WE ORIGINALLY SAID WE WERE SUPPOSED TO

Yellow: 0% in ‘% Complete’ column + past the ‘BASELINE start date’

Same question: What date value should be past the [Baseline Start] date value? SAME ANSWER ABOVE, I AM UNSURE BUT I AM TRYING TO SHOW THAT IF SHOULD BE YELLOW IF NO WORK HAS BEEN COMPLETED AND WE MISSED THE ORIGINAL START DATE

Red: <100% in ‘% Complete’ Column + past the ‘BASELINE end date’

<100% in ‘% Complete’ Column + past the ‘end date’

What date for this one? Also, does that date need to be both after the [Baseline Finish] AND after the [End Date], or should it be Red if either condition or both conditions are met? IT SHOULD BE RED IF ETIHER CONDITION IS MET BECAUSE WE ARE SHOWING THAT ITS RED IF WE HAVE MISSED THE CURRENT DATE WE SET AND/OR IT IS RED BECAUSE WE FINISHED THE TASK PAST OUR ORIGINAL END DATE MEANING WE PUSHED OUT THE END DATE

My concern is that this is too complication to make in a formula but I figured I would try and ask.

Jeff Reisman

Try this:

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, OR(TODAY() > [Baseline Finish]@row, TODAY() > [End Date]@row)), "Red", IF(AND([% Complete]@row = 0, TODAY() > [Baseline Start]@row), "Yellow", IF(AND([% Complete]@row > 0, [% Complete]@row < 1, TODAY() >= [Baseline Start]@row, TODAY() <= [Baseline Finish]@row), "Green", ""))))

As far as your average for product status goes, you'll want to add a helper column to give your status balls numeric values. Call it "HealthScore":

=IF(Health@row = "Blue", 4, IF(Health@row = "Green", 3, IF(Health@row = "Yellow", 2, IF(Health@row = "Red", 1))))

Then, on your Parent row, in the HealthScore column, put:

=AVG(CHILDREN())

This will give you a numerical average of the health scores.

Then, on the Parent row in the Health column, put:

=IF(HealthScore@row = 4, "Blue", IF(HealthScore@row >= 3, "Green", IF(HealthScore@row >= 2, "Yellow", "Red")))

"IF" formulas work from left to right, and stop processing once they come to a true condition. So if all the child rows are complete, your avg health score will be 4, and the IF will put a blue ball in. If it's not 4, the formula goes to the negative condition, which is another IF to consider, and so on.

Lauren Smith

It worked!! Thank you so much! You are a genius with the formulas!