Prev: Trust Center - Trusted Locations are greyed out
Next: Make a csv file open in excel instead of IE
From: Bernd P on 8 Apr 2010 02:38
On 7 Apr., 22:48, Smooth813 <Smooth...(a)discussions.microsoft.com>
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
From: Bernd P on 8 Apr 2010 12:11
Hello again,

Use
=RANK(A1,A$1:A$10,1)+(COUNTIF(A$1:A$10,A1)+1)/2-1
and copy down and across as far as necessary.

For the last two columns use
=RANK(I1,I$1:I$10,0)+(COUNTIF(I$1:I$10,I1)+1)/2-1

If you fancy array formulas you can select A11:A20 for example and
array-enter (with CTRL + SHIFT + ENTER, not only with ENTER):
=RANK(A1:A10,A1:A10,1)+(COUNTIF(A1:A10,A1:A10)+1)/2-1

Then you can copy A11:A20 across but for the last two columns you
should use the third RANK parameter 0 again.

Regards,
Bernd

From: Smooth813 on 10 Apr 2010 12:54
Bernd P:

Thanks for your help! I believe your formula works just fine. Working on
it with a friend, we came up with something much more complicated, so yours
will help a lot:

=IF(COUNTIF($B$3:$B$12,$B3)>1,RANK($B3,$B$3:$B$12,1)-0.5*(COUNTIF($B$3:$B$12,$B3)-1)+(COUNTIF($B$3:$B$12,$B3)-1),RANK($B3,$B$3:$B$12,1))

A tad complex. I haven't tried the array yet, but I might here in a bit.

Again, thanks for your help!

"Bernd P" wrote:

> Hello again,
>
> Use
> =RANK(A1,A$1:A$10,1)+(COUNTIF(A$1:A$10,A1)+1)/2-1
> and copy down and across as far as necessary.
>
> For the last two columns use
> =RANK(I1,I$1:I$10,0)+(COUNTIF(I$1:I$10,I1)+1)/2-1
>
> If you fancy array formulas you can select A11:A20 for example and
> array-enter (with CTRL + SHIFT + ENTER, not only with ENTER):
> =RANK(A1:A10,A1:A10,1)+(COUNTIF(A1:A10,A1:A10)+1)/2-1
>
> Then you can copy A11:A20 across but for the last two columns you
> should use the third RANK parameter 0 again.
>
> Regards,
> Bernd
>
> .
>
From: Bernd P on 10 Apr 2010 15:31
Hello,

Thanks for your feedback, you are welcome.

A more flexible formula which will also work for strings is shown at
the bottom of this page:
http://sulprobil.com/html/rank.html

A sample file you can load at the top of that page...

Regards,
Bernd
First  |  Prev  | 
Pages: 1 2 3
Prev: Trust Center - Trusted Locations are greyed out
Next: Make a csv file open in excel instead of IE