On 7 Apr., 22:48, Smooth813
wrote:
Hey, thanks for the help everyone, but my problem has become a little more
complicated than I thought. *Essentially, I am trying to create something
that will mimic the ranking system I have found on the web. Here is the data:

Data
14 * * *5 * * * 18 * * *3 * * * 0.4051 * * * * *21 * * *2 * * * 1 * * * 3 * * * 1.19
12 * * *4 * * * 23 * * *2 * * * 0.3182 * * * * *15 * * *2 * * * 1 * * * 4.655 * 1.345
6 * * * 2 * * * 13 * * *2 * * * 0.3158 * * * * *14 * * *3 * * * 0 * * * 0.409 * 0.5
16 * * *5 * * * 13 * * *2 * * * 0.2857 * * * * *15 * * *0 * * * 1 * * * 6.632 * 1.684
16 * * *3 * * * 12 * * *2 * * * 0.1954 * * * * *8 * * * 1 * * * 1 * * * 4.5 * * 1.083
11 * * *5 * * * 10 * * *3 * * * 0.2267 * * * * *13 * * *2 * * * 0 * * * 3.857 * 1.429
14 * * *4 * * * 13 * * *1 * * * 0.2813 * * * * *19 * * *0 * * * 1 * * * 5.786 * 1.371
16 * * *3 * * * 8 * * * 1 * * * 0.3276 * * * * *11 * * *0 * * * 0 * * * 4.05 * *1.8
16 * * *1 * * * 11 * * *2 * * * 0.3276 * * * * *3 * * * 0 * * * 0 * * * 8.438 * 1.969
14 * * *3 * * * 7 * * * 1 * * * 0.2769 * * * * *5 * * * 0 * * * 4 * * * 16.2 * *2

Rankings
5 * * * 9 * * * 9 * * * 9.5 * * 10 * * * * * * *10 * * *8 * * * 7 * * * 9 * * * 8
3 * * * 6.5 * * 10 * * *6 * * * 7 * * * * * * * 7.5 * * 8 * * * 7 * * * 5 * * * 7
1 * * * 2 * * * 7 * * * 6 * * * 6 * * * * * * * 6 * * * 10 * * *2.5 * * 10 * * *10
8.5 * * 9 * * * 7 * * * 6 * * * 5 * * * * * * * 7.5 * * 3 * * * 7 * * * 3 * * * 4
8.5 * * 4 * * * 5 * * * 6 * * * 1 * * * * * * * 3 * * * 6 * * * 7 * * * 6 * * * 9
2 * * * 9 * * * 3 * * * 9.5 * * 2 * * * * * * * 5 * * * 8 * * * 2.5 * * 8 * * * 5
5 * * * 6.5 * * 7 * * * 2 * * * 4 * * * * * * * 9 * * * 3 * * * 7 * * * 4 * * * 6
8.5 * * 4 * * * 2 * * * 2 * * * 8.5 * * * * * * 4 * * * 3 * * * 2.5 * * 7 * * * 3
8.5 * * 1 * * * 4 * * * 6 * * * 8.5 * * * * * * 1 * * * 3 * * * 2.5 * * 2 * * * 2
5 * * * 4 * * * 1 * * * 2 * * * 3 * * * * * * * 2 * * * 3 * * * 10 * * *1 * * * 1

I believe the original IF(COUNTIF...) function worked, but Excel seems to
use the lowest tied ranking, not the highest tied ranking, if that makes
sense. *Is there an easy way to account for this and to mimic this ranking
system?

Thanks for the help everyone.

Hello,

First column:
14 appears 3 times, consuming ranks 4, 5, and 6 which result in an
average rank (4+5+6)/3 = 5
16 appears 4 times, consuming ranks 7, 8, 9 and 10 which result in an
average rank (7+8+9+10)/4 = 8.5

I am running out of time now but maybe someone finds a nice solution
for this.

Regards,
Bernd