Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Can you average red green yellow symbols

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



  • 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)).

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

This discussion has been closed.