cascading ranking system

[Ray]

This is an interesting one .... I tried searching around and didn't
find anything, but to be honest, I wasn't quite sure what to search
for!

I'm developing a 'scoring sheet' for a local Parks & Rec department
event. The data input is fairly simple: one row for each participant
(in an event) and three blocks to enter their scores (ie distance,
time, etc). Then, using the Large function, I pull the best result
for each participant and rank them to get 1st, 2nd, & 3rd place. I
also pull the 2nd best and 3rd best results (with ranking), but only
for those that tied. So, if there's no tie in the initial ranking of
the best result, the 2nd/3rd best results are ignored.

This system works fine, but only in limited circumstances -- as soon
as there are more than 2 people tied for a position OR if there are
multiple ties (ie two tied for 1st and two tied for 2nd), the whole
system blows up.

Any ideas how I can do such a thing? Email me directly and I'll be
happy to send a sample file so you can see what I've done so far ....

TIA,
Ray

 

[Bernie Deitrick]

Ray,

Here's one way.

Let's assume that you have names in column A, and results in columns B, C, and D, with headers in
row 1.

In cell E1, enter Higher if a higher score is a good thing (like distance thrown), or Lower if a
lower value is better (like running times).

In cell E2, enter the formula

=IF($E$1="higher",MAX(B22)+LARGE(B22,2)/1000+LARGE(B22,3)/1000000,MIN(B22)+SMALL(B22,2)/1000+SMALL(B22,3)/1000000)

and in cell F2, enter the formula

=RANK(E2,$E:$E,IF($E$1="Higher",0,1))

Copy cells E2:F2 down their columns to match your data rows.

HTH,
Bernie
MS Excel MVP