Can you average red green yellow symbols

Patrick Galvin

I have rows that each have a RYG symbol and a collection of rows roll-up to a section summary.

Is there any way I can have teh section summary lok at the rows and work out an average thereby automating it

AJ_vf

Here's my suggestion:

 

Create a column that converts RYG to 1/2/3. IE, =IF([Cell] = Red, 1, IF([Cell] = Yellow, 2, 3)). Find a cell within this new column where you can stick the average of all the values in this new column. Then, in your section summary, you can use a formula to dictate which RYG ball based on the average. IE, =IF([Average] > 2, Green, IF([Average] > 1, Yellow, Red)).

Brucilla

This works great, however, if you have any empty cells in your RYG column then the first formula would assign a 3 to the corresponding cells in your new column.  Then if you average the entire column, your average will include the empty cell 3's into it's calculation.  

There may be a better way to do this, but I just added an additional condition at the end that sets an empty string in the cell if none of the symbols are present in the corresponding RYG cell.

IF([Cell] = "Green", 3, IF([Cell] = "Yellow"), 2, IF([Cell] = "Red"), 1, "")))