Download Excel file from Press F9 Until Close
Press F9 Until Close
Guess Until Correct
Brute Force Solving
Measure of Closeness
L: I am the commissioner of a swim league
There are eight teams this year. Each team hosts one meet and is the home team.
A meet will have 4 or 5 teams. (5 because you need some meets to have 5 to solve the problem. 4 because some pools only have 4 lanes.)
How to arrange the schedule so every team swims against every other team twice?
In the past, when we had 5, 6, or 7 teams, I could solve it by pressing F9 until I was close.
But this year, with 8 teams, it is not coming out.
Need to press F9 many times
Customize your model so it results in a one-number "Measure of Closeness"
Keep you eye on that number and press F9 until you get a better answer
Save that answer
Keep pressing F9 until you get close enough
Use a VBA macro to automate that process
Press F9 Until Close
Guess Until Correct
Brute Force Solving
Measure of Closeness
L: I am the commissioner of a swim league
There are eight teams this year. Each team hosts one meet and is the home team.
A meet will have 4 or 5 teams. (5 because you need some meets to have 5 to solve the problem. 4 because some pools only have 4 lanes.)
How to arrange the schedule so every team swims against every other team twice?
In the past, when we had 5, 6, or 7 teams, I could solve it by pressing F9 until I was close.
But this year, with 8 teams, it is not coming out.
Need to press F9 many times
Customize your model so it results in a one-number "Measure of Closeness"
Keep you eye on that number and press F9 until you get a better answer
Save that answer
Keep pressing F9 until you get close enough
Use a VBA macro to automate that process
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2180: Press F9 Until Best.
You know, this is one of those, I don't know what the title should be.
It might be, "Guess Until Right", or "Brute Force Solving", or "Measure of Closeness".
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by L, the Commissioner of the Swim League.
This year, they have eight teams in the Swim League.
Each team hosts one meet, and it's the home team.
And then, each meet will have 4 or 5 teams, so the home team plus another 3, or the home team plus another 4.
Why is this?
Well, 5 because you need to have some meets to have 5 in order to solve the problem, and 4 because some pools only have 4 lanes, so they're constrained by that.
And L asks how to arrange the schedule so every team swims against every other team twice.
That's our goal.
And he noted, in the past.
when they had 5, 6, or 7 teams, he would just keep pressing F9 until he was close.
But this year, with 8 teams, it's not coming out.
Alright, L, here's my answer: Press F9 faster.
But then I added a note that said, "Your life is going to be easier if you can reduce the model down to a single number "Measure of Closeness".
At which point, L replied and said, "Which video explains "Measure of Closeness"?
So that's when I realized I was going to have to dive in and learn how to become the Scheduling Commissioner of a Swim League.
Alright, here's my workbook.
I started out with the 8 home teams, 1 through 8.
Everyone hosts one home match and they're going to have either 3 or 4 other competitors, depending on how many lanes they have.
Alright?
So I'm assuming right here that we have-- seven of the venues have-- 5 or more lanes, and 1 venue has 4 lanes.
And then my question was, well, how many ways to arrange the competitors?
Alright?
So, if the home team is 1, you could do something like 2345, and then 2346, 2347, 2348, and actually listed all those down here, right?
And it turned out that there are 35 different ways to have 5 competitors, if you know that a home team is 1; and then 35 ways for a home team 2; and it was 35 ways for each.
And what was really interesting to me is, even in the situations where you only have 4 lanes, this one out here it also is 35 possible competitors, which I don't quite understand how that works out.
But it does.
It's really nice that there's 35 choices in each case, alright?
So we're going to set up a random model here, where we're going to choose a random number between 1 and 35.
Alright?
And based on that random number between 1 and 35, we go and grab the value from one of the possible 35 solutions-- or in this case, one of the possible 35 solutions further down.
Alright.
So, it's grabbing from the green section.
And what I'm doing here-- just to make my math work out-- is the last team is considered to be Team 9, which there isn't a team 9-- we're just going to ignore team 9s later on.
Alright.
So, here's our choices of the teams, and then just some simple left and mid here to plot out the teams in this schedule.
Alright, so, every time I press F9, I get that different schedule there.
But how do I evaluate whether that's good?
I have to come down to something that I'm going to call a "Measure of Closeness", alright?
A single number that I can just keep my eye on to know whether this schedule is a good schedule or a bad schedule.
Aright?
And going back to L's goal, the goal is that every single matchup happens exactly twice, or at least twice.
Alright?
So I listed all the possible matchups here-- Team 1 swims against Team 2, Team 1 against 3, and there's 28 possible matchups, ways to combine those eight numbers in pairs, and I'm always going from the lower number to the higher number.
Alright.
I said, Well, what I want to do is I want to measure, out of this schedule, how many times each matchup occurs.
And that was a bunch of formulas out here-- 8 different sets of formulas, it's not bad, it didn't take long to build this-- and once I have them match up.
So, 1-2, 1-3, 1-6; We also get matchups like, 6-8 and further on down a matchup like 8-3.
I need to rearrange that, so the smaller number's first.
So I just broke it into the left digit and the right digit using the left and right, and then ask for the min of these two and the max of these two, and then rearranged it back into min number - max number.
Once I had this column over here-- call them X-- then I use COUNTIF to count how many times that matchup occurred.
So, in this case, match up 1-2 occurred 4 times-- that's my COUNTIF.
And then we want to know if it's 2 or more, right?
So it's just simple IF the resulting column N is greater than 2, then we get a 1, otherwise a 0-- IF(N4>=2,1,0)-- and this number right here sums that up.
So, as I'm pressing F9-- wow, that's a really good one right there, 25 is close to the possible number of 28-- so I'm pressing F9 until I get a nice high number.
Alright, now, that's the "Measure of Closeness"-- I added a secondary level closeness because I realize that's going to have a lot of ties, and what the goal here is, is that every team plays every other team twice.
But just the way that this is going to to work out, I realized that there are actually 76 matchups that are going to happen.
Alright?
Our goal is 56.
Alright.
There's 76, that means there's only 20 cases where a team plays a team a third time, alright?
And I didn't want to have anyone who played the same team 4 times.
I accept the fact that sometimes teams are not playing each other 3 times, but 4 times was a bad thing.
So in this case, I'm trying to maximize this number, I'm trying to minimize this number that's measuring to see how many times a team got stuck playing 4 or more teams.
Alright.
And then also, here, just what's the most number of times that any two teams have played?
Alright.
And I'm trying to find a nice easy solution that's going to get me close to 28 and minimize this number.
In a perfect world, 28 and 0 is really what I want.
Alright.
Now, the next thing we have to figure out is just, what is-- how many possible permutations there are, right?
And so, we have 8 numbers, 1 to 35.
If I take the number 35, raised to the 8th power, it is this huge number-- 2.2 trillion possibilities.
There's no way that this PC is gonna be able to run through all 2.2 trillion possibilities.
If you had 8,000 possibilities, absolutely you could do it-- and I've done that, that's a video for another day.
But today we have this insane one.
So it's not a matter of looping through all 2.2 trillion possibilities; really, what we're going to do is we're just going to keep pressing F9 and keep our eye on this number right here-- that number is the key number.
I'm looking to try and get that to 28.
Alright.
So, I keep pressing F9 and I press F9 a few times at first just to see what the usual range is.
Yeah, so, we're getting 21s, 26s-- I guess if I get up above 25 I'll say that that one is close.
I just missed it, shoot!
Alright.
So, there, that's a good one.
Now, what I need to do now, now that I have something that gets me close-- 25 teams have the required 2 matchups, and only 6 teams have 4 or more matchups-- what I'm going to do is, I'm going to copy these 8 numbers, plus these 3 numbers, and paste them over here as this is the best one I've found so far.
Alright.
And I wrote a little macro-- I'll put the macro down in the YouTube comments so you can see that this simple little macro that just notes those numbers, alright?
So, now, the best that I have so far is a 25.
I'm going to keep pressing F9 until I get something better.
26-- beautiful, let's save that one.
Alright.
So that's my best one so far.
So these numbers over here, that schedule will get me 26.
And I keep pressing F9, F9, F9, F9, but here's the thing-- it's going to take a long time to keep pressing F9, so the second macro is a macro that is going to press F9 and it's going to check this number to see if it's better than this number.
If it is, then it's going to log it; if it's a tie-- alright, so if I get another 26, but this number here is less-- then it's also going to log in.
Alright, so now, watch what happens when I click continue.
Excel is pressing F9, it's now pressed F9 7000 times, and it eventually found one with 28 matchups-- 28 out of 28 teams have met each other twice.
But we still have two times where a team has met 4 or more times.
I accept that some teams are going to have to meet 3 times, but I'm trying to get to the point where one team will meet-- no teams will have to see each other more than 3 times.
And what I'm doing here is, I'm now logging anytime that there's a tie, alright?
And so, at first, we were getting hits really fast, was getting better and better and better; but now we're getting down to the tough part.
Alright?
And even though we've looked at 42,000 so far, out of the 2.2 trillion possibilities this is the number of percentages that we've checked.
Alright?
A very, very small, small number.
Alright, now, we're going to talk about a sidebar here.
Alright.
At first, when I started running this, I was really fascinated watching the iterations change.
It was fun to watch the iterations roll by.
But you eventually realize that you might have to test a million of these to get it to win.
Alright?
And so what I did is, I turned off Applications Screen Updating (Application.ScreenUpdating = False), I said only update this every thousand F9 prsses-- that way I know that the computer hasn't crashed, Excel hasn't locked up.
And it's still going, but it wouldn't redraw.
I was turning Application.ScreenUpdating off, on, back to off, and it wouldn't redraw.
So I learned that what I had to do was I had to move the cell pointer.
Alright.
This never used to happen, but for some reason now, in Excel 2016, they're making me move the cell pointer.
So, every thousand I select Column T, and then the next thousand I select the best entry.
That way, I can keep my eye on the top-- how many we've done-- and also on the bottom-- what we've found so far.
Alright.
So, I'm going to pause this, and we'll let this continue to run.
Hey, hey, success-- 28 of the possible matchups have 2 and no one has to play anyone 4 times.
The most anyone has to play anyone else is 3 times.
How long did this take?
Well, this took a half an hour-- 3 million presses of the F9 key.
So, I stand by my original answer: Press F9 faster.
Alright, so now that we have a winner, here's what we do: I'm going to keep my model running, and I'm going to copy this off, and I'm going to call this 1Gala-- that's what they call these matches-- withFour (1GalawithFour) and we'll take that answer that we found-- the best answer-- down here, these eight cells, Ctrl+C to copy, and paste here over the random numbers, so this becomes the answer-- this becomes the schedule that we're going to do.
Now, just for some interesting choices here, if there were two venues that had 4, that one actually got solved in 702,000 instead of 3 million; 3 Galas with 4 was solved in 334,355; 4 Galas with 4 was solved in 3.7 million.
So, it's just, how long does it take until you find the right one?
That randomly worked.
And again, even though I've looked at 3 million out of the 2.2 trillion possibilities, it is-- tenths, hundredths, thousandths-- one-ten-thousandth of a percent of the total possibilities.
I've just barely scratched the surface of the possibilities.
I suspect that if I try and get to the point, the optimal solution here would be where we had two of the meets with 5 lanes, and everyone else with 4 lanes.
I'm going to guess that that one-- the perfect solution-- it would take days to run.
Now, hey, look, just a little bit about those two macros: The first macro-- the save this one-- where we find the next row is equal to-- go to the last cell in Z, press the Ctrl+Up Arrow key, figure out where we are + 1, nd then right out to that row number, the 8 input values and then the 3 output values.
Alright?
So that's the tiny little macro.
And then the macro that actually runs this whole thing, it will, again, look for the the next row, and that ActiveSheet.Calculate, that's the line that is equivalent to pressing F9.
And then, just some checks to see if the value that we got after pressing Calculate is better than the best value, or if it's a tie, if at least the secondary criteria got better-- if it is, then log it, that's the code from below.
This whole thing here that, just to have the screen update every thousand or so, a lot of extra code there.
And then, finally, checking to see if we hit the 28 and 0 mark to finally stop.
So I'll put that-- I'll link to that code down in the YouTube comments.
Well, I spent a lot of my early career, when I was really using Excel every day, day after day, running VBA macros.
So I have five editions of this book, Excel 2016 VBA Macros, Tracy Syrstad and myself have this book.
It will bring you up the Excel learning curve, so you can do crazy things like press the F9 key 3 million times.
Press that "I" on the top right-hand corner to check that out.
Wrap-up for today's really long Episode: We have a situation where we need to press F9 many times.
Now my tip here is to customize your model so it results in a one number "Measure of Closeness".
That way you can keep your eye on that number, keep pressing F9, and when you get a better answer, save that answer, keep pressing F9 until you get close enough, and then use the VBA macro to automate that process.
Wow.
Hey, I want to thank L for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
You know, this is one of those, I don't know what the title should be.
It might be, "Guess Until Right", or "Brute Force Solving", or "Measure of Closeness".
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by L, the Commissioner of the Swim League.
This year, they have eight teams in the Swim League.
Each team hosts one meet, and it's the home team.
And then, each meet will have 4 or 5 teams, so the home team plus another 3, or the home team plus another 4.
Why is this?
Well, 5 because you need to have some meets to have 5 in order to solve the problem, and 4 because some pools only have 4 lanes, so they're constrained by that.
And L asks how to arrange the schedule so every team swims against every other team twice.
That's our goal.
And he noted, in the past.
when they had 5, 6, or 7 teams, he would just keep pressing F9 until he was close.
But this year, with 8 teams, it's not coming out.
Alright, L, here's my answer: Press F9 faster.
But then I added a note that said, "Your life is going to be easier if you can reduce the model down to a single number "Measure of Closeness".
At which point, L replied and said, "Which video explains "Measure of Closeness"?
So that's when I realized I was going to have to dive in and learn how to become the Scheduling Commissioner of a Swim League.
Alright, here's my workbook.
I started out with the 8 home teams, 1 through 8.
Everyone hosts one home match and they're going to have either 3 or 4 other competitors, depending on how many lanes they have.
Alright?
So I'm assuming right here that we have-- seven of the venues have-- 5 or more lanes, and 1 venue has 4 lanes.
And then my question was, well, how many ways to arrange the competitors?
Alright?
So, if the home team is 1, you could do something like 2345, and then 2346, 2347, 2348, and actually listed all those down here, right?
And it turned out that there are 35 different ways to have 5 competitors, if you know that a home team is 1; and then 35 ways for a home team 2; and it was 35 ways for each.
And what was really interesting to me is, even in the situations where you only have 4 lanes, this one out here it also is 35 possible competitors, which I don't quite understand how that works out.
But it does.
It's really nice that there's 35 choices in each case, alright?
So we're going to set up a random model here, where we're going to choose a random number between 1 and 35.
Alright?
And based on that random number between 1 and 35, we go and grab the value from one of the possible 35 solutions-- or in this case, one of the possible 35 solutions further down.
Alright.
So, it's grabbing from the green section.
And what I'm doing here-- just to make my math work out-- is the last team is considered to be Team 9, which there isn't a team 9-- we're just going to ignore team 9s later on.
Alright.
So, here's our choices of the teams, and then just some simple left and mid here to plot out the teams in this schedule.
Alright, so, every time I press F9, I get that different schedule there.
But how do I evaluate whether that's good?
I have to come down to something that I'm going to call a "Measure of Closeness", alright?
A single number that I can just keep my eye on to know whether this schedule is a good schedule or a bad schedule.
Aright?
And going back to L's goal, the goal is that every single matchup happens exactly twice, or at least twice.
Alright?
So I listed all the possible matchups here-- Team 1 swims against Team 2, Team 1 against 3, and there's 28 possible matchups, ways to combine those eight numbers in pairs, and I'm always going from the lower number to the higher number.
Alright.
I said, Well, what I want to do is I want to measure, out of this schedule, how many times each matchup occurs.
And that was a bunch of formulas out here-- 8 different sets of formulas, it's not bad, it didn't take long to build this-- and once I have them match up.
So, 1-2, 1-3, 1-6; We also get matchups like, 6-8 and further on down a matchup like 8-3.
I need to rearrange that, so the smaller number's first.
So I just broke it into the left digit and the right digit using the left and right, and then ask for the min of these two and the max of these two, and then rearranged it back into min number - max number.
Once I had this column over here-- call them X-- then I use COUNTIF to count how many times that matchup occurred.
So, in this case, match up 1-2 occurred 4 times-- that's my COUNTIF.
And then we want to know if it's 2 or more, right?
So it's just simple IF the resulting column N is greater than 2, then we get a 1, otherwise a 0-- IF(N4>=2,1,0)-- and this number right here sums that up.
So, as I'm pressing F9-- wow, that's a really good one right there, 25 is close to the possible number of 28-- so I'm pressing F9 until I get a nice high number.
Alright, now, that's the "Measure of Closeness"-- I added a secondary level closeness because I realize that's going to have a lot of ties, and what the goal here is, is that every team plays every other team twice.
But just the way that this is going to to work out, I realized that there are actually 76 matchups that are going to happen.
Alright?
Our goal is 56.
Alright.
There's 76, that means there's only 20 cases where a team plays a team a third time, alright?
And I didn't want to have anyone who played the same team 4 times.
I accept the fact that sometimes teams are not playing each other 3 times, but 4 times was a bad thing.
So in this case, I'm trying to maximize this number, I'm trying to minimize this number that's measuring to see how many times a team got stuck playing 4 or more teams.
Alright.
And then also, here, just what's the most number of times that any two teams have played?
Alright.
And I'm trying to find a nice easy solution that's going to get me close to 28 and minimize this number.
In a perfect world, 28 and 0 is really what I want.
Alright.
Now, the next thing we have to figure out is just, what is-- how many possible permutations there are, right?
And so, we have 8 numbers, 1 to 35.
If I take the number 35, raised to the 8th power, it is this huge number-- 2.2 trillion possibilities.
There's no way that this PC is gonna be able to run through all 2.2 trillion possibilities.
If you had 8,000 possibilities, absolutely you could do it-- and I've done that, that's a video for another day.
But today we have this insane one.
So it's not a matter of looping through all 2.2 trillion possibilities; really, what we're going to do is we're just going to keep pressing F9 and keep our eye on this number right here-- that number is the key number.
I'm looking to try and get that to 28.
Alright.
So, I keep pressing F9 and I press F9 a few times at first just to see what the usual range is.
Yeah, so, we're getting 21s, 26s-- I guess if I get up above 25 I'll say that that one is close.
I just missed it, shoot!
Alright.
So, there, that's a good one.
Now, what I need to do now, now that I have something that gets me close-- 25 teams have the required 2 matchups, and only 6 teams have 4 or more matchups-- what I'm going to do is, I'm going to copy these 8 numbers, plus these 3 numbers, and paste them over here as this is the best one I've found so far.
Alright.
And I wrote a little macro-- I'll put the macro down in the YouTube comments so you can see that this simple little macro that just notes those numbers, alright?
So, now, the best that I have so far is a 25.
I'm going to keep pressing F9 until I get something better.
26-- beautiful, let's save that one.
Alright.
So that's my best one so far.
So these numbers over here, that schedule will get me 26.
And I keep pressing F9, F9, F9, F9, but here's the thing-- it's going to take a long time to keep pressing F9, so the second macro is a macro that is going to press F9 and it's going to check this number to see if it's better than this number.
If it is, then it's going to log it; if it's a tie-- alright, so if I get another 26, but this number here is less-- then it's also going to log in.
Alright, so now, watch what happens when I click continue.
Excel is pressing F9, it's now pressed F9 7000 times, and it eventually found one with 28 matchups-- 28 out of 28 teams have met each other twice.
But we still have two times where a team has met 4 or more times.
I accept that some teams are going to have to meet 3 times, but I'm trying to get to the point where one team will meet-- no teams will have to see each other more than 3 times.
And what I'm doing here is, I'm now logging anytime that there's a tie, alright?
And so, at first, we were getting hits really fast, was getting better and better and better; but now we're getting down to the tough part.
Alright?
And even though we've looked at 42,000 so far, out of the 2.2 trillion possibilities this is the number of percentages that we've checked.
Alright?
A very, very small, small number.
Alright, now, we're going to talk about a sidebar here.
Alright.
At first, when I started running this, I was really fascinated watching the iterations change.
It was fun to watch the iterations roll by.
But you eventually realize that you might have to test a million of these to get it to win.
Alright?
And so what I did is, I turned off Applications Screen Updating (Application.ScreenUpdating = False), I said only update this every thousand F9 prsses-- that way I know that the computer hasn't crashed, Excel hasn't locked up.
And it's still going, but it wouldn't redraw.
I was turning Application.ScreenUpdating off, on, back to off, and it wouldn't redraw.
So I learned that what I had to do was I had to move the cell pointer.
Alright.
This never used to happen, but for some reason now, in Excel 2016, they're making me move the cell pointer.
So, every thousand I select Column T, and then the next thousand I select the best entry.
That way, I can keep my eye on the top-- how many we've done-- and also on the bottom-- what we've found so far.
Alright.
So, I'm going to pause this, and we'll let this continue to run.
Hey, hey, success-- 28 of the possible matchups have 2 and no one has to play anyone 4 times.
The most anyone has to play anyone else is 3 times.
How long did this take?
Well, this took a half an hour-- 3 million presses of the F9 key.
So, I stand by my original answer: Press F9 faster.
Alright, so now that we have a winner, here's what we do: I'm going to keep my model running, and I'm going to copy this off, and I'm going to call this 1Gala-- that's what they call these matches-- withFour (1GalawithFour) and we'll take that answer that we found-- the best answer-- down here, these eight cells, Ctrl+C to copy, and paste here over the random numbers, so this becomes the answer-- this becomes the schedule that we're going to do.
Now, just for some interesting choices here, if there were two venues that had 4, that one actually got solved in 702,000 instead of 3 million; 3 Galas with 4 was solved in 334,355; 4 Galas with 4 was solved in 3.7 million.
So, it's just, how long does it take until you find the right one?
That randomly worked.
And again, even though I've looked at 3 million out of the 2.2 trillion possibilities, it is-- tenths, hundredths, thousandths-- one-ten-thousandth of a percent of the total possibilities.
I've just barely scratched the surface of the possibilities.
I suspect that if I try and get to the point, the optimal solution here would be where we had two of the meets with 5 lanes, and everyone else with 4 lanes.
I'm going to guess that that one-- the perfect solution-- it would take days to run.
Now, hey, look, just a little bit about those two macros: The first macro-- the save this one-- where we find the next row is equal to-- go to the last cell in Z, press the Ctrl+Up Arrow key, figure out where we are + 1, nd then right out to that row number, the 8 input values and then the 3 output values.
Alright?
So that's the tiny little macro.
And then the macro that actually runs this whole thing, it will, again, look for the the next row, and that ActiveSheet.Calculate, that's the line that is equivalent to pressing F9.
And then, just some checks to see if the value that we got after pressing Calculate is better than the best value, or if it's a tie, if at least the secondary criteria got better-- if it is, then log it, that's the code from below.
This whole thing here that, just to have the screen update every thousand or so, a lot of extra code there.
And then, finally, checking to see if we hit the 28 and 0 mark to finally stop.
So I'll put that-- I'll link to that code down in the YouTube comments.
Well, I spent a lot of my early career, when I was really using Excel every day, day after day, running VBA macros.
So I have five editions of this book, Excel 2016 VBA Macros, Tracy Syrstad and myself have this book.
It will bring you up the Excel learning curve, so you can do crazy things like press the F9 key 3 million times.
Press that "I" on the top right-hand corner to check that out.
Wrap-up for today's really long Episode: We have a situation where we need to press F9 many times.
Now my tip here is to customize your model so it results in a one number "Measure of Closeness".
That way you can keep your eye on that number, keep pressing F9, and when you get a better answer, save that answer, keep pressing F9 until you get close enough, and then use the VBA macro to automate that process.
Wow.
Hey, I want to thank L for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
