handicap predictions

  • Thread starter Thread starter ditchy
  • Start date Start date
D

ditchy

Hi there excellers, can someone help me with this problem

Col a Col b Col c Col d
predicted time handicap total time
row1 0:18:00 100% 0:09:00 0:27:00
row2 0:27:00 0:00:00 0:27:00
row3 0:22:30 0:04:30 0:27:00
row4 0:20:00 0:07:00 0:27:00
formula h:mm:ss h:mm:ss h:mm:ss
I am trying to do handicaps and what I need is a formula.
Row 1 col a is the fastest time which I want as 100% so that the other
handicaps will be based on the fastest time, how do I get row 2,3,4
col a to be a percentage of Row 1 col a
Regards Ditchy
T.I.A
 
Not really sure whether this should be the case,
but here's one take....

Assuming you have the table below in A1: D5

Predictedtime..handicap%.handicap..totaltime
0:18:00.....100%....0:09:00..0:27:00
0:27:00.....150%....0:13:30..0:40:30
0:22:30.....125%....0:11:15..0:33:45
0:20:00.....111%....0:10:00..0:30:00

where row 1 = col headers,
and with "benchmarks" set in row 2, viz.:
A2: 0:18:00
B2: 100%
C2: 0:09:00
D2: =A2+C2

Put in:
B3: =A3/$A$2
C3: =B3*$C$2
D3: =A3+C3

Select B3:C3
copy down B3:D5

