[SOLVED]Ranking

I am making a excell spread sheet for our rodeo. In column B5-B230 are the team Names, In column D is there Time. In Column E I have a macro setup to put a 1-10 on the 10 fastest times.

What i need help with is in Q17 we would like it to show the team name with the fastest time. and then in Q18 show the team with the 2nd fastest time. and so on thru 6th place.

So i guess what i need it to do is either rank column D and if it is Rank 1, show the team name in Q17.

Or else sence I allready have it listing 1 thru 10 in column E, is there a way to make a macro that says if it is 1, then show the team name in Q17, if it is 2, show the team name in Q18..???

any help would be appricaited, thanks

It all rather depends on how you're accounting for duplicity of Rank - ie if 2nd and 3rd have the same time how is this being denoted (ie both rank 2 or rank 2 & rank 3 ?)

If you always have unique identifiers you can use an INDEX/MATCH, eg:

Q17: =INDEX($B$5:$B$230,MATCH(ROWS(Q$17:Q17),$E$5:$E$230,0))
copied down

If the ranking is not unique (ie not nec. listing all digits 1 to 10) then you will need to adopt a variation of the above (or adjust the rank such that it is unique)

Donkey, thanks for the help, but every number is not unique, and we usually end up with a couple ties.

Your equation worked great except when i have a tie, it just shows the first team name as 5th, and a N/A for 6th place...

if we have two teams tie with the same time for 2nd place, then they will split the 2nd and 3rd place money.

but with this equation it just shows the first team as 2nd place and the 2nd team as N/A... How would i go about changing this to show the 2nd team as 2nd also, and then go to forth place after that.. Pretty much just how rank works, show two number 2's and no 3, and then skip to forth..

hope this made sence..

Originally Posted by seahawks3726

Your equation worked great except when i have a tie

Yes, the prior suggestion (as outlined) is reliant on unique identifiers.

To account for duplicates in terms of rank and assuming that the names (B5:B230) are themselves unique then:

Please Login or Register  to view this content.

it is imperative in the above that Q16 does not contain the name of any team in the B5:B230 listing.

So, to be clear, you're saying the relationship between Team (B) & Time (D) is not 1:1 and that any given Team may place multiple times within the Top 10 ?

A sample file might help us visualise exactly what's listed etc...

What I would suggest you do (for sake of simplicity) is modify your RANK values, eg:

E5: =IF(D5=0,"",IF(RANK(D5,D:D,1)>10,"",RANK(D5,D:D,1)+ROW(D5)/10000))
copied down
(then copied to Col G, I)

This means each RANK value becomes unique by virtue of the decimal remainder which is the ROW number / 10000

With the above in place the resulting tables are pretty straightforward, eg:

Q17: =INDEX(B$5:B$230,MATCH(LARGE($E$5:$E$230,ROWS(Q$17:Q17)),$E$5:$E$230,0))
copied across matrix Q17:S26

the above table logic can be replicated in the other tables though you will need to adjust reference to Col E (to G, I respectively) and also references to Q$17 (to Q$30 and Q$44 respectively)

Further to the above - you would also need to adjust references for the Time column in each table (from D to F etc..)

Apologies - I've used LARGE in the above whereas I should have used SMALL - adjust all formulae accordingly.

Apologies for confusion caused - (what happens when I try to multi task)