May 12 2021 04:28 AM
Hello. Would appreciate help with the following matter.
I am trying to establish a ranked table based on a certain condition. My table relates to a series of cover designs which have been rated to produce individual scores out of 5. However, I only want to rank those covers that have been rated on 5 or more occasions (as indicated in column 2) such that Cover3 would not be ranked at all and that Cover13 would be ranked #1.
Have tried various approaches with the RANK formula but to no avail. Would therefore be grateful for any suggestions that will help crack the problem.
Ratings | Score | Rank | |
Cover1 | 3 | 3.33 | 14 |
Cover2 | 12 | 3.50 | 11 |
Cover3 | 1 | 5.00 | 1 |
Cover4 | 2 | 4.50 | 4 |
Cover5 | 9 | 3.78 | 8 |
Cover6 | 4 | 2.75 | 17 |
Cover7 | 9 | 4.44 | 5 |
Cover8 | 15 | 3.73 | 9 |
Cover9 | 2 | 3.50 | 12 |
Cover10 | 9 | 3.67 | 10 |
Cover11 | 1 | 3.00 | 15 |
Cover12 | 5 | 3.50 | 13 |
Cover13 | 9 | 5.00 | 2 |
Cover14 | 14 | 4.00 | 7 |
Cover15 | 6 | 3.00 | 16 |
Cover16 | 7 | 1.00 | 20 |
Cover17 | 6 | 2.50 | 19 |
Cover18 | 3 | 4.33 | 6 |
Cover19 | 8 | 2.57 | 18 |
Cover20 | 3 | 5.00 | 3 |
Thanks in advance.
May 12 2021 07:36 AM
May 12 2021 07:55 AM
SolutionIn D2:
=IF(B2>=5, COUNTIFS($B$2:$B$21, ">=5", $C$2:$C$21, ">"&C2) + COUNTIFS($B$2:$B2, ">=5", $C$2:$C2, C2), "")
Fill down.