Microsoft Excel Tutorial: Generating all combinations of N outcomes for K games.
In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted "binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5" method. This involves a lot of typing and two different formulas.
Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of 1. And then at the end, he uses +1111 to convert the 0 to 5 to 1 to 6.
In this video, I show Kyle's method for the Jeopardy Masters problem and then I generalize the steps for any values of N and K.
To download this workbook: Excel All Combinations One To Six in Four Columns - Episode 2604 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Problem Statement
(0:24) Bill's Convoluted Method
(0:43) Kyle's Formula for Combinations using BASE
(1:00) Start the SEQUENCE at 0
(1:12) BASE function in Excel
(1:32) Clever: Add 1111
(2:02) One formula
(2:13) General steps for any N and K
(3:30) Break with MID and SEQUENCE?
(4:00) Break with TEXT and TEXTSPLIT?
(4:55) Thanks to Kyle
In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted "binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5" method. This involves a lot of typing and two different formulas.
Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of 1. And then at the end, he uses +1111 to convert the 0 to 5 to 1 to 6.
In this video, I show Kyle's method for the Jeopardy Masters problem and then I generalize the steps for any values of N and K.
To download this workbook: Excel All Combinations One To Six in Four Columns - Episode 2604 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Problem Statement
(0:24) Bill's Convoluted Method
(0:43) Kyle's Formula for Combinations using BASE
(1:00) Start the SEQUENCE at 0
(1:12) BASE function in Excel
(1:32) Clever: Add 1111
(2:02) One formula
(2:13) General steps for any N and K
(3:30) Break with MID and SEQUENCE?
(4:00) Break with TEXT and TEXTSPLIT?
(4:55) Thanks to Kyle
Transcript of the video:
Hey, welcome back to MrExcel NetCast. I'm Bill Jelen.
A couple days ago I was solving the “Jeopardy Masters - What are the odds of a tie?”.
And I had to do all combinations.
That did just one to six, six different scenarios for four different games.
And I did this horrible formula for counting up in binary, but it wasn't binary.
It was sort of Base 6, but not really Base 6 because it went from one to six instead of zero to five.
Yeah, I use this method a lot. I'm not proud of this method.
Just type the numbers one to six here, and then grab the number from six rows above, copy that down.
And then the formula here, checking to see if the number to the right of us is a one. The number above us is a six.
If it is, then add one to the number above us, unless it's already a six, then reset back to one.
And thankfully, Kyle Freistedt set me again the easy steps to do this.
He did this the last time when I was doing the World Cup.
And honestly, yesterday I knew there was an easier way to do it.
I just couldn't remember what it was. I'm going to document it and try and learn it.
The first thing we do is we're going to do 1,296 numbers, but not starting from one.
We're going to start from zero like that.
We get the number zero to 1,295. That's our 1,296 numbers.
And then Kyle uses equal BASE, and we're going to convert that to base six.
Oh-Six-Hash we'll get all 1,296 numbers in Base 6 with a minimum length of four.
All right. And that generates almost what I need.
All right.
But what I need is instead of 0, 0, 0, 0, I need 1, 1, 1, 1.
Very cleverly, Kyle just adds, it's amazing that this works, +1111, like that. And we get exactly what I need.
Now it's all in one column, so I'm going to do some tricks to break it out into four columns.
But this is much easier than what I had done here with two different formulas and just a bunch of typing. It's one of those things I just have to learn.
And then of course, Kyle put it all in one formula like that.
We don't need the extra columns. It's just that formula.
For me, it's easier to explain it in the multiple columns.
All right. Here's the big test.
Let's do some other scenario.
Let's say that we have, I don't know, three columns and the number of scenarios is seven. All right.
The first thing to do is figure out how many rows we're going to need.
That's the number seven, raised to the third power, because for the first column, it could be the number one to seven, so there's seven possibilities.
And then for the second column, again, the numbers, so seven times seven times seven is seven raised to the third power. But I'm just trying to make this nice and general.
All right.
We know that we have 343, and so we're going to do a sequence of that number starting from zero.
We get the number zero to 342. All right.
And then, we're going to wrap that whole thing in the base function.
And for the radix, it is this number, this input cell here, seven.
And the minimum number of digits is the number of columns that we want at the end, so three.
Cool. Great.
And then from there, this great trick from Kyle, just add in this case +111 because it's only three digits.
Beautiful.
Now I'm starting to think about how I'm going to break that back out. It's funny.
TEXTSPLIT can break at a dash, but it can't say break it into every one character.
I'm thinking ahead here on the fly. Could we do equal MID of this.
Where we're going to start? We're going to start at the SEQUENCE of three.
One row, three columns for a length of one.
Yeah, that would work.
And then double click to copy that down, except double click doesn't work for these array formulas.
Okay. Let me go back to the original plan here.
Sorry, this is longer.
I'm going to wrap the whole thing in the TEXT function, and I'm going to add those hyphens in. 0-0-0, like that.
And then here, I should be able to do equal TEXTSPLIT of this text, split it at the dash.
Good.
And then can I do AB9#, or is that going to run into the array of array problems? Of course, it's the array of array problems.
All right. We have our formula there.
At this point, I guess I'm going to go back to the old formula.
Come down to the bottom. That was control-down arrow.
Move over to the right with the right arrow.
Control-shift up, and then Control-D is the fast way to copy that down.
And then of course, copy and paste as values and go on with the rest of yesterday's video. Thanks to Kyle for sending this in.
He sent it in last time when I did the World Cup, and I just didn't...
It's one of those things I saw it. I was like, "Oh, that's cool.
But then the next time I needed it, I could not remember it.
Me personally, I'll have to bookmark this video.
So the next time it comes up, I can come back and use this great little formula here.
Instead of the just convoluted way that I did it yesterday.
All right.
Hey, thanks to Kyle for sending that formula in a second time. Thanks to you for stopping by.
We'll see you next time for another NetCast for MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
A couple days ago I was solving the “Jeopardy Masters - What are the odds of a tie?”.
And I had to do all combinations.
That did just one to six, six different scenarios for four different games.
And I did this horrible formula for counting up in binary, but it wasn't binary.
It was sort of Base 6, but not really Base 6 because it went from one to six instead of zero to five.
Yeah, I use this method a lot. I'm not proud of this method.
Just type the numbers one to six here, and then grab the number from six rows above, copy that down.
And then the formula here, checking to see if the number to the right of us is a one. The number above us is a six.
If it is, then add one to the number above us, unless it's already a six, then reset back to one.
And thankfully, Kyle Freistedt set me again the easy steps to do this.
He did this the last time when I was doing the World Cup.
And honestly, yesterday I knew there was an easier way to do it.
I just couldn't remember what it was. I'm going to document it and try and learn it.
The first thing we do is we're going to do 1,296 numbers, but not starting from one.
We're going to start from zero like that.
We get the number zero to 1,295. That's our 1,296 numbers.
And then Kyle uses equal BASE, and we're going to convert that to base six.
Oh-Six-Hash we'll get all 1,296 numbers in Base 6 with a minimum length of four.
All right. And that generates almost what I need.
All right.
But what I need is instead of 0, 0, 0, 0, I need 1, 1, 1, 1.
Very cleverly, Kyle just adds, it's amazing that this works, +1111, like that. And we get exactly what I need.
Now it's all in one column, so I'm going to do some tricks to break it out into four columns.
But this is much easier than what I had done here with two different formulas and just a bunch of typing. It's one of those things I just have to learn.
And then of course, Kyle put it all in one formula like that.
We don't need the extra columns. It's just that formula.
For me, it's easier to explain it in the multiple columns.
All right. Here's the big test.
Let's do some other scenario.
Let's say that we have, I don't know, three columns and the number of scenarios is seven. All right.
The first thing to do is figure out how many rows we're going to need.
That's the number seven, raised to the third power, because for the first column, it could be the number one to seven, so there's seven possibilities.
And then for the second column, again, the numbers, so seven times seven times seven is seven raised to the third power. But I'm just trying to make this nice and general.
All right.
We know that we have 343, and so we're going to do a sequence of that number starting from zero.
We get the number zero to 342. All right.
And then, we're going to wrap that whole thing in the base function.
And for the radix, it is this number, this input cell here, seven.
And the minimum number of digits is the number of columns that we want at the end, so three.
Cool. Great.
And then from there, this great trick from Kyle, just add in this case +111 because it's only three digits.
Beautiful.
Now I'm starting to think about how I'm going to break that back out. It's funny.
TEXTSPLIT can break at a dash, but it can't say break it into every one character.
I'm thinking ahead here on the fly. Could we do equal MID of this.
Where we're going to start? We're going to start at the SEQUENCE of three.
One row, three columns for a length of one.
Yeah, that would work.
And then double click to copy that down, except double click doesn't work for these array formulas.
Okay. Let me go back to the original plan here.
Sorry, this is longer.
I'm going to wrap the whole thing in the TEXT function, and I'm going to add those hyphens in. 0-0-0, like that.
And then here, I should be able to do equal TEXTSPLIT of this text, split it at the dash.
Good.
And then can I do AB9#, or is that going to run into the array of array problems? Of course, it's the array of array problems.
All right. We have our formula there.
At this point, I guess I'm going to go back to the old formula.
Come down to the bottom. That was control-down arrow.
Move over to the right with the right arrow.
Control-shift up, and then Control-D is the fast way to copy that down.
And then of course, copy and paste as values and go on with the rest of yesterday's video. Thanks to Kyle for sending this in.
He sent it in last time when I did the World Cup, and I just didn't...
It's one of those things I saw it. I was like, "Oh, that's cool.
But then the next time I needed it, I could not remember it.
Me personally, I'll have to bookmark this video.
So the next time it comes up, I can come back and use this great little formula here.
Instead of the just convoluted way that I did it yesterday.
All right.
Hey, thanks to Kyle for sending that formula in a second time. Thanks to you for stopping by.
We'll see you next time for another NetCast for MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
