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.

I was having trouble understanding the Help in Excel for this particular
problem. I have redone the formulas but now it gives something.5 for all the
ranks. I only want the duplicate ranking to be .5's. Bearing in mind that the
figures change week to week and I want someone else to use it (protected
formulas), is there any other advice?

Many thanks

"Toppers" wrote:

try:

=RANK(A1,$A$1:$A$10,1)+(COUNT($A$1:$A$10) + 1 - RANK(A1,$A$1:$A$10, 0) -
RANK(A1, $A$1:$A$10, 1))/2

This is using correction factor as described in HELP for RANK function.

HTH

"Joff" wrote:

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.

I got the following results using RANK and Adjusted RANK

Data RANK Adj RANK
1 2 2
15 7 7
28 8 8.5
5 4 4
28 8 8.5
10 5 5.5
10 5 5.5
2 3 3
0 1 1
56 10 10


"Joff" wrote:

I was having trouble understanding the Help in Excel for this particular
problem. I have redone the formulas but now it gives something.5 for all the
ranks. I only want the duplicate ranking to be .5's. Bearing in mind that the
figures change week to week and I want someone else to use it (protected
formulas), is there any other advice?

Many thanks

"Toppers" wrote:

try:

=RANK(A1,$A$1:$A$10,1)+(COUNT($A$1:$A$10) + 1 - RANK(A1,$A$1:$A$10, 0) -
RANK(A1, $A$1:$A$10, 1))/2

This is using correction factor as described in HELP for RANK function.

HTH

"Joff" wrote:

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.

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.

Thanks Toppers.

It was my mistake. I had done a copy/paste on the cells in question, but had
incorrectly referred to another cell. I only picked the mistake up on further
examination, so my apologies and thank you very much :)

"Toppers" wrote:

I got the following results using RANK and Adjusted RANK

Data RANK Adj RANK
1 2 2
15 7 7
28 8 8.5
5 4 4
28 8 8.5
10 5 5.5
10 5 5.5
2 3 3
0 1 1
56 10 10


"Joff" wrote:

I was having trouble understanding the Help in Excel for this particular
problem. I have redone the formulas but now it gives something.5 for all the
ranks. I only want the duplicate ranking to be .5's. Bearing in mind that the
figures change week to week and I want someone else to use it (protected
formulas), is there any other advice?

Many thanks

"Toppers" wrote:

try:

=RANK(A1,$A$1:$A$10,1)+(COUNT($A$1:$A$10) + 1 - RANK(A1,$A$1:$A$10, 0) -
RANK(A1, $A$1:$A$10, 1))/2

This is using correction factor as described in HELP for RANK function.

HTH

"Joff" wrote:

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.