This will fill B3:D5 with the figures in the table above,
where the handicap percentages (B3 down) are
first proportionally calculated based on the predictedtimes,
and with the handicap (C3 down) then computed
(based on the handicap%'s) before the total time
is added together.

Scratch the above if the logic isn't what you intended.
 
Max said:
Not really sure whether this should be the case,
but here's one take....

Assuming you have the table below in A1: D5

Predictedtime..handicap%.handicap..totaltime
0:18:00.....100%....0:09:00..0:27:00
0:27:00.....150%....0:13:30..0:40:30
0:22:30.....125%....0:11:15..0:33:45
0:20:00.....111%....0:10:00..0:30:00

where row 1 = col headers,
and with "benchmarks" set in row 2, viz.:
A2: 0:18:00
B2: 100%
C2: 0:09:00
D2: =A2+C2

Put in:
B3: =A3/$A$2
C3: =B3*$C$2
D3: =A3+C3

Select B3:C3
copy down B3:D5

This will fill B3:D5 with the figures in the table above,
where the handicap percentages (B3 down) are
first proportionally calculated based on the predictedtimes,
and with the handicap (C3 down) then computed
(based on the handicap%'s) before the total time
is added together.

Scratch the above if the logic isn't what you intended.
Click to expand...
Hi Max
not quite what I had in mind. The total time needs to be the same for
all, then the handicap is adjusted by subtracting the predicted from
the total.
not sure if % is the way to go
regards Ditchy
 
You need to state what will be filled in by hand and what you want
calculated by formulas.

Obviously totaltime is given, but what else? How is the predicted time
determined. Where do you need a formula?
 
Tom Ogilvy said:
You need to state what will be filled in by hand and what you want
calculated by formulas.

Obviously totaltime is given, but what else? How is the predicted time
determined. Where do you need a formula?
Click to expand...
Predicted time is determined from the results of the last 3 events, a
minutes per Kilometer. Then the minutes per Kilometer is multiplied by
the distance.
Eg
0:04:00 * 5K = 0:20:00 handicap 0:07:30
0:05:30 * 5K = 0:27:30 handicap 0:00:00
0:03:00 * 5K = 0:15:00 handicap 0:05:00
then the fastest time is subtracted from slowest time to get a
handicap.
All are expected to finish at the same time. What I need is a formula
that I can use for the next event if the runner ran faster or slower
than expected.it needs to be automated, maybe a plus or minus up to
10%?
as I have a list of 100 runners, a mix of 70 to 80 compete at these
events.
Hope that this is a bit clearer, any help is appreciated

Regards Ditchy
 
Here's another attempt ?

Suppose you have the table below in A1:D5

pred.time..handicap...10%....-10%
0:18:00...0:09:00...0:09:54...0:08:06
0:27:00...0:00:00...0:00:00...0:00:00
0:22:30...0:04:30...0:04:57...0:04:03
0:20:00...0:07:00...0:07:42...0:06:18

where:

col A = predicted time (values from the event)
col B = handicap (calculated based on col A: Difference from Slowest Time in
col A )
col C = handicap + 10% (calculated)
col D = handicap - 10% (calculated)
(cols C & D will provide the +/-10% tolerances)

Put in

B2: =MAX(A:A)-A2
C2: =B2*(1+$C$1)
D2: =B2*(1+$D$1)

Select B2:D2
Format as per col A (h:mm:ss)
Copy down as many rows as you have values in col A

Change the plus/minus tolerance to another figure, if desired, by amending
C1 & D1

Hope the above kind of aligns better with what you have in mind..
 
Max said:
Here's another attempt ?

Suppose you have the table below in A1:D5

pred.time..handicap...10%....-10%
0:18:00...0:09:00...0:09:54...0:08:06
0:27:00...0:00:00...0:00:00...0:00:00
0:22:30...0:04:30...0:04:57...0:04:03
0:20:00...0:07:00...0:07:42...0:06:18

where:

col A = predicted time (values from the event)
col B = handicap (calculated based on col A: Difference from Slowest Time in
col A )
col C = handicap + 10% (calculated)
col D = handicap - 10% (calculated)
(cols C & D will provide the +/-10% tolerances)

Put in

B2: =MAX(A:A)-A2
C2: =B2*(1+$C$1)
D2: =B2*(1+$D$1)

Select B2:D2
Format as per col A (h:mm:ss)
Copy down as many rows as you have values in col A

Change the plus/minus tolerance to another figure, if desired, by amending
C1 & D1

Hope the above kind of aligns better with what you have in mind..
Click to expand...
Hi there Max
Click to expand...
Thanks for that it will help a bit. What I am after is most likely not
possible with my limited knowledge of excel. I'll try to elaborate a
bit more.
I have details of the last 3 events, averaged to minutes per Kilometer
for each athlete and I * that by the distance to get an approximate
race time. I need some variables like if they have ran faster or
slower than predicted. A penalty of +or – a time %, but I need a
way to do that automatically because there is a list of 100 athletes.
Maybe a vlookup to compare averaged kilometer rate to actual run
rates. Thanks for your help it is much appreciated
Regards Ditchy
 
ok, here's the 3rd try...

suppose you have the table below in A1:G3

Runner..MPK..Predtime..Hand..Acttime..ActMPK..RevHand
ABC..0:04:00..0:20:00..0:07:30..0:21:00..0:04:12..0:06:45
XYZ..0:05:30..0:27:30..0:00:00..0:27:30..0:05:30..0:00:00

where:

col A = Runner (a name or number)

col B = MPK (Mins per km, averaged from the last 3 events,
ie input values)

col C = Predtime (calculated based on current event distance)

col D = Hand (Handicap, calculated, as before:
Difference from Slowest Time in col C)

col E = Acttime (Actual time of current event, ie input values)
col F = ActMPK (Actual Mins per km, calculated)
col G = RevHand (Revised Handicap, calculated)

Assume you also have 2 named "single cell" ranges:

H1 is named Dist
I1 is named Tolerance

with sample values input in H1: 5, in I1: 10%

Put in:

C2: =B2*Dist
D2: =MAX(C:C)-C2
F2: =E2/Dist
G2: =IF(F2/B2>(1+Tolerance),D2,D2*(1-Tolerance))

Select C2:D2

Format as per col B (h:mm:ss)

Copy down as many rows as you have
runners in col A

Repeat steps for F2:G2

Col G will give you the revised handicaps to be applied
for the current event.

The formula in col G checks the Actual MPK against the
(original) MPK and if the difference exceeds
the Tolerance % (i.e. the runner is actually running
at a slower speed than originally predicted), then the
original handicap calculated (ie col D) will still apply,
otherwise the revised handicap will be lowered by
the Tolerance %)

Just change the named ranges Dist and Tolerance to suit
the current event distance and the tolerance level desired

hth
Max
 
Just another point...

Supposing you have in another sheet,
Sheet1 (say) the MPK details for the last 3 events, viz:

In Sheet1 (cols A & B)

Runner..MPK
ABC..0:04:00
ABC..0:03:30
ABC..0:04:20
XYZ..0:05:00
XYZ..0:05:15
XYZ..0:05:30
etc (another 300+ rows, the runner order doesn't matter,
can be for e.g.: ABC, ABC, XYZ, XYZ, ABC, XYZ, etc)

you can extract the average MPK direct to populate col B
of the previous sheet via putting in B2 (in the previous sheet)
something like:

=AVERAGE(IF(Sheet1!$A$2:$A$400=A2,Sheet1!$B$2:$B$400))
[just adjust the last cell, ie $A$400 and $B$400,
in the formula to suit first)

and then array-entering the formula, i.e.:
Hold down Ctrl & Shift keys and press Enter
(instead of just pressing Enter)

formatting B2 as h:mm:ss

and copying down for the 100+ runners in col A
 
Max said:
ok, here's the 3rd try...

suppose you have the table below in A1:G3

Runner..MPK..Predtime..Hand..Acttime..ActMPK..RevHand
ABC..0:04:00..0:20:00..0:07:30..0:21:00..0:04:12..0:06:45
XYZ..0:05:30..0:27:30..0:00:00..0:27:30..0:05:30..0:00:00

where:

col A = Runner (a name or number)

col B = MPK (Mins per km, averaged from the last 3 events,
ie input values)

col C = Predtime (calculated based on current event distance)

col D = Hand (Handicap, calculated, as before:
Difference from Slowest Time in col C)

col E = Acttime (Actual time of current event, ie input values)
col F = ActMPK (Actual Mins per km, calculated)
col G = RevHand (Revised Handicap, calculated)

Assume you also have 2 named "single cell" ranges:

H1 is named Dist
I1 is named Tolerance

with sample values input in H1: 5, in I1: 10%

Put in:

C2: =B2*Dist
D2: =MAX(C:C)-C2
F2: =E2/Dist
G2: =IF(F2/B2>(1+Tolerance),D2,D2*(1-Tolerance))

Select C2:D2

Format as per col B (h:mm:ss)

Copy down as many rows as you have
runners in col A

Repeat steps for F2:G2

Col G will give you the revised handicaps to be applied
for the current event.

The formula in col G checks the Actual MPK against the
(original) MPK and if the difference exceeds
the Tolerance % (i.e. the runner is actually running
at a slower speed than originally predicted), then the
original handicap calculated (ie col D) will still apply,
otherwise the revised handicap will be lowered by
the Tolerance %)

