Ranking numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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
 
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
 
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$11,A2)>1,ROUND(1/COUNTIF(A$2:A$11,A2),2))
 
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 :)
 
Back
Top