Average + Harvey balls

Renquisalas

I'am new in smartsheet and I want to find the average score of my team in different areas using the harvey balls, and have the results measured via the spheres,

I was trying to use the average function + harvey line but it gives me as a result error, thank you for your help

Genevieve P.

Hi @Renquisalas

The harvey balls represent text, not numbers, which means that they cannot be directly averaged using "AVG". We will need to convert each of the text representations into numbers in order to determine an average.

For example, if I have three columns called One, Two, and Three, it would look like this:

=((COUNTIF(One@row:Three@row, "Quarter") * 0.25) + (COUNTIF(One@row:Three@row, "Half") * 0.5) + (COUNTIF(One@row:Three@row, "Three Quarter") * 0.75) + (COUNTIF(One@row:Three@row, "Full") * 1)) / COUNT(One@row:Three@row)

This will give you an average in decimals, which you could format to look like a percentage, if that helps.

If you want this to look like a harvey ball again, we'll need to convert that average back into text values. I would personally keep these as two separate formulas for simplicity - one locked column with the average in numbers, a second column showing that in symbols.

=IF(AVG@row = 0, "Empty", IF(AVG@row = 1, "Full", IF(AVG@row <= 0.35, "Quarter", IF(AVG@row <= 0.65, "Half", IF(AVG@row <= 1, "Three Quarter")))))

You'll need to decide for yourself at what point do you want to show the half, quarter, etc. Let me know if this makes sense and works for you!

Cheers,

Genevieve

jpaul

Are you manually changing the Harvey balls or are they built by other data? The solution depends on how your data is built.

Renquisalas

H, I'm changing manually the data

Genevieve P.

Hi @Renquisalas

The harvey balls represent text, not numbers, which means that they cannot be directly averaged using "AVG". We will need to convert each of the text representations into numbers in order to determine an average.

For example, if I have three columns called One, Two, and Three, it would look like this:

=((COUNTIF(One@row:Three@row, "Quarter") * 0.25) + (COUNTIF(One@row:Three@row, "Half") * 0.5) + (COUNTIF(One@row:Three@row, "Three Quarter") * 0.75) + (COUNTIF(One@row:Three@row, "Full") * 1)) / COUNT(One@row:Three@row)

This will give you an average in decimals, which you could format to look like a percentage, if that helps.

If you want this to look like a harvey ball again, we'll need to convert that average back into text values. I would personally keep these as two separate formulas for simplicity - one locked column with the average in numbers, a second column showing that in symbols.

=IF(AVG@row = 0, "Empty", IF(AVG@row = 1, "Full", IF(AVG@row <= 0.35, "Quarter", IF(AVG@row <= 0.65, "Half", IF(AVG@row <= 1, "Three Quarter")))))

You'll need to decide for yourself at what point do you want to show the half, quarter, etc. Let me know if this makes sense and works for you!

Cheers,

Genevieve

Renquisalas

Hi Genevieve,

It works, thank you so much!