Unique Rankings

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

[Leo Heuser]

"cdavidson" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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.
>

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

--
Regards,
Tom Ogilvy


"cdavidson" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
>
>
>

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

In article <[email protected]>,
cdavidson <[email protected]> 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]

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)

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 <[email protected]>,
cdavidson <[email protected]> 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

[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






"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 <[email protected]>,
> > cdavidson <[email protected]> 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
>
>

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

In article <[email protected]>,
cdavidson <[email protected]> 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

[Domenic]

In article <[email protected]>,
Morrigan <[email protected]>
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]

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

In article <[email protected]>,
Morrigan <[email protected]>
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.