Ranking in a Different Way

[Smooth813]

Basically, I am trying to rank a range of numbers, but when there are two
numbers of the same value, I don't want the number that is returned to be
their exact rank. Instead, I want ONLY number that have equal value to be .5
less than the value given. So, if I have two number that are equal, instead
of being ranked "9" or something, I want them to be ranked "8.5". I still
want numbers that are by themselves to be ranked wholly.

I have been using the Rank function, but I don't know how to incorporate it
so that it changes values if they are equal. My first thought was an array
of IF functions, but I was hoping there was an easier way. Does anyone have
any suggestions?

Thanks.

 

[Mike H]

What do you want if there are 3 numbers the same?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[T. Valko]

Can you post some sample data and let us know what results you want?

 

[Smooth813]

I would like all of them to be .5 less than their rank. So, if three tie and
have a rank of "9", then I want all three to be "8.5" and the next value to
be the next rank, if that makes sense.

 

[Smooth813]

I would want all three values (or however many tied) to be .5 less. So all
three would be .5 less.

 

[מיכ×ל (מיקי) ×בידן]

Mike dragged out the question of my mouth...
Anyhow - why not rank the list so that 2 equal values will be ranked as
shown hereunder ?
Value Rank
1 8
2 7
3 6
4 4
4 5
5 2
5 3
6 1
Micky

 

[מיכ×ל (מיקי) ×בידן]

For my previous shown 8 values - will this be acceptable ?
------
8
7
6
4
3.5
2
1.5
1

 

[Mike H]

Hi,

Maybe this

=(SUMPRODUCT(--(A1<$A$1:$A$20),1/COUNTIF($A$1:$A$20,$A$1:$A$20&""))+1)-IF(COUNTIF($A$1:$A$20,A1)>1,0.5,0)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[EricG]

Assuming you have 14 numbers in column A, starting in the first cell, enter
this formula in column B and fill down. It seems to do what you want. You
may have to adjust depending on whether you are ranking in ascending or
descending order.

=IF(COUNTIF($A$1:$A$14,$A1)>1,RANK($A1,$A$1:$A$14,1)-0.5,RANK($A1,$A$1:$A$14,1))

HTH,

Eric

 

[Smooth813]

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.

 

[Bernd P]

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

 

[Smooth813]

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]

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