Prev: Trust Center - Trusted Locations are greyed out
Next: Make a csv file open in excel instead of IE
From: מיכאל (מיקי) אבידן on 6 Apr 2010 11:59
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.
From: מיכאל (מיקי) אבידן on 6 Apr 2010 12:13
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.
From: Mike H on 6 Apr 2010 12:15
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.
From: EricG on 6 Apr 2010 12:54
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.
From: Smooth813 on 7 Apr 2010 17:48
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.

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