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.

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.


"Smooth813" wrote:

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.

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

--
Biff
Microsoft Excel MVP


"Smooth813" wrote in message
...
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.

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.

"Mike H" wrote:

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.


"Smooth813" wrote:

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.

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

"Mike H" wrote:

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.


"Smooth813" wrote:

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

"Smooth813" wrote:

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.

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


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

"Smooth813" wrote:

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.

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.


"Smooth813" wrote:

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.

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" wrote:

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.

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.

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

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

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

.

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