Calculating Fastest Possible Time from Multiple swimmer for Relay Race

[Andy Donegan]

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.

name	Freestyle	Backstroke	Butterfly	Breaststroke
Billy	00:50.25	00:40.29	00:39.82	00:45.62
Jed	00:38.54	00:45.28	00:52.23	00:31.23
Geoffrey	00:42.34	00:40.14	00:45.32	00:41.23
Gordon	00:35.31	00:38.41	00:42.10	00:50.29
Gareth	00:48.23	00:36.23	00:44.91	00: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.

 

[Seti]

You can also solve this problem with Excel's solver add-in. It requires a little manual set-up, but works pretty easily. Let's assume that you have your data as presented in the OP, and that it starts in A1. You need to add 4 columns to the right of your data and fill them with 1's (this can be any number, but at the end of the process, these will be either 1 or 0). Then in cell J2, put =SUM(F2:I2) and copy down to the last row of swimming data. Next, in cell F7 (assuming the data you posted), put the following formula =SUM(F2:F6) and copy across to I7. Then in J7, put =SUMPRODUCT(B2:E6,F2:I6). Our goal is now to minimize J7 by setting a single 1 in each column and each row for the rows with swimmers.

The next step is to set up the solver. Launch solver, then set the target cell to J7, choose the min option, the by changing cells is set to F2:I6, and then we need to add 5 constraints. they are as follows:
$F$2:$I$6 <= 1
$F$2:$I$6 integer
$F$2:$I$6 >= 0
$F$7:$I$7 = 1
$J$2:$J$6 <= 1

The first 3 constrain the values in F2:I6 to be either 1 or 0, the one on row 7, requires that there be exactly one 1 in each column and column J constrains us to no more than 1 event per swimmer.

When you then run solver, you should end up with only four 1's in the range of F2:I6, one in each column (column F will be the swimmer to swim the freestyle with the row containing the 1 identifying the swimmer, repeat for each column). Hope this helps. If you have a different number of swimmers each time, I would add dummy records with absurdly high times for the blank rows and set the constraints to include all rows. So if you have as many as 10 swimmers, maybe set this up for 15 or 20 and load very slow times for these dummy rows. Hopefully this will help, if you choose not to use the VBA solution.

 

[Andy Donegan]

Ok, I am learning so much right now it hurts, I have created the user defined function and it works excellently so impressed thank you.

Only one small problem which is my fault and if you scream I totally understand, some times a swimmer may not have a time for a discipline due to it being a bad stroke for them, or they have not swam it that season yet.

In the case of this happening what happens the blank is selected obviously as 0 time and picked as best time.

Is there potential with this to ignore when no time for the swimmer, its easy to setup with the ABCD function way, I just do an IF statement and dont do a count at the end if any cell is =0, but obviously this coded function is a lot more complex.

Also I have tested this with data now and anything over 7 rows it gives an error, or is it just me, apologies.

Sorry to add a humongous problem into this but the work you have done is greatly appreciated.

Andy.

Not to be outdone by the solver function, try this:

Function TrueBrute(DataRange As Range, OutPut As String)
'datarange should just be data, no labels.  People as rows, legs of race as columns
'output 1 = names, 2 = time
If OutPut = 1 Or OutPut = 2 Then Else Exit Function
Dim a As Integer, b As Integer, c As Integer, d As Integer
x = DataRange.Rows.Count
Dim permutations
ReDim permutations(1 To 2, 1 To WorksheetFunction.Fact(x))
Z = 1
Dim names()
ReDim names(1 To DataRange.Rows.Count)
    For i = 1 To UBound(names)
    names(i) = DataRange.Cells(i, 1).Offset(, -1).Value
    Next i
For a = 1 To x
 For b = 1 To x
  For c = 1 To x
   For d = 1 To x
        If Not (a = b Or a = c Or a = d Or b = c Or b = d Or c = d) Then
             permutations(1, Z) = a & "-" & b & "-" & c & "-" & d
             permutations(2, Z) = map(DataRange, a, 1) + map(DataRange, b, 2) + map(DataRange, c, 3) + map(DataRange, d, 4)
             Z = Z + 1
             
        End If
   Next d
  Next c
 Next b
