# I want to average the health status of different teams

Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• Options

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?

• ✭✭✭✭✭
Options