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

• ✭✭✭✭✭✭
edited 09/03/24 Answer ✓

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.

• ✭✭✭✭✭✭
edited 09/03/24 Answer ✓

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!