Rank Function Problem - Page 2
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Rank Function Problem

  1. #11
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I think maybe my last post misled those of you who are trying to help. I entered both versions of the formulas and got all 1's in the rank column. I sorted the data I posted only to make it more easy for you to see the groups. In a real sheet, those columns will me unsorted. If the rank formula works right, I can sort on just the rank column and have all first place winners in each group will be followed by second place winners...and so on. I retreive those people easier.

    Thanks again

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Row order doesn't matter (I'm not sure my Aladin mentioned it in his notes). I added a random number column to the list and resorted it repeatedly based on those values and both formulas work just fine!

    [ This Message was edited by: Mark W. on 2002-03-08 16:42 ]

  3. #13
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just have one thing to say. You guys are all fantastic. Thank you so much for all your help. I think that is going to work.....I misread the copy part...ITs working now....Thanks again to you all

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-08 16:30, JohnJay wrote:
    I think maybe my last post misled those of you who are trying to help. I entered both versions of the formulas and got all 1's in the rank column. I sorted the data I posted only to make it more easy for you to see the groups. In a real sheet, those columns will me unsorted. If the rank formula works right, I can sort on just the rank column and have all first place winners in each group will be followed by second place winners...and so on. I retreive those people easier.

    Thanks again
    John,

    After posting the formula, I left everything as is and gone to bed.

    Indeed, forget what I said about sorting. The formula doesn't require that at all. I want to add more to what we have.

    If you have definite ranges (the data do not change frequently), the formula is OK including the shortining of

    the ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))

    to

    ROW($1:$25)

    If the range of the data is dynamic, use however the following.

    I still assume the example sample to be in A1:E25.

    In F1 enter:

    =MATCH(9.99999999999999E+307,A:A)

    In F2 enter:

    ="1:"&F1

    In B2 array-enter and copy down as far as needed:

    =MATCH(A2,LARGE(IF((OFFSET($C$2,0,0,$F$1,1)=C2)*(OFFSET($D$2,0,0,$F$1,1)=D2)*(OFFSET($E$2,0,0,$F$1,1)=E2),OFFSET($A$2,0, 0,$F$1,1)),ROW(INDIRECT("1:"&$F$1))),0)

    This gives you a small advantage in that you don't need to edit the formula when the data changes: You only need to activate B2 and give a double-click on the little black square (fill handle) in the lower right corner of this cell.

    Aladin

  5. #15
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    I read your last post and the data will be dynamic. Can you explain your last most to me. I did not quite get it.

    Thanks
    John

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-09 14:39, JohnJay wrote:
    Aladin,

    I read your last post and the data will be dynamic. Can you explain your last most to me. I did not quite get it.

    Thanks
    John
    Did you try it? Is it OFFSET that you want to know about?

    Aladin

  7. #17
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried the fromulas you and mark gave in the previous page. They both wirked great. Then I saw your next post on Dynamic data. Here is an idea of how I would like it to grow. IF I add any new columns to the sheet, the rank formula should adjust its references to still provide the correct data. However, if I add new rows to the sheet, I would like the ranks to adjust acording to the data that was entered in the new rows. I think thats what you were explaining in your last post. I was just curious about the F1 & F2 entries.

    Thanks again, you guys are great help to us newbies... LOL

    John

  8. #18
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    I understand what you are doing, but I had to make a modification on your Match function. I had to change it to:
    =MATCH(A1,A:A) cause there are codes in Column A that can Be AlphaNumeric as well as Numeric...Sort of like last minute entries that are fitted into a slot between 122 & 123 would get a value of 122A. When I modified that code, it gave me the correct number of entries in Column A.

    I think that I will stick to that and make a small field down in the bowels of the sheet to store those two formulas you added on.

    Maybe what I will do is initialize & format the sheet upon load. this way all the formulas are there and read to just accept data.

    Thanks again
    John

  9. #19
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-09 14:52, JohnJay wrote:
    I tried the fromulas you and mark gave in the previous page. They both wirked great. Then I saw your next post on Dynamic data. Here is an idea of how I would like it to grow. IF I add any new columns to the sheet, the rank formula should adjust its references to still provide the correct data. However, if I add new rows to the sheet, I would like the ranks to adjust acording to the data that was entered in the new rows. I think thats what you were explaining in your last post. I was just curious about the F1 & F2 entries.

    Thanks again, you guys are great help to us newbies... LOL

    John
    Both the original formula [ I'm the copyright owner ] and the dynamic version will adjust automatically when you insert additional columns within the data area. The latter needs not to be changed when you add new rows data to or delete rows from the data area, while the former must be edited to adapt to the changed data area. You just need the copy the formula down (by using the double-click trick).

    F1 computes what the row number of the last cell that contains a score, which OFFSET uses to return a range reference.

    F2 computes what LARGE needs.

    If you need more, just shoot. And, if you like I can send you a copy of the WB.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 15:43 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 15:44 ]

  10. #20
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I saw that when I tired your code out. I think the latter one would be the best thing for a growing worksheet. I will make a small area in the depts of the sheet, say around IV65536, to store those formulas. It will make them safe from overwrites and accidents...LOL. But I would like to intialize a sheet on startup to contain the rank formula from row 1 all the way to row 65536...this way I dont need to use the fill button at all.

    Thanks
    John

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com