| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank Tie
My formula keeps returning 1 for every value on the rank list. I'm not sure
what i'm missing. I need it to rank the ties but not skip any rankings. =COUNT(B69:B77+1-RANK(B69,B69:B77,)-RANK(B69,B69:B77,1)/2) Rank Military Time A B 1200 1530 1830 1200 1330 1730 1330 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
If you are looking for the correction factor from the Excel help entry for
the RANK function, then I guess you intended to use =COUNT(B$69:B$77)+1-RANK(B69,B$69:B$77,)-RANK(B69,B$69:B$77,1)/2 ? -- David Biddulph "RaY" wrote in message ... My formula keeps returning 1 for every value on the rank list. I'm not sure what i'm missing. I need it to rank the ties but not skip any rankings. =COUNT(B69:B77+1-RANK(B69,B69:B77,)-RANK(B69,B69:B77,1)/2) Rank Military Time A B 1200 1530 1830 1200 1330 1730 1330 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
Maybe... =IF(B69="","",SUMPRODUCT(--(B69<B$69:B$77),1/COUNTIF(B$69:B$77,B$69:B $77&""))+1) Biff's response to what I think could be the same type of ranking request. Ken Johnson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
I was however, now that I see the outcome this may not work for me. If the
rank is 3.5 for both 1200 then my VLOOKUP will still run into the same problem as if it where to lookup just 1200. Is it possible to have it say 3.1,3.2, and so on for each duplicate? I will never have more then 6 duplicates. Thank you "David Biddulph" wrote: If you are looking for the correction factor from the Excel help entry for the RANK function, then I guess you intended to use =COUNT(B$69:B$77)+1-RANK(B69,B$69:B$77,)-RANK(B69,B$69:B$77,1)/2 ? -- David Biddulph "RaY" wrote in message ... My formula keeps returning 1 for every value on the rank list. I'm not sure what i'm missing. I need it to rank the ties but not skip any rankings. =COUNT(B69:B77+1-RANK(B69,B69:B77,)-RANK(B69,B69:B77,1)/2) Rank Military Time A B 1200 1530 1830 1200 1330 1730 1330 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
Awesome. Thank you
-Ray "Herbert Seidenberg" wrote: Duplicate ranks with decimal fractions: http://www.savefile.com/files/1749951 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rank Tie
Herbert:
Could you provide the duplicate ranks with decimal fractions algorithm on this blog... I can't gain access to this website you gave. Thanks, MichaelZ "Herbert Seidenberg" wrote: Duplicate ranks with decimal fractions: http://www.savefile.com/files/1749951 |
| 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) | |||
Linear Mode