Next a
m = WorksheetFunction.Match(WorksheetFunction.Min(WorksheetFunction.Index(permutations, 2, 0)), WorksheetFunction.Index(permutations, 2, 0), 0)
mm = WorksheetFunction.Min(WorksheetFunction.Index(permutations, 2, 0))
'find locations of - in best solution
a = WorksheetFunction.Search("-", permutations(1, m))
b = WorksheetFunction.Search("-", permutations(1, m), a + 1)
c = WorksheetFunction.Search("-", permutations(1, m), b + 1)
Select Case OutPut
Case 1
TrueBrute = names(Mid(permutations(1, m), 1, a - 1)) & ", " & names(Mid(permutations(1, m), a + 1, b - a - 1)) & ", " & names(Mid(permutations(1, m), b + 1, c - b - 1)) & ", " & names(Mid(permutations(1, m), c + 1, Len(permutations(1, m))))
Case 2
TrueBrute = permutations(2, m)
End Select
End Function
Function map(r As Range, i As Integer, j As Integer)
map = r.Cells(i, j).Value
End Function

So you can input a 1 for the names and a 2 for the time.
For instructino on creating UDFs, see here. How to Create a User Defined Function in Microsoft Excel

Bam!

 

[Verald]

Only one small problem which is my fault and if you scream I totally understand, some times a swimmer may not have a time for a discipline due to it being a bad stroke for them, or they have not swam it that season yet.

In the case of this happening what happens the blank is selected obviously as 0 time and picked as best time.

A simple solution would be to replace each 0 time with 99, or something that would be larger than any "real" score before running the calculations. Ctrl-H will bring up the dialog box.

 

[Andy Donegan]

shg,

Thank you, the workbook is ideal, unfortunately it counts no times as a zero, so if a swimmer has not swum a time that season or is a young new swimmer who has just moved up to that event they would be picked for the team on a 0 time.

I looked through the coding on the sheet but it is way beyond my collections.

Thank you ever so much for the reference it is an excellent tool, I just will not be able to use it as is for the moment.

Andy.

There's a workbook at https://app.box.com/s/tmvj4kcrn8ixyk0n2o62 that may do what you want. It shows an example where the fastest swimmer does not swim a particular leg because he has a better time on another stroke.

 

[bruderbell]

Ok, I am learning so much right now it hurts, I have created the user defined function and it works excellently so impressed thank you.

Only one small problem which is my fault and if you scream I totally understand, some times a swimmer may not have a time for a discipline due to it being a bad stroke for them, or they have not swam it that season yet.

In the case of this happening what happens the blank is selected obviously as 0 time and picked as best time.

Is there potential with this to ignore when no time for the swimmer, its easy to setup with the ABCD function way, I just do an IF statement and dont do a count at the end if any cell is =0, but obviously this coded function is a lot more complex.

Also I have tested this with data now and anything over 7 rows it gives an error, or is it just me, apologies.

Sorry to add a humongous problem into this but the work you have done is greatly appreciated.

Andy.

You could manually seed the blanks with huge times, like 100 days. The formula would never pick them then. But it's easier to just do it in the code:

Function TrueBrute(DataRange As Range, OutPut As String)
'datarange should just be data, no labels.  People as rows, legs of race as columns
'output 1 = names, 2 = time
If OutPut = 1 Or OutPut = 2 Then Else Exit Function
If DataRange.Columns.Count <> 4 Then Exit Function
Dim a As Integer, b As Integer, c As Integer, d As Integer
x = DataRange.Rows.Count
Dim permutations
ReDim permutations(1 To 2, 1 To WorksheetFunction.Fact(x))
Z = 1
Dim names()
ReDim names(1 To DataRange.Rows.Count)
    For i = 1 To UBound(names)
    names(i) = DataRange.Cells(i, 1).Offset(, -1).Value
    Next i
