Problems Rank Ordering

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BoRed79 - 09 Jul 2007 14:00 GMT

I am trying to rank order a list of numbers, so that I can use them in an
Offset functions.

I am aware that I have some duplicate ranks in my data and have therefore
used the following formula to try to take account of these duplicates:

=RANK(U2,SportLocationList)+COUNTIF($U$2:U2,U2)-1

This works absolutely fine on all of my tables except one. On the
problematic table the function works like an ordinary rank and assigns
duplicate ranks (although ironically the ordinary rank formula assigns unique
ranks - which it shouldnt).

I am going to be using these tables as part of an automated report (where
the data will regularly change), so I would like to iron out what the problem
is.

Can anyone suggest why it might not be working on this one occasion?

Reply to this Message

Bob Phillips - 09 Jul 2007 14:03 GMT

Would help to post the data that is being ranked.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I am trying to rank order a list of numbers, so that I can use them in an
> Offset functions.
[quoted text clipped - 16 lines]
>
> Can anyone suggest why it might not be working on this one occasion?

Reply to this Message

BoRed79 - 09 Jul 2007 14:54 GMT

Below is the original data list

Location    Awareness
Pool    49.4%
Leisure     65.8%
Sports     57.9%
Lake     68.1%
Leisure2    54.5%
Community 40.6%
Centre     28.8%
Turf      33.0%
School     50.1%
Golf     34.6%
Hall     55.6%
Village    43.8%
Website    28.3%
Games     38.6%
Kickpitch     36.3%
Climb 40.6%

The ranks that are coming out are:

Location    Awareness Rank
Pool    49.4% 7
Leisure     65.8% 2
Sports     57.9% 3
Lake     68.1% 1
Leisure2    54.5% 5
Community 40.6% 10
Centre     28.8% 15
Turf      33.0% 14
School     50.1% 6
Golf     34.6% 13
Hall     55.6% 4
Village    43.8% 8
Website    28.3% 16
Games     38.6% 11
Kickpitch     36.3% 12
Climb 40.6% 10

Community and climb are both coming out with a rank of 10, which when doing
unique ranks they shouldnt be.

Thanks.

> Would help to post the data that is being ranked.
>
[quoted text clipped - 18 lines]
> >
> > Can anyone suggest why it might not be working on this one occasion?

Reply to this Message

Bob Phillips - 10 Jul 2007 09:30 GMT

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

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Below is the original data list
>
[quoted text clipped - 67 lines]
>> >
>> > Can anyone suggest why it might not be working on this one occasion?

Reply to this Message

BoRed79 - 10 Jul 2007 16:40 GMT

Unfortunately this does not remove the problem in my data.

> =RANK(B2,$B$2:$B$17)+COUNTIF($B$2:$B2,B2)-1
>
[quoted text clipped - 69 lines]
> >> >
> >> > Can anyone suggest why it might not be working on this one occasion?

Reply to this Message

Rate this thread: