#1   Report Post  
Old June 25th 09, 01:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 18
Default RANK ....Grrrrr

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




  #2   Report Post  
Old June 25th 09, 02:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 8
Default RANK ....Grrrrr

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

"Blobbies" wrote:

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



  #3   Report Post  
Old June 25th 09, 02:27 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default RANK ....Grrrrr

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
---
"Blobbies" wrote:
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



  #4   Report Post  
Old June 25th 09, 03:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 220
Default RANK ....Grrrrr

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


--
-------------------
If toast always lands butter-side down, and cats always land on their feet,
what happen if you strap toast on the back of a cat and drop it?
Steven Wright (1955 - )


"Blobbies" wrote:

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



  #5   Report Post  
Old June 29th 09, 07:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 18
Default RANK ....Grrrrr

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!!

"Blobbies" wrote:

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
rank the numbers / range of data using 'RANK' and 'ABS' KP Excel Worksheet Functions 1 March 8th 08 06:50 PM
RANK, duplicate ranking but no gaps in rank arron laing Excel Worksheet Functions 4 June 14th 06 07:57 AM
Rank where lowest value is highest rank mile3024 Excel Worksheet Functions 2 December 9th 05 11:57 PM
Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. Emmanuel Excel Worksheet Functions 3 November 12th 05 04:33 PM
Rank items, select one start date, have remaining dates follow based on rank rob normerica Excel Discussion (Misc queries) 1 August 15th 05 09:36 PM


All times are GMT +1. The time now is 09:53 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017