handicap spreadsheet for cross country athletics

D

ditchy

Hi there all
I have been given the task of doing the handicaps for an athletic
clubs cross country season. Different distances each week and
different terrain. I have the data from the previous six races (time
it took for each athlete to complete the course). What I need to know
is how would I go about setting up a spreadsheet,could anyone help me
out? Thanks in advance
Ditchy, Ballarat Australia
 
P

Phillip Topping

What exactly do you want the spreadsheet to display?

How are the handicaps calculated?

Phillip
QLD
Australia
 
D

ditchy

Phillip Topping said:
What exactly do you want the spreadsheet to display?

How are the handicaps calculated?

Phillip
QLD
Australia
Click to expand...

Hi there Phillip
What I need is for the spreadsheet to calculate the handicaps for me.
At the moment I use the results from the last 6 events and get an
average KL/M Rate for each athlete then work out the estimated time it
will take the slowest runner to do the race. Eg, 5KL/M race @ 6:00 min
per KL/M =30:00 min
fastest runner @ 3:00 min per KL/M = 15:00 min.
Slowest runner would be of GO and the fastest would start 15:00 min
later.
The way I do it is very time consuming and I am looking for a faster
way?
I also need some variables like allow for stronger runners over hills
or allow for sprinters on shorter events. Hope this is easier to
understand.
regards, Ditchy, Ballarat Australia
 
G

GB

ditchy said:
"Phillip Topping" <[email protected]> wrote in message

Hi there Phillip
What I need is for the spreadsheet to calculate the handicaps for me.
At the moment I use the results from the last 6 events and get an
average KL/M Rate for each athlete then work out the estimated time it
will take the slowest runner to do the race. Eg, 5KL/M race @ 6:00 min
per KL/M =30:00 min
fastest runner @ 3:00 min per KL/M = 15:00 min.
Slowest runner would be of GO and the fastest would start 15:00 min
later.
The way I do it is very time consuming and I am looking for a faster
way?
I also need some variables like allow for stronger runners over hills
or allow for sprinters on shorter events. Hope this is easier to
understand.
regards, Ditchy, Ballarat Australia
Click to expand...

Okay - set it out like this:

Col A: Names

Next 6 Columns B to G - times for last 6 races

Next 6 columns H to M - distances for those races - the numbers are
obviously the same for all the athletes but it is easier to explain like
this

Next 6 Columns N to S - calculate Minutes per Km - Enter in cell N2 = B2/H2
Copy that formula down and across the rest of columns N to S

Column T - Calculate average Mins per Km - Enter in T2 = Average(N2:S2)
Copy that formula down col T

In col U enter distance for this race - the number is the same for all
athletes.
I suggest that you fiddle with this distance if you want to adjust for how
hilly the course is. For example, if this course is particularly hilly you
might add a bit to the course length to allow for that - don't know how
much - maybe 1 Km in a 10 Km race? So, if the course is 10 Km you might put
in 11 Km in col U to allow for the extra hilliness.

In Col V - calculate expected time for this race (in minutes) - In V2 enter
= T2 * U2
Copy that formula down col V

In col W - calculate start time after slowest runner (in minutes) - in W2
enter = Max (V:V) - V2
Copy that formula down col W

That's it for now. You can insert columns or move the data around when you
come to do the race after this one.

HTH

Geoff
 
D

ditchy

GB said:
Okay - set it out like this:

Col A: Names

Next 6 Columns B to G - times for last 6 races

Next 6 columns H to M - distances for those races - the numbers are
obviously the same for all the athletes but it is easier to explain like
this

Next 6 Columns N to S - calculate Minutes per Km - Enter in cell N2 = B2/H2
Copy that formula down and across the rest of columns N to S

Column T - Calculate average Mins per Km - Enter in T2 = Average(N2:S2)
Copy that formula down col T

In col U enter distance for this race - the number is the same for all
athletes.
I suggest that you fiddle with this distance if you want to adjust for how
hilly the course is. For example, if this course is particularly hilly you
might add a bit to the course length to allow for that - don't know how
much - maybe 1 Km in a 10 Km race? So, if the course is 10 Km you might put
in 11 Km in col U to allow for the extra hilliness.

In Col V - calculate expected time for this race (in minutes) - In V2 enter
= T2 * U2
Copy that formula down col V

In col W - calculate start time after slowest runner (in minutes) - in W2
enter = Max (V:V) - V2
Copy that formula down col W

That's it for now. You can insert columns or move the data around when you
come to do the race after this one.

HTH

Geoff
Click to expand...
Thanks for that Geoff
it gives me a good start, much appreciated
Ditchy
 
Top