Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Numbers in Ranks
I am trying to rank a list of 10 numbers and I know how to rank using
the RANK function. I have a problem though. When there are two (or more) values that are the same, i want them to show .5 instead of multiple 4s. i.e. 1356 9 1394 12 1252 6 1141 2 1374 10 1388 11 1152 3 1242 5 1256 8 1252 6 1158 4 980 1 There are two occurences of 6.. but I would them to each be 6.5 rather than each of them being 6. Any thoughts? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Numbers in Ranks
If a number appeared 3 times would you then want the rank to be n.33333? If
4 times, then rank should be n.25? If that's what you want, try this (with the decimal rounded to 2 places): =ROUND(RANK(A1,A$1:A$12,1)+MOD(1/COUNTIF(A$1:A$12,A1),1),2) -- Biff Microsoft Excel MVP wrote in message ... I am trying to rank a list of 10 numbers and I know how to rank using the RANK function. I have a problem though. When there are two (or more) values that are the same, i want them to show .5 instead of multiple 4s. i.e. 1356 9 1394 12 1252 6 1141 2 1374 10 1388 11 1152 3 1242 5 1256 8 1252 6 1158 4 980 1 There are two occurences of 6.. but I would them to each be 6.5 rather than each of them being 6. Any thoughts? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Numbers in Ranks
If you applied the above formula
to the modified list below, you would get the results in the second column. Note that 5.25 repeats 4 times and that rank 6, 7 and 8 are missing: 1356 9.00 6.00 1394 12.00 9.00 1252 5.25 5.00 1141 2.00 2.00 1374 10.00 7.00 1388 11.00 8.00 1151 3.00 3.00 1252 5.25 5.75 1252 5.25 5.50 1252 5.25 5.25 1158 4.00 4.00 980 1.00 1.00 If you want the results in the third column, use this formula: =SUMPRODUCT((amt<amt 2:2)*(1/COUNTIF(amt,amt))) +1+MOD(1/COUNTIF(amt,amt 2:2),1)* MOD(COUNTIF(amt 2:$13,amt 2:2),COUNTIF(amt,amt 2:2)) It is assumed that your list is named "amt" and starts in row 2 and ends in row 13. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I differentiate between duplicate ranks? | Excel Worksheet Functions | |||
Duplicate numbers | New Users to Excel | |||
DUPLICATE RANKS | Excel Worksheet Functions | |||
duplicate numbers | Excel Discussion (Misc queries) | |||
no duplicate numbers | Excel Discussion (Misc queries) |