RANK ....Grrrrr

[Blobbies]

For the life of me, I can't understand why this is happening ..... Using
Excel 2007 on XP.

I have a series of gymnastics results. Sometimes an equal score will
produce a tied rank, and sometimes, for some reason, it will produce separate
rankings .... Weird!! Sample pasted below - all these results are produced
by the formula "=RANK(P22,$P$22:$P$39,0)" (obviously the P22 changes on each
row!).

Please note placings 7 and 8, and 11, and 14!!?? I would like to end up
with all eual scores producing a tied rank! I suspect there will be a simple
answer, and i thank you in advance!!



Mike
New Zealand

TOTAL Total Place
35.7000 1
34.8000 2
34.7000 3
34.6000 4
34.4000 5
34.3000 6
33.9000 7
33.9000 8
33.6000 9
33.5000 10
33.4000 11
33.4000 11
33.3000 13
33.2000 14
33.2000 14
33.1000 16
32.6000 17
31.8000 18

 

[Paste charts as enchanced metafile]

I'm guessing that the values only look the same. Try using the ROUND function
to round all values to 4 places.

 

[Max]

Fwiw, when I tested with your sample data (pasted) into my xl2003, it didn't
give the anomaly that you indicated for placings 7-8.

These were the results returned:

35.7 1
34.8 2
34.7 3
34.6 4
34.4 5
34.3 6
33.9 7
33.9 7
33.6 9
33.5 10
33.4 11
33.4 11
33.3 13
33.2 14
33.2 14
33.1 16
32.6 17
31.8 18

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[EricG]

Way out there in the umteenth decimal place, the numbers are not the same.
Use the ROUND function to round for four decimal places before doing the
rank. For example, I added a column with the numbers rounded to 4 decimal
places ("=ROUND(A2,4)"). Note how places 7 & 8 are now the same.


TOTAL Total Place ROUNDED TOTAL Total Place
35.7000000000 1 35.7000000000 1
34.8000000000 2 34.8000000000 2
34.7000000000 3 34.7000000000 3
34.6000000000 4 34.6000000000 4
34.4000000000 5 34.4000000000 5
34.3000000000 6 34.3000000000 6
33.9000000001 7 33.9000000000 7
33.9000000000 8 33.9000000000 7
33.6000000000 9 33.6000000000 9
33.5000000000 10 33.5000000000 10
33.4000000000 11 33.4000000000 11
33.4000000000 11 33.4000000000 11
33.3000000000 13 33.3000000000 13
33.2000000000 14 33.2000000000 14
33.2000000000 14 33.2000000000 14
33.1000000000 16 33.1000000000 16
32.6000000000 17 32.6000000000 17
31.8000000000 18 31.8000000000 18

 

[Blobbies]

Hi all

Thank you so much for your help - I have solved the problem with the
rounding of the marks! I still can't work out why though it needed rounding
as the scores were all inputted without a spurious .0000001 at the end ....
but anyway, the rounding fixed it!


Cheers!!