Remember Me?

#1
June 25th 09, 01:53 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 18
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

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
June 25th 09, 02:08 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2009 Posts: 8
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

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
June 25th 09, 02:27 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
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
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

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
June 25th 09, 03:16 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2009 Posts: 220
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

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
June 29th 09, 07:27 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 18
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

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post KP Excel Worksheet Functions 1 March 8th 08 05:50 PM arron laing Excel Worksheet Functions 4 June 14th 06 07:57 AM mile3024 Excel Worksheet Functions 2 December 9th 05 10:57 PM Emmanuel Excel Worksheet Functions 3 November 12th 05 03:33 PM rob normerica Excel Discussion (Misc queries) 1 August 15th 05 09:36 PM

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