Ranking an average time from 1 to 5

  • Thread starter Thread starter Ksean
  • Start date Start date
K

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
 
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?
 
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
 
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(B2:D2)/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.
 
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
 
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
 
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
 
Back
Top