I have a table of data where there are 50 rows and many columns. Each column's value is sorted by the most important result at the top. These are names of people in sports. I would like to see emerging trends of the best performing person in the last x number of columns. Is there any way of doing this?
For example, the person's name with the best results in the last 10 columns has a cell colour of red (hot). The second best is a lighter shade of red, which fades through a colour scale to green.
I can manually do a conditional format for each person's name, so that name is highlighted in their own selected colour, but that's very laborious and I have to guess who I think has been performing well.
Edit: thanks to CallumDS33. I suppose I am talking about averages. Is there a way to create a separate column which searches all the columns/rows for a name? If it doesn't exist in our new comparison column - it is added. Then when all the unique names are added once to the column, it finds their average ranking position from each column where they appeared.
For example - in 10 columns, Mary Smith appeared in row positions 25, 13, 19, 8, 5 (she didn't have to appear in every column). So her average position is 14 (total of 70 / 5). Then she can be sorted against everyone else's average position.
If this last part is too hard - it would be amazing just to have this column of average positions.
Edit 2: Sample data: https://docs.google.com/spreadsheets/d/1HTQ3hiTLPRTEWuwN4ebNXZy_Sn4nFLpet2MLR_qJrco/edit?usp=sharing
In the sample data, each column is a separate competition, and the winners of the competition are in row 1. Second place is row 2, and so on. To the right of the sample data I have manually found some good performing players, then found their average position, then sorted those players by lowest average. This is what I'm trying to do. It's not too hard to do manually for 5 columns, however - doing it for 10 or more becomes much harder. My data will have up to 60 columns, so I would love to be able to choose any 5-10 columns to analyse, and show the result of the best players in those columns.