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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]Freestyle[/TD]
[TD]Backstroke[/TD]
[TD]Butterfly[/TD]
[TD]Breaststroke[/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD]00:50.25[/TD]
[TD]00:40.29[/TD]
[TD]00:39.82[/TD]
[TD]00:45.62[/TD]
[/TR]
[TR]
[TD]Jed[/TD]
[TD]00:38.54[/TD]
[TD]00:45.28[/TD]
[TD]00:52.23[/TD]
[TD]00:31.23
[/TD]
[/TR]
[TR]
[TD]Geoffrey[/TD]
[TD]00:42.34[/TD]
[TD]00:40.14[/TD]
[TD]00:45.32[/TD]
[TD]00:41.23
[/TD]
[/TR]
[TR]
[TD]Gordon[/TD]
[TD]00:35.31[/TD]
[TD]00:38.41[/TD]
[TD]00:42.10[/TD]
[TD]00:50.29[/TD]
[/TR]
[TR]
[TD]Gareth[/TD]
[TD]00:48.23[/TD]
[TD]00:36.23[/TD]
[TD]00:44.91[/TD]
[TD]00:32.29[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Candace H]

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.

Hi Andy, do you mind sharing the final sheet for best relay teams