Average on RGBY Symbols

Jona_g28

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")))

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=e7c5a484b2744c8a822274da75ead59d

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=e7c5a484b2744c8a822274da75ead59d

Jona_g28

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

jmyzk_cloudsmart_jp

Happy to help!😁 @Jona_g28