Excel: Calculating Fastest Possible Time from Multiple swimmer for Relay Race

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
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.

This question generated 27 answers. To proceed to the answers, click here.

This thread is current as of October 29, 2013.

For more resources for Microsoft Excel