Best Order of Songs to Optimize Time Spent

Question

Let's say that I have a band that practices 4 songs and has 7 people in the band. Everyone has to be at practice for the first song on the list but not everyone is on every song. Once each individual band member has completed all their songs for the night then and only then can they leave. Only one song can be played at a time. How would I choose the correct sequence of songs to result in the least cumulative amount of time being spent at a practice. For an example see below

Song Number | Song Time | People playing

1 15 min A, B, C

2 15 min D, E, F, G

3 15 min D, E, F, G

4 15 min A, B

In the above case it might make sense to choose the next song based on the person with the lowest remaining total songs. Using this logic above you get the sequence 1, 4, 2, 3 (or 1, 4, 3, 2) which results in saving person A 30 minutes, person B 30 minutes, and person C 45 minutes (cumulative time save = 105 min).

However a better solution is to do the sequence 2, 3, 1, 4 (or 3, 2, 1, 4) which results in saving person C 15 minutes, saving person D 30 minutes, saving person E 30 minutes, saving person F 30 minutes, and saving person G 30 minutes (cumulative time save = 135 min).

So the ultimate question is what set of steps can I follow to always get the correct most cumulative time saved (least cumulative time spent at practice) without computing each sequence individually as this becomes unviable with an increased number of songs. Ideally this method should work with varying practice lengths for each song.

Answer 1

I would create a worksheet in Microsoft Excel with a table with a row for each song and a column for each band member, with an additional column for the length of time it takes to practice each song. Then I would write a Macro which computes the time saved in musician-hours for each permutation of order of songs and lists the one permutation (or more than one in the event of a tie) that saves the most time along with the amount of time saved.

Larry.