I need to rank the following numbers:

Entered Values What Excel has ranked What I actually want
1 2
2
15 7
7
28 9
9.5
5 4
4
28 9
9.5
10 6
6.5
10 6
6.5
2 3
3
0 1
0
56 10
10

This is for a fishing score data. The weight is the 'Entered Values' (and is
changeable week to week). I can get around the '0' that i actually want
(there might of course be another way), but am really struggling to get the
..5's sorted. Any help will be greatly apprieciated.

After a quick edit, this is what my original post should look like!!

I need to rank the following numbers:

Entered Values Current Excel rank What I want
1 2 2
15 7 7
28 9 9.5
5 4 4
28 9 9.5
10 6 6.5
10 6 6.5
2 3 3
0 1 0
56 10 10

This is for a fishing score data. The weight is the 'Entered Values' (and is
changeable week to week). I can get around the '0' that i actually want
(there might of course be another way), but am really struggling to get the
.5's sorted. Any help will be greatly apprieciated.

In your "What Excel has ranked" column I get 8 where you have 9 and I get 5
where you have 6 using this formula:

=RANK(A2,A$2:A$11,1)

If a number appeared 3 times would you then want n.3 ? If a number appeared
4 times would you want n.25 ? If so try this:

=IF(A2=0,0,RANK(A2,A$2:A$11,1))+IF(COUNTIF(A$2:A$1 1,A2)1,ROUND(1/COUNTIF(A$2:A$11,A2),2))

--
Biff
Microsoft Excel MVP


"Joff" wrote in message
...
I need to rank the following numbers:

Entered Values What Excel has ranked What I actually
want
1 2
2
15 7
7
28 9
9.5
5 4
4
28 9
9.5
10 6
6.5
10 6
6.5
2 3
3
0 1
0
56 10
10

This is for a fishing score data. The weight is the 'Entered Values' (and
is
changeable week to week). I can get around the '0' that i actually want
(there might of course be another way), but am really struggling to get
the
.5's sorted. Any help will be greatly apprieciated.