I want to average the health status of different teams

VerHeyD717

Hello,

I am hoping this is a simple ask.. I have a basic sheet with 10 different team types in one column (Ex: IT Systems, Licensure, Staffing) and health (red, yellow, green) of the row's task in another column. I am trying to find a formula to tell me the average health of all tasks within a team.

Please help.

Jon Mark H

Hey @VerHeyD717

This shouldn't be a problem to do, but you'll need a few helper columns -

Question - Do you want the "Average" health to be displayed as a RYG ball, like your "Health" column, or do you want to display it as a number? Either way you'll need to decide on a value for Red, Yellow, and Green.

In my example (See screenshot below), I used 1,2 & 3 respectively - the "Helper Column" uses this formula:

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

to pull that information from the "Health" Column.

"Avg Health All Tasks / Team" Column then uses this formula:

=AVERAGEIF(Sprint:Sprint, Sprint@row, [Helper Column]:[Helper Column])

To get the average per the team on each row.

Finally, "Avg Health Icon Per Team" column puts it together in a symbol [you have to choose a tipping point for when it's green vs. yellow vs. red - as shown below with items above 2.6 avg displaying as Green, and above 1.6 displaying as yellow:

=IF([Avg Health All Tasks / Team]@row = "", "", IF([Avg Health All Tasks / Team]@row > 2.6, "Green", IF([Avg Health All Tasks / Team]@row > 1.6, "Yellow", "Red")))

You could then hide the (2) helper columns on the right (after making the formula a column formula)

You could also make the Avg Health Icon only display on the top row, per team, so it doesn't just repeat again and again.

Let me know if that addressed your issue, or if you are in need of further assistance!

-Jon

Jon Mark H

Hey @VerHeyD717

This shouldn't be a problem to do, but you'll need a few helper columns -

Question - Do you want the "Average" health to be displayed as a RYG ball, like your "Health" column, or do you want to display it as a number? Either way you'll need to decide on a value for Red, Yellow, and Green.

In my example (See screenshot below), I used 1,2 & 3 respectively - the "Helper Column" uses this formula:

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

to pull that information from the "Health" Column.

"Avg Health All Tasks / Team" Column then uses this formula:

=AVERAGEIF(Sprint:Sprint, Sprint@row, [Helper Column]:[Helper Column])

To get the average per the team on each row.

Finally, "Avg Health Icon Per Team" column puts it together in a symbol [you have to choose a tipping point for when it's green vs. yellow vs. red - as shown below with items above 2.6 avg displaying as Green, and above 1.6 displaying as yellow:

=IF([Avg Health All Tasks / Team]@row = "", "", IF([Avg Health All Tasks / Team]@row > 2.6, "Green", IF([Avg Health All Tasks / Team]@row > 1.6, "Yellow", "Red")))

You could then hide the (2) helper columns on the right (after making the formula a column formula)

You could also make the Avg Health Icon only display on the top row, per team, so it doesn't just repeat again and again.

Let me know if that addressed your issue, or if you are in need of further assistance!

-Jon

VerHeyD717

Thank you Jon! This does accomplish what I was looking for. Would it make sense to gather this data in a metric sheet vs the active project sheet?

Jon Mark H

@VerHeyD717

Glad that helped!

I would say yes, it would be beneficial to gather the data in a metric sheet, as long as you're not adding & taking teams away frequently (which you probably aren't), so yes, that would be solid, and would also make it simple to show in a report.