Formula for Status Balls
Hello!
I'm hoping someone will be able to help! I'm trying to determine the total percentage complete of children under a parent category. Criteria for percentage complete using the status balls would be:
Green = 1 (deliverable met)
Yellow = .5 (deliverable partially met)
Null/Blank field/no status ball selected = 0 (deliverable not met, should be included in percentage calculation)
Grey = N/A (item will not be delivered, not applicable, should not be calculated in percentage)
Then I'm hoping to have overall percentage complete for delivery column based on percentages of parent categories calculated above.
This is what has been attempted so far but didn't work:
=AVERAGEIF(CHILDREN([Data Cut 2 Status]@row)), IF([Data Cut 2 Status]@row = "Green", 1, IF([Data Cut 2 Status]@row = "Yellow", .5, IF([Data Cut 2 Status]@row = "Blank", 0)))
=AVERAGEIF(CHILDREN([Data Cut 3 Status]@row)), IF([Data Cut 3 Status]@row = "Green", 1, IF([Data Cut 3 Status]@row = "Yellow", .5, IF([Data Cut 3 Status]@row = "Red", 0, IF([Data Cut 3 Status]@row = "Gray", 0)))))
I appreciate
any help!!
Thank you!
Answers
-
Hi @Marissa C
Try something like this:
=(COUNTIF(CHILDREN(), "Green") + (COUNTIF(CHILDREN(), "Yellow") * 0.5)) / COUNT(CHILDREN())
This counts how many of the rows below are Green (valuing each green ball as 1). It then counts how many yellow balls there are, multiplying that by 0.5.
It then divides that number by the total number of status balls, regardless of colour. This COUNT(CHILDREN()) automatically excludes blank cells from the count, but includes grey.
For your overall percentage, once you have the percentages of each parent row, you can simply AVG the Children of that top-level parent:
=AVG(CHILDREN())
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much for asking this question Marissa!!
And thank you for the input Genevieve!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!