Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average time | Excel Worksheet Functions | |||
Average of Day and Time | Excel Worksheet Functions | |||
Average Time | Excel Worksheet Functions | |||
average rate of change per given time period between 2 moments in time of a value | Excel Worksheet Functions | |||
Average Time | Excel Worksheet Functions |