0

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.

Improve this question

1 Answer 1

Reset to default
0

Is this what you are looking for?

enter image description here

You might need to create a column which numerically ranks each person

Improve this answer
3
  • Yeah I guess I am talking about averages aren't I? The person with the lowest average should be ranked higher. For example, in the last 10 columns, someone is ranked 25, 22, 13, 8, 9. So they have made 5 appearances, for a total rank average of 15.4 (77/5). Then I guess if there was a separate table of averages, you could sort by lowest to highest, and then highlight that person's name in the colour scale colour. How can this be done in an automated manner?
    – Jayden Lawson
    Jan 21, 2015 at 9:52
  • 1
    Can you amend your question to include a sample of data and I'll take a look?
    – CallumDA
    Jan 21, 2015 at 10:47
  • Any ideas @CallumDS33 ?
    – Jayden Lawson
    Jan 25, 2015 at 23:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .