Hello there! Very basic user here, so appreciate your advice.
I have a list of 10 people which are ranked and I want to return the 3 top performers and the two bottom performers for that month.
The complicated part is that some of the rankings are equal. When there are equal rank values, the people are already sorted by ability so if two people get the same rank, the higher value will be awarded to the first person on the list (I know it sounds unfair
).
So if this was the table:
Name: Rod, Jane, Freddy, Geoffrey, Bungle
Test 1 Rank: 1, 5, 3, 3, 3
Test 2 Rank: 2, 3, 5, 1, 3
I would want the results for Test 1 to show Rod, Freddy and Geoffrey as the top 3 performers. Even though Bungle got an equal score, he is lower down the list, so in fact, he is the second lowest performer, with Jane as bottom.
For Test 2, Geoffrey, Rod and Jane are top 3. Bungle comes 4th and Freddy, 5th.
Attached is a closer table to what I am hoping to create, with 10 people in total, with the top 3 and bottom two values returned for each month. I am really sorry but I can't seem to install XL2BB so i only have a picture.
Is this possible with formulas (I know LARGE and SMALL but my silly, complicated ranking system doesn't allow for equals and I don't know how to return the value at the top in Row 2)?
Thank you for any help or guidance!
Simon
I have a list of 10 people which are ranked and I want to return the 3 top performers and the two bottom performers for that month.
The complicated part is that some of the rankings are equal. When there are equal rank values, the people are already sorted by ability so if two people get the same rank, the higher value will be awarded to the first person on the list (I know it sounds unfair
).So if this was the table:
Name: Rod, Jane, Freddy, Geoffrey, Bungle
Test 1 Rank: 1, 5, 3, 3, 3
Test 2 Rank: 2, 3, 5, 1, 3
I would want the results for Test 1 to show Rod, Freddy and Geoffrey as the top 3 performers. Even though Bungle got an equal score, he is lower down the list, so in fact, he is the second lowest performer, with Jane as bottom.
For Test 2, Geoffrey, Rod and Jane are top 3. Bungle comes 4th and Freddy, 5th.
Attached is a closer table to what I am hoping to create, with 10 people in total, with the top 3 and bottom two values returned for each month. I am really sorry but I can't seem to install XL2BB so i only have a picture.
Is this possible with formulas (I know LARGE and SMALL but my silly, complicated ranking system doesn't allow for equals and I don't know how to return the value at the top in Row 2)?
Thank you for any help or guidance!
Simon
