Ranking an average time from 1 to 5

[Ksean]

I have a group of soccer players that each run three races, I want to use the
average of these three timed races to rate these players within a group from
1 to 5 (1, 2, 3, 4, 5) where 5 is the smallest time increment and 1 is the
largest time increment (faster is better).

A couple of notes:
1.) Races are usually no more than a few minutes long.
2.) The end result should have approximately the same number of players of
each rating.
3.) Each number from 1 – 5 needs to be a whole number; no decimals.
4.) Each group of players can vary in size from 16 to 72.
5.) There are 12 groups in total and each group needs their own rating.

I am not even sure where to start with this.

Thanks

 

[E.Q.]

I have one approach... maybe someone else can come up with something more
elegant..
I would have each group be on a separate worksheet. Identify the player in
column A (name or ID number or whatever). Place race times in columns b,c,d
and average those in column E. Above one of the columns I'd put a count
function to note the number of players, so maybe cell H1 could be counta(A:A)
(just realize this will count a column heading if it's there). I would also
want to estimate how many players per group, to do that I'd have cell H2 have
the formula =H1/5
In column F I'd use the rank function but use the value in H1 to reverse the
ranks... so cell F2 would contain the formula =h1+1-rank(e2,E:E); that
formula would be copied down to rank times in reverse order.
I would then use the ceiling function in column g; for example g2 would be
=ceiling(F2/H2,1).
I believe that should provide a group ranking for each player.
Is that the type of thing you're looking for?

 

[E.Q.]

Playing around, with data in column E, I came up with the following formula:
=CEILING((COUNT(E:E)+1-RANK(E2,E:E))/(COUNT(E:E)/5),1)
that puts what I have in my last post together as one ugly but workable
formula

 

[Minty Fresh]

Kind of ugly, but...
Lets say group 1 has 16 players
Column A lists the player names
B race 1 times
C race 2 times
D race 3 times
Assuming there are column headers, cell E2 is =SUM(B22)/3 which is then
copied down to give the 3 race average for each player.
Cells G2 through G5 are numbered 1 through 5 (optional)
Cell I1 subtracts the slowest average time from the fast and divides the
result by 5 to return the increment on which the ranking is based:
=(MAX(E2:E17)-MIN(E2:E17))/5
Cell H2 contains the slowest time =MIN(E2:E17)
Cell I2 adds the increment to find the upper limit of rank 1: =H2+I$1
Cell H3 = I2
I3 =H3+I$1
H4 = I3
I4 =H4+I$1
H5 = I4
I5 =H5+I$1
H6 = I5
H6 is the fastest average time =MAX(E2:E17)
Enter the following formula in F2 and copy it down for all 16 players
=IF(AND(E2>=H$2,E2<I$2),1,IF(AND(E2>=H$3,E2<I$3),2,IF(AND(E2>=H$4,E2<I$4),3,IF(AND(E2>=H$5,E2<I$5),4,5))))

To expand for larger groups, change all E17 references to the E cell at the
end to your table.
Alternatively, one can mash the entire process into a single formula to go
in column F, but it gets pretty unmanagable. You could use names to make it
easier to follow, but I will leave that part of the adventure up to you.

 

[Ksean]

Minty Fresh,

Thank you for your suggestion.

I created a spreadsheet using the in formulas you proveded and it generate a
rating of 1-5 for each player however there were a couple of issues:
1.) You have me putting two different formulas in H6 which of course does
not work so I just put =MAX(E2:E17) in H6.
2.) The ratings are backwards 1 is the shortest time and 5 is the longest
time which is backwards to what I am hoping for.
3.) There isn't an approximately equal number of 1's, 2's, 3's, 4's and 5's.
The data table I created actually generated 2- 1's, 2-2's, 5-3's, 5-4's and
2-5's. What I am trying to achieve is approximately the same number of
players at each rating.

Any thoughts as to how I could achieve the end result I am looking for?

Ksean

 

[Ksean]

E.Q.

Thanks for the formula(s) they seem to work great. In the database I am
working in your last formula is actually one of the smaller formulas so
"ugly" it ain't.

The extra fine tuning saved me some time and it is definitly appreciated.

Thanks Again,
Ksean

 

[Ksean]

E.Q.

I reversed my ranking requirements when I laid out my problem; what I
actually need is for the smallest time to rate as a 5 and the longest time to
rate as a 1. How do I reverse your formula???

Thanks Again,
Ksean

 

[Ksean]

I answered my own question.

Thanks for all your help.

Ksean