Calculating Fastest Possible Time from Multiple swimmer for Relay Race

Andy Donegan

New Member
Joined
Sep 2, 2013
Messages
13
Hi,

I have been searching online for something to produce this in Excel and have only found one link which seems to be based for goodle docs only ? SAS and R: Example 9.18: Constructing the fastest relay team via enumeration

With the help from this forum and stealing as much as I can find via the search facility I have now moved on a great deal and learnt a lot.

Now this calculation I am trying to produce I just do not know were to start in excel.

I have a group of swimmers say 5 and they all have times registered (personal bests) for four individual events.

nameFreestyleBackstrokeButterflyBreaststroke
Billy00:50.2500:40.2900:39.8200:45.62
Jed00:38.5400:45.2800:52.2300:31.23
Geoffrey00:42.3400:40.1400:45.3200:41.23
Gordon00:35.3100:38.4100:42.1000:50.29
Gareth00:48.2300:36.2300:44.9100:32.29

<tbody>
</tbody>

What I am trying to figure out is the best Relay Team combination of swimmers, 1 swimmer for each individual event and the calculation will give me the fastest team selection.

Now invariably I have more than 5 swimmers to choose from, so would love to be able amend the calculation to be able to filter through a larger group than 5 if possible, I understand this makes the maths problem very large indeed.

The link above shows something and I have found an old web page which does this, but I want to incorporate this into our Gala planning sheet so that we can ensure we pick fairly and correctly the teams for our galas.

This might be what they call a lego calculation, I have found some maths sites in my searches but nothing to put my mind on to the correct path in creating an excel solver for this.

Thank you for your help in advance.

Andy.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jakeman

Active Member
Joined
Apr 29, 2008
Messages
313
Andy - I have used Excel's Solver Add-in before to find the optimal team in fantasy football. This same tool would be useful for you in finding the optimal team for swimming. To understand what I am talking about, check this article out here: Using Solver to rate sports teams - Excel - Office.com

Basically, you have four constraints to solve for: the best time for each event, and you have the other constraint which is the swimmer who has the best blend of times for all events.

Hope that helps get you in the right direction.
 

Andy Donegan

New Member
Joined
Sep 2, 2013
Messages
13
Hi Jake,

Thank you very much for taking the time to share the link, and yes that does something ideal for teams etc and for fantasy football it would based on ratings provide a good team etc.

The problem with the swimming or Relay problem is that A relay race has 4 event, Backstroke, Butterfly, Breaststroke and Freestyle.

Each swimmer has a time for each event, I could have ten boys all available to race, I need to calculate which of the 4 boys with all the different times would make the fastest over all result.

There times could be classed as ratings, so the link would sort them out as ratings, but the key difficulty to this calculation is the variations of swimmers and events, and calculating the fastest mix over all.

Unless I am not reading the link correctly, but I just tried to decipher it and I can not break it down to calculate the variations that is the hardest part of the maths.

Thank you very much again.

Andy.
 

jakeman

Active Member
Joined
Apr 29, 2008
Messages
313
Hi Andy - yes, I understand your requirement. I was trying to compare the process for coming up with the best overall result in fantasy football with your requirement.

Here's what I see. If you take the min values for each of the events from the table you listed above, I believe that your first constraint would be the overall total time for the 4 events. That would be 2:22.6 (00:35.3,00:36.2, 00:39.8, 00:31.2).

I see that your second constraint would be only 4 swimmers out of the 10 or however many you have, who best represent a mix of times that gets you as close to the 2:22.6 as possible.

You can find an excel version of Wayne Winston's solver for fantasy football out there and I'm pretty sure you can modify the fantasy football template to fit your needs. You have to change the constraints to fit yours but it'll work. I used it before for my team and got it to work. But I had to hack it up some.
 

Verald

New Member
Joined
Oct 16, 2009
Messages
28
Andy,

Since you’re using excel, you can calculate the outcome of all the possible permutations. This still only works for relatively small number of swimmers. 26 swimmers would give 358,800 different permutations.

