Average + Harvey balls
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
Best Answer

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
Answers

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

H, I'm changing manually the data

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

Hi Genevieve,
It works, thank you so much!