SOLVED

# Excel

Occasional Contributor

# Excel

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

4 Replies

# Re: Excel

In D2:

=IF(B2>=5,COUNTIFS(\$B\$2:\$B\$21,">=5",\$C\$2:\$C\$21,">"&C2)+1,"")

Fill down.

# Re: Excel

Thanks for your reply Hans; it works a treat and I would certainly not have arrived at this conclusion under my own steam. However, one additional matter; I would like the ranking results to be presented as a set of unique numbers. Is there a solution?

best response confirmed by Iriwel (Occasional Contributor)
Solution

# Re: Excel

In 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.

# Re: Excel

Hans, again my very grateful thanks for your help.

Regards

James