Average Health Bubble from Multiple Project Health Bubbles

Lauren Smith

Hi All!

I have a portfolio dashboard for my PMO team and all of their projects. I use a metrics sheet to drive calculations for all projects. I am stuck on one thing and I need some help! I have formulas to calculate the main health bubbles for each from R, Y, G these come into a count:

What is the best way to average these up and get a portfolio health bubble?

Thanks!!

Genevieve P.

Hi Lauren,

Great job on the chart!

I'm not quite sure I understand what you're looking to do, though. Are you wanting an overall Health, displaying the colour with the Max number? If so, you could use an INDEX(MATCH formula to find the MAX in the number column and return the text from the Health column:

=INDEX(Health2:Health4, MATCH(MAX([Number Column]2:[Number Column]4), [Number Column]2:[Number Column]4))

I am presuming that your Health column is called Health, and that your top row is row 1, so the chart begins on row 2. I have input the name "Number Column" as a column title for the numbers as well.

Since you have created such a great chart, you may want to look into creating Chart Widgets on a dashboard, instead. The Pie Widget would be great for this, as it will show you the percentages of each status and you can colour those pie slices the same colour as the health. Our Help Center has more information on these types of widgets (see here).

Let me know if this helps, or if I've misunderstood!

Cheers,

Genevieve