Just change the named ranges Dist and Tolerance to suit
the current event distance and the tolerance level desired

hth
Max
Click to expand...
Thank you again, where would we be without the help from you experts.

I still have a small problem though.
the formula, =IF(F2/B2>(1+Tolerance),D2,D2*(1-Tolerance))
The formula in col G checks the Actual MPK against the
(original) MPK and if the difference exceeds
the Tolerance % (i.e. the runner is actually running
at a slower speed than originally predicted), then the
original handicap calculated (ie col D) will still apply,
otherwise the revised handicap will be lowered by
the Tolerance %)
Click to expand...
this needs to be reversed. ie, if a runner is running faster than
expected the handicap needs to be increased.
I have tried to alter with no success, I have included the sheet
selection example that you gave.
T.I.A
regards Ditchy
 
Try instead in G2: =IF(F2/B2<(1-Tolerance),D2*(1+Tolerance),D2)

This should now satisfy. cheers.
 
Max said:
Try instead in G2: =IF(F2/B2<(1-Tolerance),D2*(1+Tolerance),D2)

This should now satisfy. cheers.
Click to expand...
Thank you Max
you have been a great help,
novice people like me would be lost without help from people like you
regards
Ditchy, Ballarat Australia
 
you're welcome, Ditchy!

do linger around these excel newsgroups,
- great communities with lots of helpful folks around!

cheers
Max
 
Back
Top