For a = 1 To x
 For b = 1 To x
  For c = 1 To x
   For d = 1 To x
        If Not (a = b Or a = c Or a = d Or b = c Or b = d Or c = d) Then
             
             permutations(1, Z) = a & "-" & b & "-" & c & "-" & d
             permutations(2, Z) = map(DataRange, a, 1) + map(DataRange, b, 2) + map(DataRange, c, 3) + map(DataRange, d, 4)
             Z = Z + 1
             
        End If
   Next d
  Next c
 Next b
Next a
m = WorksheetFunction.Match(WorksheetFunction.Min(WorksheetFunction.Index(permutations, 2, 0)), WorksheetFunction.Index(permutations, 2, 0), 0)
mm = WorksheetFunction.Min(WorksheetFunction.Index(permutations, 2, 0))
'find locations of - in best solution
a = WorksheetFunction.Search("-", permutations(1, m))
b = WorksheetFunction.Search("-", permutations(1, m), a + 1)
c = WorksheetFunction.Search("-", permutations(1, m), b + 1)
Select Case OutPut
Case 1
TrueBrute = names(Mid(permutations(1, m), 1, a - 1)) & ", " & names(Mid(permutations(1, m), a + 1, b - a - 1)) & ", " & names(Mid(permutations(1, m), b + 1, c - b - 1)) & ", " & names(Mid(permutations(1, m), c + 1, Len(permutations(1, m))))
Case 2
TrueBrute = permutations(2, m)
End Select
End Function
Function map(r As Range, i As Integer, j As Integer)
If r.Cells(i, j).Value = 0 Then
map = 10000000
Else
map = r.Cells(i, j).Value
End If
End Function

Note that the change is in the function Map, where it now checks for zero values and returns ten million if blank (rather than returning zero). Thus, that blank value is never the best choice.

 

[bruderbell]

You can also solve this problem with Excel's solver add-in. It requires a little manual set-up, but works pretty easily. Let's assume that you have your data as presented in the OP, and that it starts in A1. You need to add 4 columns to the right of your data and fill them with 1's (this can be any number, but at the end of the process, these will be either 1 or 0). Then in cell J2, put =SUM(F2:I2) and copy down to the last row of swimming data. Next, in cell F7 (assuming the data you posted), put the following formula =SUM(F2:F6) and copy across to I7. Then in J7, put =SUMPRODUCT(B2:E6,F2:I6). Our goal is now to minimize J7 by setting a single 1 in each column and each row for the rows with swimmers.

The next step is to set up the solver. Launch solver, then set the target cell to J7, choose the min option, the by changing cells is set to F2:I6, and then we need to add 5 constraints. they are as follows:
$F$2:$I$6 <= 1
$F$2:$I$6 integer
$F$2:$I$6 >= 0
$F$7:$I$7 = 1
$J$2:$J$6 <= 1

The first 3 constrain the values in F2:I6 to be either 1 or 0, the one on row 7, requires that there be exactly one 1 in each column and column J constrains us to no more than 1 event per swimmer.

When you then run solver, you should end up with only four 1's in the range of F2:I6, one in each column (column F will be the swimmer to swim the freestyle with the row containing the 1 identifying the swimmer, repeat for each column). Hope this helps. If you have a different number of swimmers each time, I would add dummy records with absurdly high times for the blank rows and set the constraints to include all rows. So if you have as many as 10 swimmers, maybe set this up for 15 or 20 and load very slow times for these dummy rows. Hopefully this will help, if you choose not to use the VBA solution.

Thanks for the explanation - I got it to work that way. That's a great tool for my toolchest. Just tried it with blank values and you are right - you'd have to seed high values so that it doesnt' pick the blanks.

 

[shg]

... unfortunately it counts no times as a zero, ...

If there is no other solution that you prefer, let me know, and I'll accommodate that.

 

[DaveWC]

Andy,

I am wondering if you would be willing to share your excel template for this macro. I am currently trying to solve the same problem but have been unable to make it work.

Thanks.