What I did is assign each swimmer a letter (A,B,C,D, etc.), as below: The times are simplified, and I used conditional formatting to highlight the min for each event.

backstrokebreaststrokeflyfree
A40453940
B45315238
C40414542
D38504235
E36324448
F32484034
G39364136
H37514437

<tbody>
</tbody>



In a different area, you list each permutation, where ABCD would be 1 relay group, ABCE would be another and so forth (I’ll get to creating the permutations in a bit).

Creating the permutations is done via VBA as a 4-part nested loop. The code is below. The map() function converts from a number to a letter. (note: you would need to modify the map() function if creating for greater than 26 swimmers)


Code:
Sub get_perms()
    Dim vrange As Range
    Set vrange = Range("A1")
    lev = 8 ' total number of swimmers
   
    For x = 1 To lev
     For y = 1 To lev
      For Z = 1 To lev
       For a = 1 To lev
            If Not (x = y Or x = Z Or x = a Or y = Z Or y = a Or Z = a) Then
                     
                vrange.Value = map(x) & map(y) & map(Z) & map(a)
                Set vrange = vrange.Offset(1)
            End If
        Next a
      Next Z
     Next y
    Next x
   
 
End Sub
Code:
Function map(val As Variant) As String
 
    Select Case (val)
    Case 1: map = "A"
    Case 2: map = "B"
    Case 3: map = "C"
    Case 4: map = "D"
    Case 5: map = "E"
    Case 6: map = "F"
    Case 7: map = "G"
    Case 8: map = "H"
    Case 9: map = "I"
    Case 10: map = "J"
    Case 11: map = "K"
    Case 12: map = "L"
    Case 13: map = "M"
    Case 14: map = "N"
    Case 15: map = "O"
    Case 16: map = "P"
    Case 17: map = "Q"
    Case 18: map = "R"
    Case 19: map = "S"
    Case 20: map = "T"
    Case 21: map = "U"
    Case 22: map = "V"
    Case 23: map = "W"
    Case 24: map = "X"
    Case 25: map = "Y"
    Case 26: map = "Z"
   
 
End Select
 
End Function
Be sure you're on a different tab in the workbook before you run the sub it will overwrite your data.

Now use vlookups to determine the outcome of ABCD, ABCE, etc. Then filter for the minimum. This may be a bit oversimplified. I can't figure out how to post a sample workbook, but I can send it to you if needed.

Hopefully this helps.
 

rcbunde

New Member
Joined
Oct 29, 2013
Messages
1
...I took a "simpler" approach. Find the minimum time for each event; locate the time in it's respective column; offset from the specific time to determine the swimmer. Of course, if a swimmer has the fastest time in 2 or more events, a coaching decision must be made.


NameFreestyleBackstrokeButterflyBreaststroke
Billy00:50.200:40.300:39.800:45.6\
Jed00:38.500:45.300:52.200:31.2 \
Geoffrey00:42.300:40.100:45.300:41.2The Data
Gordon00:35.300:38.400:42.100:50.3 /
Gareth00:48.200:36.200:44.900:32.3/
00:35.300:36.200:39.800:31.2Determine minimum time
Cell B8:=MIN(B2:B6)
4512Find row where min time occurs
Cell B11:=MATCH(B8,B2:B6,0)
FreestyleBackstrokeButterflyBreaststroke
GordonGarethBillyJedFind the name associated
Cell B15:=OFFSET($A$1,B10,0,1,1)with the minimum time…
GordonGarethBillyJed…or all with one formula.
Cell B18:=OFFSET($A$1,MATCH(MIN(B2:B6),B2:B6,0),0,1,1)

<tbody>
</tbody>
 

bruderbell

Active Member
Joined
Aug 29, 2006
Messages
374
Because I've been learning more about user-defined functions...
Code:
Function BruteForce(DataRange As Range, Element As Integer)

If Element > DataRange.Columns.Count - 1 Then
BruteForce = "Invalid Element #"
Exit Function
End If
DataArray = DataRange
Dim Names()
Names = WorksheetFunction.Index(DataArray, 0, 1)
Dim Solution()
ReDim Solution(1 To DataRange.Columns.Count)

