Let’s begin with a brain teaser. Suppose you want to do 7 unique things in 7 days, by doing exactly one per day. How many different combinations are there? Consider that on day 1, you can do any of 7 things: A, B, C, D, E, F, or G. Let’s say you pick C. On day 2, you can then do any of 6 things: A, B, D, E, F, or G. Let’s say you pick A. On day 3, you can do any of 5 things: B, D, E, F, or G. And so on.
As a result, the amount of options you have is 7 x 6 x 5 x 4 x 3 x 2 x 1, which equals 5,040. For those who remember their school days, this can be represented as 7! (you can refresh yourself on Factorials here).
Now, let’s say introduce some constraints. Let’s say you can’t do C on the day before or after doing B (that would eliminate 1/3 of your 5,040 combinations). Or that you can’t do F on any day but day 7 (that would eliminate 6/7 of your 5,040 combinations). But, of course, there is some overlap there, so figuring out how many combinations that leaves you with is a bit tricky. And you can keep introducing constraints, but at some point, there would be too many constraints to satisfy all of them.
So here’s the big question: is there a way to model this in Microsoft Excel so that you can see the results with each constraint? That answer is yes: I created a series of formulas in Excel to do just this, although I will admit that it is pretty ugly. Still, since I created it, and it took quite a bit of work, I wanted to share it with you today.
This came out of a real life example, which was a desire to create a workout schedule. I won’t bore you with the details of each workout, but my goal was to create a 7-day training split, where I would do the following on each of the 7 days:
1) Deadlifts (bodyparts: hamstrings and some additional back exercises)
2) Squats (quads, along with calves and abs)
3) Pressing (bench press and military press, so chest and some shoulders, and abs)
4) Arms (biceps, triceps, forearms)
5) Back and shoulders (ideally by doing the exercises not done on day 1 and day 3, respectively)
6) Running (legs)
7) Off
The thought process behind why these are the workouts chosen is beyond the scope of this post, so let’s just assume that these are the 7 workouts (including the off day) that you want to do. Now what are the issues one might have when trying to create a workout schedule?
Rest is very important in any training schedule, and you don’t want to work the same muscle on consecutive days. Given the 7 workout days above, this presents some real issues.
1) There are three lower-body/leg days in here: day 1, day 2, and day 6. So we should delete all of the 5,040 combinations that has any of those days next to each other.
2) I like to run on Fridays. I like to run at night, and Friday night is my best chance to take a long run. This, of course, eliminates 6 out of every 7 combinations.
3) We have two days of shoulder exercises here. Just as with legs, we should eliminate any combination that has day 3 and day 5 on back to back days.
4) Here’s a tricky one: the pressing day involves a lot of triceps, so we don’t want to have day 3 and day 4 on back to back days, either.
5) There are two back days on here, so just like with shoulders, we don’t want to have them on consecutive days. In addition, because a lot of back exercises stress the biceps, we’d like to have those on non-consecutive days, too. So days 1, 4, and 5 should all be spread out, with none appearing next to each other.
Now, if you consider the desire to satisfy all of these constraints, you might see why I thought it worthwhile to create an excel file to see if I can come up with an ideal schedule. I did just that, and there are exactly 8 workout schedules that meet this criteria. Looking at 8 combinations is a lot simpler than 5,040, and so I added one more constraint:
6) I don’t want an off day to be on the weekend, since those are the easiest days to workout. So day 7 can’t occur on a Saturday or Sunday.
That left me with 6 possible combinations out of 5,040, based on the above constraints. Now, all of this may not be very interesting to you, but what might be interesting is how I actually did this in excel. So here is the file, which you can use for any similar problem:
Again, I wanted to share this because (1) it took quite a bit of time and mental horsepower for me to create, so why not share it, and (2) it can be used for any scheduling combination needs, and I’m just using workout out as the example.
So how do you use this file? Let me explain the steps.
1) First, you need to create the 5,040 combinations, which I have shown here in cells A3 through G5042. This file already has what you need, but here is how you can create your own set of combinations for different numbers of days.
2) I listed the days in each column (A = Mon, B = Tue, and so on) and then I repeated Monday and Tuesday in columns H and I; this is purely so I can see what comes after Sunday. You will see that the cells in H and I reference the cells in A and B, respectively.
Now, let’s get to the fun excel stuff. The first issue we have is to separate the three leg days: days 1, 2, and 6. Now I’m not amazing at excel, so I had to break this into three different formulas: making sure days 1 and 2 were not next to each day, with separate formulas for keeping apart days 2 and 6 and 1 and 6. So how do you do that?
First we need the Match function. This tells us where in a series a particular number falls. All the data here is in columns A through G. So if we want to see where Day 1 is, we would enter the following formula:
=Match(1,A3:G3,0)
Here, 1 represents the data we are looking for, A3:G3 is the data set we are searching, and 0 just means that this formula will find the first value equal to the number (not really necessary for this formula, but that’s how Match works in general).
So if day 1 is in column C (i.e., the 3rd column), the result from this formula will be 3. If day 1 is in column A, the result will be 1.
This is the key to figuring out whether or not certain workouts will be next to each other. Because if
=Match(1,A3:G3,0) [one of the leg days]
gives us an answer of 3, and
=Match(2,A3:G3,0) [also one of the leg days]
gives us an answer of 2 or 4, then we should discard that day.
How do we figure that out in Excel? The way I did that was by searching for the values of day 1 and day 2 and subtracting them from each other (using absolute value, to avoid negatives). If the absolute value of the result for day 1 minus day 2 equals 1, then we have a problem. We would write that formula like this, with “no” meaning we need to discard that combination:
=IF(ABS(MATCH(1,A3:G3,0)-MATCH(2,A3:G3,0))=1,”no”,”yes”)
This means if the absolute value of the results of the match for day 1 minus the results of the match for day 2 equals 1, then we should write “no” in our cell; if it doesn’t equal 1, then we should write “yes”.
Oh, but there’s one other issue: if we have one of these on Monday (day 1) and one of these on Sunday (day 7), that wouldn’t be captured by this formula but those are still back to back days! So if the absolute difference between the two match results equals 1 or equals 6, then we have a problem. So here is the full formula that I used:
=IF(ABS(MATCH(1,A3:G3,0)-MATCH(2,A3:G3,0))=1,”no”,IF(ABS(MATCH(1,A3:G3,0)-MATCH(2,A3:G3,0))=6,”no”,”yes”))
And while that’s pretty complicated, that pretty much is the base for how I created this spreadsheet. This formula will write “no” in the cell if day 1 and day 2 are next to each other (or 6 days apart), and “yes” in the cell if they are not. And that’s all we are trying to do.
3) We can use this same formula to see if any two days are next to each other by just changing the first result in each match formula. So while the formula above compares days 1 and 2, this formula looks at days 1 and 6:
=IF(ABS(MATCH(1,A3:G3,0)-MATCH(6,A3:G3,0))=1,”no”,IF(ABS(MATCH(1,A3:G3,0)-MATCH(6,A3:G3,0))=6,”no”,”yes”))
That is how I derived the formula used in columns J, K, and L.
4) Next, we want to make sure that Friday is the run day. This is pretty easy: Friday is column E and run day is day 6, so this is the formula we need in row 3:
=IF(E3=6,”yes”,”no”)
We then drag this down for each of the 5,040 rows, and 6 out of every 7 rows will give us the “no” result we want. This is all soon in column M.
If you followed that, you can probably follow the rest of the spreadsheet. The final column, in column T, shows whether or not all of the other previous columns display an answer of yes.
Anyway, I’ll finish things up here. I hope this is either interesting or useful to at least a few of you. If you have any questions, let me know!