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?

"cdavidson" skrev i en meddelelse
...
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?


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.



--
Best Regards
Leo Heuser

Followup to newsgroup only please.

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

--
Regards,
Tom Ogilvy


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

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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


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

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!

In article ,
cdavidson wrote:

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

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)



Domenic Wrote:
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!

In article ,
cdavidson wrote:

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


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390190

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






"Morrigan" wrote:


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)



Domenic Wrote:
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!

In article ,
cdavidson wrote:

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


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390190

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!

In article ,
cdavidson wrote:

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

In article ,
Morrigan
wrote:

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)

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

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



Domenic Wrote:
In article ,
Morrigan
wrote:

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)

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


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390190