Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank where lowest value is highest rank | Excel Worksheet Functions | |||
Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |