Average on RGBY Symbols
Hello,
I am in need to come up with a formula that takes into consideration 4 different columns (Schedule, Scope, Schedule and Resource) that have RGBY Symbols across. I looked around for any options through the community but I couldn't find something that works for me. I tried with 4 helper columns to get an average but it didn't work well or maybe I was putting the criteria wrong. So instead I tried another formula and still failed. I need to adjust this formula to determine what to show in case there are 3 greens and 1 yellow because as the formula is written, yellow is taking precedence, and in such case I need the majority to overrule the conditions. If anyone has any suggestions, please tell me how can I make this work? Rows 5 and 7 for example are what I wanted to change:
=IF(COUNTIF(Resources@row:Budget@row, "Red") > 0, "Red", IF(COUNTIF(Resources@row:Budget@row, "Yellow") > 0, "Yellow", IF(COUNTIF(Resources@row:Budget@row, "Green") > 0, "Green", "Blue")))
Best Answer

Hi @Jona_g28
I added RGBY and Majority helper columns to make the formula simple.
[Average] =IF(Majority@row = "G", "Green", IF(Majority@row = "B", "Blue", IF(COUNTIF(Resources@row:Budget@row, "Red") > 0, "Red", IF(COUNTIF(Resources@row:Budget@row, "Yellow") > 0, "Yellow", IF(COUNTIF(Resources@row:Budget@row, "Green") > 0, "Green", "Blue")))))
[G] =COUNTIF(Resources@row:Budget@row, "Green")
[B] =COUNTIF(Resources@row:Budget@row, "Blue")
[Y] =COUNTIF(Resources@row:Budget@row, "Yellow")
[R] =COUNTIF(Resources@row:Budget@row, "Red")
[Max] =MAX(G@row:R@row)
[Majority] =IF(R@row = Max@row, "R", IF(Y@row = Max@row, "Y", IF(B@row = Max@row, "B", IF(G@row = Max@row, "G"))))In the [Majority] formula, R or Red is placed first, assuming the worstcase status is the default when there is a tie. For example, Y if Y=2 and B=2; however, the order can be changed to Y, R, B, Y if you want to put Y when Y and R ties.
Answers

Hi @Jona_g28
I added RGBY and Majority helper columns to make the formula simple.
[Average] =IF(Majority@row = "G", "Green", IF(Majority@row = "B", "Blue", IF(COUNTIF(Resources@row:Budget@row, "Red") > 0, "Red", IF(COUNTIF(Resources@row:Budget@row, "Yellow") > 0, "Yellow", IF(COUNTIF(Resources@row:Budget@row, "Green") > 0, "Green", "Blue")))))
[G] =COUNTIF(Resources@row:Budget@row, "Green")
[B] =COUNTIF(Resources@row:Budget@row, "Blue")
[Y] =COUNTIF(Resources@row:Budget@row, "Yellow")
[R] =COUNTIF(Resources@row:Budget@row, "Red")
[Max] =MAX(G@row:R@row)
[Majority] =IF(R@row = Max@row, "R", IF(Y@row = Max@row, "Y", IF(B@row = Max@row, "B", IF(G@row = Max@row, "G"))))In the [Majority] formula, R or Red is placed first, assuming the worstcase status is the default when there is a tie. For example, Y if Y=2 and B=2; however, the order can be changed to Y, R, B, Y if you want to put Y when Y and R ties.

Thank you! A little bit more manual than what I was hoping but this really helps. Thank you!

Happy to help!😁 @Jona_g28
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!