Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Ranking data in arrays

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

    Default

    Is it possible to rank data in an array? For example, the array is Height (a,b). I would like to create an array HeightRank (a,b) whereby all of the people in array a=1 would be ranked, etc. So, HeightRank (1,3) would be the rank of b=3 among all those with a=1. a represents a group and b a subgroup of each a. I want to rank b's within their group. I am not interested in inputing the array values in a spreadsheet and using the RANK function (I know how to do that) but need VBA code to do the same thing.

    Thanks.

    [ This Message was edited by: tanda on 2002-03-12 13:03 ]

    [ This Message was edited by: tanda on 2002-03-12 13:04 ]

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you give some kind of example of the data and then of what you want your array to look like?

    Thank you,

    rh

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

    Default

    The arrays contain numbers. They are two-dimensional. For example, BattingAverage (t,p). t represents the number of teams and p represents the players on each team. However, the teams and players are represented by numbers. So t=1 may be the Yankees, etc. BattingAverage (1,1) may be Derek Jeter (since Jeter is p=1 when t=1). Thus, every player in the league has a batting average assigned to it which can be represented by the appropriate BattingAverage (t,p).

    Note: my use is not for batting averages or baseball, this is just an example, it is for others type of statistics. In my actual use, t= 10 or so and p = 10 or so with there being approximately 100 distinct units with a statistic assigned to it.

    So, I want to rank a player's batting average within his group with the group being his team. Thus, I want to rank the BattingAverage (1,1), (1,2), (1,3) etc. If Derek Jeter (1,1) had the highest batting average on his team (as defined by t=1) then RankBattingAverage (1,1) = 1 would be desired result.

    I hope this makes sense.

    Thanks.

  4. #4
    Guest

    Default

    On 2002-03-12 14:40, tanda wrote:
    The arrays contain numbers. They are two-dimensional. For example, BattingAverage (t,p). t represents the number of teams and p represents the players on each team. However, the teams and players are represented by numbers. So t=1 may be the Yankees, etc. BattingAverage (1,1) may be Derek Jeter (since Jeter is p=1 when t=1). Thus, every player in the league has a batting average assigned to it which can be represented by the appropriate BattingAverage (t,p).

    Note: my use is not for batting averages or baseball, this is just an example, it is for others type of statistics. In my actual use, t= 10 or so and p = 10 or so with there being approximately 100 distinct units with a statistic assigned to it.

    So, I want to rank a player's batting average within his group with the group being his team. Thus, I want to rank the BattingAverage (1,1), (1,2), (1,3) etc. If Derek Jeter (1,1) had the highest batting average on his team (as defined by t=1) then RankBattingAverage (1,1) = 1 would be desired result.

    I hope this makes sense.

    Thanks.

    Not really. Usually you would actually re-sort the array. Can you give a better example of your data? A 2-dimensional array is just like an Excel Spreadsheet (say dimension 1 is rows and 2 is columns).

    So do you have something like:

    Jeter .300
    Giambi .330

    ?

    or

    .300 Jeter
    .330 Giambi

    ...and how do you want them to look when you are done?

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That was me - don't know why it was anon.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-12 13:02, tanda wrote:
    Is it possible to rank data in an array? For example, the array is Height (a,b). I would like to create an array HeightRank (a,b) whereby all of the people in array a=1 would be ranked, etc. So, HeightRank (1,3) would be the rank of b=3 among all those with a=1. a represents a group and b a subgroup of each a. I want to rank b's within their group. I am not interested in inputing the array values in a spreadsheet and using the RANK function (I know how to do that) but need VBA code to do the same thing.

    Thanks.

    [ This Message was edited by: tanda on 2002-03-12 13:03 ]

    [ This Message was edited by: tanda on 2002-03-12 13:04 ]
    Not sure I understood the problem fully, but it sounds like you want to do conditional ranking (or "RankIf"). For a non-VBA approach, the following could be of some value to you:

    http://www.mrexcel.com/wwwboard/messages/13717.html
    http://www.mrexcel.com/wwwboard/messages/13984.html
    http://www.mrexcel.com/wwwboard/messages/14101.html

    Aladin

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

    Default

    I have an Number (a,b).

    Number (a,b) equals some value expressed as a number for each a and b.

    I need to rank each value within a.

    So, if Number (4,5) = 0.2, then I want to rank that value among all values when a = 4.

    Maybe this will help.

    Number (4,1) = 0.3
    Number (4,2) = 0.5
    Number (4,3) = 0.1
    Number (4,4) = 0.9
    Number (4,5) = 0.2

    There are no other values when a = 4. I want to rank these values. This is a simplified example.

    The result I would like is:

    Rank (4,1) = 3
    Rank (4,2) = 2
    Rank (4,3) = 5
    Rank (4,4) = 1
    Rank (4,5) = 4

    Again, I can do it by importing the values to a spreadsheet and using the RANK function, but for a variety of reasons, I would like a VBA solution.

    [ This Message was edited by: tanda on 2002-03-12 16:44 ]

  8. #8
    Guest

    Default

    If you tried to do what you said, you would lose your 0.2, etc. values, and would be left with 1, 2, 3, 4, 5 (etc.). Do you not want those values in your array any longer?

    How about if you sorted the array, so that your results looked like this:

    Rank (4,1) = 0.9
    Rank (4,2) = 0.5
    Rank (4,3) = 0.3
    Rank (4,4) = 0.2
    Rank (4,5) = 0.1

    ?

    I think that is what you're looking for...let me know if not.

  9. #9
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't know why that last one was anon - was me.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-13 08:50, Russell Hauf wrote:
    Don't know why that last one was anon - was me.
    When you are not logged on, you become Anonymous.

    Aladin

Some videos you may like

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
  •