Solve = WorksheetFunction.Small(WorksheetFunction.Index(DataArray, 0, 2), 1)
Solution(1) = WorksheetFunction.Index(Names, WorksheetFunction.Match(Solve, WorksheetFunction.Index(DataArray, 0, 2), 0))(1)


For a = 3 To DataRange.Columns.Count
Z = WorksheetFunction.Index(DataArray, 0, a)
q = 1
On Error Resume Next
1:
Err.Clear
p = WorksheetFunction.Index(Names, WorksheetFunction.Match(WorksheetFunction.Small(Z, q), Z, 0))(1)
r = 0
r = WorksheetFunction.Match(p, Solution, 0)
If r = 0 Then
t = a - 1
Solution(t) = p
Else
q = q + 1
If q > DataRange.Rows.Count Then Exit Function
GoTo 1:
End If
Next a
BruteForce = Solution(Element)
End Function
To use, in a cell put =BruteForce($A$2:$E$6,1)
Where the range includes the people's names but not the strokes' names, and the 1 is the sequential stroke you are solving for. So 1 = Freestyle, 2 = Backstroke, etc.
The formula solves for the person with the best time, but prioritizes strokes further to the left in the table. So if Pete was fastest at both freestyle and breaststroke, the formula should return Pete for freestyle and will pick the next best swimmer for breaststroke (assuming that second best person isn't already the best swimmer for another stroke to the left in the table). So...if you want to prioritize certain strokes or legs of the race, just put them further to the left.

Hope it is useful. Keep in mind that the code isn't error-proof. If you select a datarage that doesn't have data, you may wish that you recently saved your workbook.
 

Verald

New Member
Joined
Oct 16, 2009
Messages
28
Bruderbell,

I like your approach. I started looking at it this way, but got stuck in a couple spots. I’m wondering if you can modify it to account for this bias:
“prioritizes strokes further to the left in the table.”

Since you’re looking for the best combination of people to give the best overall time, you wouldn’t want to put a swimmer in an event where they are marginally better than the next best swimmer, where at the same time the first swimmer is considerably better than the rest in a different event.

Consider the following simplified example, with swimmers Andy, Bob, Charlie and events (legs of the race) X and Y.
X
Y
Andy
20
20
Bob
21
45
Charlie
26
40

<tbody>
</tbody>

Obviously, you would want Andy in event Y and Bob in event X; for a total of 41. Your function as currently would put Andy in event X and Charlie in event Y; for a total of 60.
 

Andy Donegan

New Member
Joined
Sep 2, 2013
Messages
13
...I took a "simpler" approach. Find the minimum time for each event; locate the time in it's respective column; offset from the specific time to determine the swimmer. Of course, if a swimmer has the fastest time in 2 or more events, a coaching decision must be made.


NameFreestyleBackstrokeButterflyBreaststroke
Billy00:50.200:40.300:39.800:45.6\
Jed00:38.500:45.300:52.200:31.2 \
Geoffrey00:42.300:40.100:45.300:41.2The Data
Gordon00:35.300:38.400:42.100:50.3 /
Gareth00:48.200:36.200:44.900:32.3/
00:35.300:36.200:39.800:31.2Determine minimum time
Cell B8:=MIN(B2:B6)
4512Find row where min time occurs
Cell B11:=MATCH(B8,B2:B6,0)
FreestyleBackstrokeButterflyBreaststroke
GordonGarethBillyJedFind the name associated
Cell B15:=OFFSET($A$1,B10,0,1,1)with the minimum time…
GordonGarethBillyJed…or all with one formula.
Cell B18:=OFFSET($A$1,MATCH(MIN(B2:B6),B2:B6,0),0,1,1)

<tbody>
</tbody>
RCBUNDE thank you, but unfortunately the calculation needs to take away the coaches decision as when there are say only 10 swimmers just picking the fastest for each stroke becomes exceedingly complicated as listed further down in the post.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,622
Messages
5,469,758
Members
406,668
Latest member
ElizaR

This Week's Hot Topics

Top