Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Ranking an average time from 1 to 5

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default Ranking an average time from 1 to 5

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?
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default Ranking an average time from 1 to 5

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Ranking an average time from 1 to 5

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.


"Ksean" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Ranking an average time from 1 to 5

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



"Minty Fresh" wrote:

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.


"Ksean" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Ranking an average time from 1 to 5

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." wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Ranking an average time from 1 to 5

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

"E.Q." wrote:

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Ranking an average time from 1 to 5

I answered my own question.

Thanks for all your help.

Ksean



"Ksean" wrote:

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

"E.Q." wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average time bpc Excel Worksheet Functions 15 July 14th 08 06:44 PM
Average of Day and Time Jarod Excel Worksheet Functions 3 July 11th 08 10:55 PM
Average Time Karen Excel Worksheet Functions 5 September 20th 07 06:19 PM
average rate of change per given time period between 2 moments in time of a value David Virgil Hobbs Excel Worksheet Functions 1 December 19th 06 07:24 AM
Average Time ATP Excel Worksheet Functions 1 July 20th 05 06:30 PM


All times are GMT +1. The time now is 09:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"