Ranking data in arrays

tanda

New Member
Joined
Feb 18, 2002
Messages
13
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Can you give some kind of example of the data and then of what you want your array to look like?

Thank you,

rh
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top