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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
It works, thank you so much!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives