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 worst-case 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 worst-case 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
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!