Problems Rank Ordering

[BoRed79]

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?

 

[Bob Phillips]

Would help to post the data that is being ranked.

--
HTH

Bob

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

 

[BoRed79]

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.

 

[Bob Phillips]

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

--
HTH

Bob

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

 

[BoRed79]

Unfortunately this does not remove the problem in my data.