Average of Color Symbols (RYG) on same row

SherryFox · 12/10/24

I need to be able to create an average (In the Overall Status field) of the 3 filed to the right. As you Can see, they are symbols with the standard stoplight (RYG) colors. I have done this (with the help of the community) for parent/child, but now these fields are on the same row.

Melissa Yamada · 12/10/24

Hello @SherryFox

Try this:

=IF(COUNTIF(Cost1:Performance1, "Red") = COUNT(Cost1:Performance1), "Red", IF(COUNTIF(Cost1:Performance1, "Green") = COUNT(Cost1:Performance1), "Green", "Yellow"))

Or this if you want to use column formula:

=IF(COUNTIF(Cost@row:Performance@row, "Red") = COUNT(Cost@row:Performance@row), "Red", IF(COUNTIF(Cost@row:Performance@row, "Green") = COUNT(Cost@row:Performance@row), "Green", "Yellow"))

Paul Newcome · 12/10/24

You would use the same method as parent/child, but instead of your range being CHILDREN([Column Name]@row), your range would be Cost@row:Performance@row.

SherryFox · 12/10/24

@Paul Newcome ,

I went back and pulled the answer from the Parent/Child, and applied the format to this one like you said, but it does not seem to be working. I am sure it is something simple that I messed up. It says Incorrect Argument Set for the error. Here is what I have:

=IF(COUNTIF(Cost1:Performance1, "Red") = COUNT(Cost1:Performance1, "Red", IF(Cost1:Performance1, "Green") = COUNT(Cost1:Performance1, "Green", "Yellow")))

Melissa Yamada · 12/10/24

Hello @SherryFox

Try this:

=IF(COUNTIF(Cost1:Performance1, "Red") = COUNT(Cost1:Performance1), "Red", IF(COUNTIF(Cost1:Performance1, "Green") = COUNT(Cost1:Performance1), "Green", "Yellow"))

Or this if you want to use column formula:

=IF(COUNTIF(Cost@row:Performance@row, "Red") = COUNT(Cost@row:Performance@row), "Red", IF(COUNTIF(Cost@row:Performance@row, "Green") = COUNT(Cost@row:Performance@row), "Green", "Yellow"))