RANK

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hello.

Is there a way to manipulate the RANK function so that it
will show the next counting number in the rank series
after a duplicate value?

For example, if A1:A5 contains 38,40,40,42,43,
respectively, RANK() returns 1,2,2,4,5. I want the values
to show 1,2,2,3,4. Any ideas?

Mark :)
 
One way


=RANK(A1,$A$1:$A$5,1)-(COUNTIF($A$1:$A$5,"<"&A1)-SUM((1/COUNTIF($A$1:$A$5,$A
$1:$A$5))*($A$1:$A$5<A1)))

entered with ctrl + shift & enter and copied down
list does not have to be sorted
 
Mark,

Array enter (using Ctrl-Shift-Enter)

=SUM(IF($A$1:$A$5<A1,1/COUNTIF($A$1:$A$5,$A$1:$A$5),0))+1

and copy down for 4 more cells.

HTH,
Bernie
MS Excel MVP
 
Thank you Bernie.

I tried your solution, but after entering the formula as
an array, I was unable to copy down the remaining 4 cells
as you suggested. What am I doing wrong?

If I could also slightly restate my initial post:

I wish to rank five values in reverse order. So if my
list is 42,44,44,45,47, I wish for the ranking to reflect
1,2,3,3,4 - not 1,2,3,3,5.

Thank you again.

Mark


-----------------------
 
Mark,

Array enter the formula into a single cell, then copy the cell and select
the next four cells and hit paste. Array formulas copy just the same as
others, once they are actually entered into a cell. You'll also need to
change the < sign to a > sign, since I thought you wanted low value = rank
of 1.

HTH,
Bernie
MS Excel MVP
 
Back
Top