sortingdategoogle-sheetstransposerank
Find and rank the 5 days with the highest score (some days have the same score)
The data has the format below. (1st row is the date, 2nd row the score.) Some days have the same score.
| Date | 6-Oct | 7-Oct | 8-Oct | 9-Oct | 10-Oct | 11-Oct | 12-Oct | 13-Oct | 14-Oct |
|---|---|---|---|---|---|---|---|---|---|
| Score | 23 | 34 | 21 | 12 | 19 | 25 | 35 | 24 | 21 |
I'm trying to create a formula that will give me the dates of the top 5 scoring days.
| Rank | Date | Score |
|---|---|---|
| 1 | ? | 35 |
| 2 | ? | 34 |
| 3 | ? | 34 |
| 4 | ? | 25 |
| 5 | ? | 23 |
I created a formula using index, match and large however it does not work when 2 days have the same score.
Any help would be much appreciated!
Solution
try in google sheets (as tagged):
=SORTN(TRANSPOSE(B1:J2); 5;; 2; )
