K
Ksean
I am using the following formula to determine the individual rating of a
group of players running a race.
F3=CEILING((COUNT($A$3:$A$23)+1-RANK(E3,$E$3:$E$23,1))/(COUNT($A$3:$A$23)/5),1)
A B C D E F G H
2 ID. No. Time #1 Time #2 Time #3 Average Time Rating Gender Level
3 6 01:01.1 02:59.6 01:59.9 02:00.2 5 Male U14
4 5 03:00.0 01:01.1 02:24.7 02:08.6 5 Male U14
5 16 03:02.8 02:47.7 01:01.1 02:17.2 5 Male U14
6 4 02:24.7 01:59.9 02:32.6 02:19.1 5 Male U14
7 17 02:25.2 02:11.3 02:47.4 02:28.0 4 Male U14
8 19 02:15.6 02:21.2 02:47.7 02:28.2 4 Male U14
9 11 01:59.9 02:25.8 03:01.9 02:29.2 4 Male U14
10 12 03:01.9 02:24.7 02:12.6 02:33.1 4 Male U14
11 2 02:12.6 03:01.1 02:36.9 3 Male U14
12 7 02:25.8 02:55.5 02:42.2 02:41.2 3 Male U14
13 1 02:21.2 02:42.2 03:02.8 02:42.1 3 Male U14
14 13 02:47.7 02:12.6 03:12.2 02:44.2 3 Male U14
15 20 02:59.6 03:01.1 02:25.8 02:48.8 2 Male U14
16 9 02:59.9 03:03.5 02:23.4 02:48.9 2 Male U14
17 10 02:42.2 03:01.9 02:47.7 02:50.6 2 Male U14
18 3 03:18.5 02:56.5 02:45.6 03:00.2 2 Male U14
19 8 03:01.1 03:12.2 02:55.5 03:02.9 1 Male U14
20 18 03:12.2 03:02.8 02:59.6 03:04.9 1 Male U14
21 15 03:13.2 03:05.2 02:58.6 03:05.7 1 Male U14
22 14 03:15.2 03:46.6 03:30.9 1 Male U14
My problem is that this group of 20 players is only a fraction of the
players in the total database. I need the formula to be able to pick these 20
players out of a list of 400 or more players before rating their times.
The filtering criteria would be from columns ‘G’ and ‘H’. The example above
is for all ‘U14’ males so I need the formula to rate each ‘U14 male’ compared
to all other ‘U14 males’ in the table. Other possibilities for column ‘H’
would be U6, U8, U10, U12, U16 and U18. Note that the “U†in this situation
is short for “underâ€; it does not refer to column ‘U’.
In addition there may be 30 ‘U14 males’ to choose from but 10 of them may
not have actually participated in the race. So the formula has to weed out
the ‘U14’ players that do not have an average time (the cell would read
#DIV/O!) and only rate the players that have a time. An option might be to
enter zero’s in the time slots for the players that didn’t participate thus
giving the player an average time of zero.
When I posted the table above the forum engine kept mixing up some of the
columns, sorry! I tried making the columns thinner but that didn't help.
The columns headers are:
A= ID. No.
B= Time #1
C= Time #2
D= Time #3
E= Average Time
F= Rating
G= Gender
H= Level (whicn is represented by U14)
Thanks,
Ksean
group of players running a race.
F3=CEILING((COUNT($A$3:$A$23)+1-RANK(E3,$E$3:$E$23,1))/(COUNT($A$3:$A$23)/5),1)
A B C D E F G H
2 ID. No. Time #1 Time #2 Time #3 Average Time Rating Gender Level
3 6 01:01.1 02:59.6 01:59.9 02:00.2 5 Male U14
4 5 03:00.0 01:01.1 02:24.7 02:08.6 5 Male U14
5 16 03:02.8 02:47.7 01:01.1 02:17.2 5 Male U14
6 4 02:24.7 01:59.9 02:32.6 02:19.1 5 Male U14
7 17 02:25.2 02:11.3 02:47.4 02:28.0 4 Male U14
8 19 02:15.6 02:21.2 02:47.7 02:28.2 4 Male U14
9 11 01:59.9 02:25.8 03:01.9 02:29.2 4 Male U14
10 12 03:01.9 02:24.7 02:12.6 02:33.1 4 Male U14
11 2 02:12.6 03:01.1 02:36.9 3 Male U14
12 7 02:25.8 02:55.5 02:42.2 02:41.2 3 Male U14
13 1 02:21.2 02:42.2 03:02.8 02:42.1 3 Male U14
14 13 02:47.7 02:12.6 03:12.2 02:44.2 3 Male U14
15 20 02:59.6 03:01.1 02:25.8 02:48.8 2 Male U14
16 9 02:59.9 03:03.5 02:23.4 02:48.9 2 Male U14
17 10 02:42.2 03:01.9 02:47.7 02:50.6 2 Male U14
18 3 03:18.5 02:56.5 02:45.6 03:00.2 2 Male U14
19 8 03:01.1 03:12.2 02:55.5 03:02.9 1 Male U14
20 18 03:12.2 03:02.8 02:59.6 03:04.9 1 Male U14
21 15 03:13.2 03:05.2 02:58.6 03:05.7 1 Male U14
22 14 03:15.2 03:46.6 03:30.9 1 Male U14
My problem is that this group of 20 players is only a fraction of the
players in the total database. I need the formula to be able to pick these 20
players out of a list of 400 or more players before rating their times.
The filtering criteria would be from columns ‘G’ and ‘H’. The example above
is for all ‘U14’ males so I need the formula to rate each ‘U14 male’ compared
to all other ‘U14 males’ in the table. Other possibilities for column ‘H’
would be U6, U8, U10, U12, U16 and U18. Note that the “U†in this situation
is short for “underâ€; it does not refer to column ‘U’.
In addition there may be 30 ‘U14 males’ to choose from but 10 of them may
not have actually participated in the race. So the formula has to weed out
the ‘U14’ players that do not have an average time (the cell would read
#DIV/O!) and only rate the players that have a time. An option might be to
enter zero’s in the time slots for the players that didn’t participate thus
giving the player an average time of zero.
When I posted the table above the forum engine kept mixing up some of the
columns, sorry! I tried making the columns thinner but that didn't help.
The columns headers are:
A= ID. No.
B= Time #1
C= Time #2
D= Time #3
E= Average Time
F= Rating
G= Gender
H= Level (whicn is represented by U14)
Thanks,
Ksean