Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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, "")))
