Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounting for Duplicates in Rank
Good morning --
I am trying to account for duplicates in using the RANK worksheet function. Basically, I have 43 categories that I am trying to rank, but I am only showing the Top 5 / Bottom 5. Each of these sets of five rarely ever have duplicates, but due to the fact that the mid section of results typically do have duplicates, I cannot properly account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5 performers). The help documentation did not particularly help me in this situation. Anyone out there with a solution? Thanks, Martin Austin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounting for Duplicates in Rank
Say in A1 thru A43 we have:
14 6 5 4 12 19 6 1 11 7 3 8 16 1 14 10 3 5 6 10 5 3 19 7 9 11 16 3 20 12 5 14 8 19 3 13 3 16 6 1 1 2 10 In B1 thru B5 enter: =LARGE(A$1:A$43,ROW()) to see: 20 19 19 19 16 In C1 thru C5 enter: =SMALL(A$1:A$43,ROW()) to see: 1 1 1 1 2 In this way duplicates are properly displayed. This is what you would see if the data were sorted and you examines the top and bottom 5. -- Gary''s Student - gsnu200726 " wrote: Good morning -- I am trying to account for duplicates in using the RANK worksheet function. Basically, I have 43 categories that I am trying to rank, but I am only showing the Top 5 / Bottom 5. Each of these sets of five rarely ever have duplicates, but due to the fact that the mid section of results typically do have duplicates, I cannot properly account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5 performers). The help documentation did not particularly help me in this situation. Anyone out there with a solution? Thanks, Martin Austin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounting for Duplicates in Rank
On May 31, 9:51 am, Gary''s Student
wrote: Say in A1 thru A43 we have: 14 6 5 4 12 19 6 1 11 7 3 8 16 1 14 10 3 5 6 10 5 3 19 7 9 11 16 3 20 12 5 14 8 19 3 13 3 16 6 1 1 2 10 In B1 thru B5 enter: =LARGE(A$1:A$43,ROW()) to see: 20 19 19 19 16 In C1 thru C5 enter: =SMALL(A$1:A$43,ROW()) to see: 1 1 1 1 2 In this way duplicates are properly displayed. This is what you would see if the data were sorted and you examines the top and bottom 5. -- Gary''s Student - gsnu200726 " wrote: Good morning -- I am trying to account for duplicates in using the RANK worksheet function. Basically, I have 43 categories that I am trying to rank, but I am only showing the Top 5 / Bottom 5. Each of these sets of five rarely ever have duplicates, but due to the fact that the mid section of results typically do have duplicates, I cannot properly account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5 performers). The help documentation did not particularly help me in this situation. Anyone out there with a solution? Thanks, Martin Austin- Hide quoted text - - Show quoted text - Hi and sorry for the ambiguity in my request. What I was looking to do is skip duplicates in the ranking. So I do not want to see 1, 2, 2, 2, 5. I want to see 1, 2, 3, 4, 5. Even if I could use SMALL or LARGE and exclude items where the value is zero, that would fix the problem I'm having. Thanks, Martin Austin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounting for Duplicates in Rank
See if this helps:
http://tinyurl.com/2tl9ar -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Martin Austin" wrote in message oups.com... On May 31, 9:51 am, Gary''s Student wrote: Say in A1 thru A43 we have: 14 6 5 4 12 19 6 1 11 7 3 8 16 1 14 10 3 5 6 10 5 3 19 7 9 11 16 3 20 12 5 14 8 19 3 13 3 16 6 1 1 2 10 In B1 thru B5 enter: =LARGE(A$1:A$43,ROW()) to see: 20 19 19 19 16 In C1 thru C5 enter: =SMALL(A$1:A$43,ROW()) to see: 1 1 1 1 2 In this way duplicates are properly displayed. This is what you would see if the data were sorted and you examines the top and bottom 5. -- Gary''s Student - gsnu200726 " wrote: Good morning -- I am trying to account for duplicates in using the RANK worksheet function. Basically, I have 43 categories that I am trying to rank, but I am only showing the Top 5 / Bottom 5. Each of these sets of five rarely ever have duplicates, but due to the fact that the mid section of results typically do have duplicates, I cannot properly account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5 performers). The help documentation did not particularly help me in this situation. Anyone out there with a solution? Thanks, Martin Austin- Hide quoted text - - Show quoted text - Hi and sorry for the ambiguity in my request. What I was looking to do is skip duplicates in the ranking. So I do not want to see 1, 2, 2, 2, 5. I want to see 1, 2, 3, 4, 5. Even if I could use SMALL or LARGE and exclude items where the value is zero, that would fix the problem I'm having. Thanks, Martin Austin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounting for Duplicates in Rank
Hi Martin:
How about two steps or two columns. The first column puts them in rank order and the second column removes dups: if the data is in A1 thru A43, then in B1 thru B43 put: =LARGE(A:A,ROW()) In C1 put: =B1 in C2 thru C43 put: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$B$1:$B$43&""), 0)),"",INDEX(IF(ISBLANK($B$1:$B$43),"",$B$1:$B$43) ,MATCH(0,COUNTIF(C$1:C1,$B$1:$B$43&""),0))) as an array formula (CNTRL-SHIFT-ENTER) and copy down. Here is an example of what you might see: 21 39 39 39 38 38 13 38 35 5 38 34 30 35 32 8 34 31 32 32 30 29 32 29 27 32 28 32 31 27 19 30 25 38 29 24 32 28 23 8 27 21 31 27 20 25 25 19 14 25 18 16 25 17 4 24 16 18 24 15 27 23 14 24 23 13 34 23 11 28 23 8 35 21 5 25 21 4 17 20 16 19 23 18 23 17 11 16 38 16 16 16 23 15 23 14 38 14 24 13 4 11 21 8 14 8 25 5 20 4 15 4 -- Gary''s Student - gsnu200726 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank(A1,C1:C5) - Rank using 2 ranges | 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) |