Unique Rankings

C

cdavidson

My problem...

I have the Rank( ) function But
following (ascending order) I
numbers: sais: need:

4 5 5
4 5 6
1 1 1
1 1 2
1 1 3
3 4 4

Any solution?
 
L

Leo Heuser

cdavidson said:
My problem...

I have the Rank( ) function But
following (ascending order) I
numbers: sais: need:

4 5 5
4 5 6
1 1 1
1 1 2
1 1 3
3 4 4

Any solution?
Click to expand...


Davidson

One way assuming numbers in B2:B7:

In an arbitrary cell:

=RANK(B2,$B$2:$B$7,2)+COUNTIF($B$2:B2,B2)-1

Copy down.
 
T

Tom Ogilvy

=Rank(A1,$A$1:$A$6,1)+Countif($A$1:A1,A1)-1
entered in B1, then drag filled down gives me what you say you need.
 
C

cdavidson

My apologies to you both, I now realize I should have elaborated to start
with my actual situation, as it is more complex to solve than I thought. I
actually have two columns of data, and need to rank them as per the example
below:


Month Product Count Rank Required
------- ---------------- -----------------
12 20 2
12 12 3
12 40 1
11 15 2
11 5 4
11 10 3
11 20 1
10 30 1
10 12 2


Essentially, what I need to do is rank by monthly groupings, in descending
order. I want all the 'Month = 12' ranked first in descending order, then
start over to rank all the 'Month = 11' ranked in descending order, etc.

Many thanks for you assistance!

Craig
 
D

Domenic

Try...

C1, copied down:

=(SUMPRODUCT(--($A$1:$A$9=A1),--(B1<$B$1:$B$9))+1)+(SUMPRODUCT(--($A$1:A1
=A1),--($B$1:B1=B1))-1)

Hope this helps!
 
M

Morrigan

The second part of Domenic's formula can be neglected as it will onl
return zero.

ie.
=(SUMPRODUCT(--($A$1:$A$9=A1),--(B1<$B$1:$B$9))+1)
 
C

cdavidson

Please accept my second apology! I have some duplicate values in my data,
yet I need unique ranking numbers. Please see my revised example below:


Month Product Count Rank Required
------- ---------------- -----------------
12 20 2
12 20 3
12 40 1
11 15 2
11 15 3
11 10 4
11 20 1
10 30 1
10 30 2
10 25 3
10 25 4
 
D

Domenic

If you try the formula I offered in my previous post, you'll find that
it will return the results you're looking for. Here it is again for
easy reference...

=(SUMPRODUCT(--($A$2:$A$12=A2),--(B2<$B$2:$B$12))+1)+(SUMPRODUCT(--($A$2:
A2=A2),--($B$2:B2=B2))-1)

Adjust the ranges accordingly.

Hope this helps!
 
D

Domenic

The second part of Domenic's formula can be neglected as it will only
return zero.

ie.
=(SUMPRODUCT(--($A$1:$A$9=A1),--(B1<$B$1:$B$9))+1)
Click to expand...

Actually, the second part of the formula is needed since the OP is
looking for unique ranking. See the OP's last post.
 
M

Morrigan

You are right. That takes care of the duplicates. Sorry for my minima
excel knowledge. ;)
 
Top