Jim sends in today's question. Jim is a fan of an iPad game called Dots. You have a 6x6 grid of dots that appear in four colors. You are trying to find the longest connection of dots of the same color. Jim wondered if Excel could generate a random 6x6 grid of four colors of dots to look for patterns.
In today's episode, see how to use RANDBETWEEN, conditional formatting and a custom number format to generate the grid of dots. But finding patterns is going to be tougher. While the human brain can detect the patterns, a formula in Excel to find the longest connection would be tougher. The video ends with some conditional formatting to highlight the possible connecting cells but no method for finding the longest connection.
In today's episode, see how to use RANDBETWEEN, conditional formatting and a custom number format to generate the grid of dots. But finding patterns is going to be tougher. While the human brain can detect the patterns, a formula in Excel to find the longest connection would be tougher. The video ends with some conditional formatting to highlight the possible connecting cells but no method for finding the longest connection.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1739 - Dots!
On today's question is sent in by Jim. Jim is playing a game on the iPad called Dots, and the way the Dots works is, you get a 6x6 grid for different colored dots, and you're looking for patterns. And Jim wanted to know if there was way in Excel to generate the 6x6 grid of the four different color dots.
And so I downloaded Dots to the iPhone and, you know, checked it out. And what you're trying to do with dots is, you're looking for connecting cells. So for example in the Dots game, you might draw a line from here to here, so we're looking for colors that are the same. And at this point I could either go down to this one, or I could have gotten left, alright, there's no diagonals. And once I choose this, these 5 dots would disappear.
And then Tetris like, everything else would fall down, new dots would appear, and you're trying to get as many dots connected in 60 seconds as possible. So right now, I've got the 6x6 grid working, let's talk about how to do that.
So I'm going to go on to the next sheet, we're going to choose a 6x6 range, =RANDBETWEEN(1,4), RANDBETWEEN gives us a integer between 1-4.
So that gets us to the numbers 1-4, every time we press F9, we get a new set of numbers, simple enough, right? We're going to do Alt O D for Conditional Formatting, New Rule.
If the cell value is equal to 1, then we're going to choose a font color. So we'll just choose the theme colors here, I'll go red for 1, and then OK. And we'll do the same thing, New Rule, cells that contain equal to 2, Format, font color green, alright. So I paused the recording there, but I did the same thing to set up the rules for 3 and 4, click OK, and now the font color is automatically changing. Let's set the column width, and let's just see here, this row 1 is a height of 15 (20 pixels). So let's make column A also have 20 pixels, nice square. So that's 2,14, Alt O C W, 2.14. So we have square cells now, and we'll Center those. Now how do we get the dots to appear instead of the numbers?
Well first let's see if we can find the dots.
We'll come out here to Insert, Symbol, and in my Recently used symbols I have a nice black circle, the bullet character there.
Click Close, and I will copy that to the clipboard, Ctrl+C. Alright, now here, let's go and select our range of cells, Ctrl+1, the number format.
No, we're not going to do that, we're going to go to a custom and say "Hey, no matter what number shows up there", in quotes, I'm going to Ctrl+V, put that dot character, click OK. Alright, so now we have the grid that's working.
Alright, but I assume that Jim is probably trying to generate this. That way he can kind of use Excel to help spot the trends or something like that, or spot the connections. And so I set up 4 new rules for this range, yeah.
Use a formula to determine which cells to highlight, and all of these formulas are referring to B2, the top left hand corner cell in the selection. If it's equal to, that's not right, if it's =B1. Alright so if, from this cell, if any of the adjacent vertical or horizontal cells are the exact same, then color it in gray. This way I know, don't even look at these, the ones that aren't gray, the obvious choices are the ones that have the gray. But even there, it's still not going to help, because you need to be able to find the longest string. And so for example here, the longest one might be this particular range here, 1-2-3-4-5-6 would be the longest, and being able to detect the longest, that's one that seems a little bit tough. So now Jim, we have your question answered, we have the grid working, we can refresh the grid with F9. But as far as having this help you with the Dots game to look for the longest particular chain, that is going to be a much tougher problem.
Alright hey, I want to thank you for stopping by, thank Jim for sending that question in, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1739 - Dots!
On today's question is sent in by Jim. Jim is playing a game on the iPad called Dots, and the way the Dots works is, you get a 6x6 grid for different colored dots, and you're looking for patterns. And Jim wanted to know if there was way in Excel to generate the 6x6 grid of the four different color dots.
And so I downloaded Dots to the iPhone and, you know, checked it out. And what you're trying to do with dots is, you're looking for connecting cells. So for example in the Dots game, you might draw a line from here to here, so we're looking for colors that are the same. And at this point I could either go down to this one, or I could have gotten left, alright, there's no diagonals. And once I choose this, these 5 dots would disappear.
And then Tetris like, everything else would fall down, new dots would appear, and you're trying to get as many dots connected in 60 seconds as possible. So right now, I've got the 6x6 grid working, let's talk about how to do that.
So I'm going to go on to the next sheet, we're going to choose a 6x6 range, =RANDBETWEEN(1,4), RANDBETWEEN gives us a integer between 1-4.
So that gets us to the numbers 1-4, every time we press F9, we get a new set of numbers, simple enough, right? We're going to do Alt O D for Conditional Formatting, New Rule.
If the cell value is equal to 1, then we're going to choose a font color. So we'll just choose the theme colors here, I'll go red for 1, and then OK. And we'll do the same thing, New Rule, cells that contain equal to 2, Format, font color green, alright. So I paused the recording there, but I did the same thing to set up the rules for 3 and 4, click OK, and now the font color is automatically changing. Let's set the column width, and let's just see here, this row 1 is a height of 15 (20 pixels). So let's make column A also have 20 pixels, nice square. So that's 2,14, Alt O C W, 2.14. So we have square cells now, and we'll Center those. Now how do we get the dots to appear instead of the numbers?
Well first let's see if we can find the dots.
We'll come out here to Insert, Symbol, and in my Recently used symbols I have a nice black circle, the bullet character there.
Click Close, and I will copy that to the clipboard, Ctrl+C. Alright, now here, let's go and select our range of cells, Ctrl+1, the number format.
No, we're not going to do that, we're going to go to a custom and say "Hey, no matter what number shows up there", in quotes, I'm going to Ctrl+V, put that dot character, click OK. Alright, so now we have the grid that's working.
Alright, but I assume that Jim is probably trying to generate this. That way he can kind of use Excel to help spot the trends or something like that, or spot the connections. And so I set up 4 new rules for this range, yeah.
Use a formula to determine which cells to highlight, and all of these formulas are referring to B2, the top left hand corner cell in the selection. If it's equal to, that's not right, if it's =B1. Alright so if, from this cell, if any of the adjacent vertical or horizontal cells are the exact same, then color it in gray. This way I know, don't even look at these, the ones that aren't gray, the obvious choices are the ones that have the gray. But even there, it's still not going to help, because you need to be able to find the longest string. And so for example here, the longest one might be this particular range here, 1-2-3-4-5-6 would be the longest, and being able to detect the longest, that's one that seems a little bit tough. So now Jim, we have your question answered, we have the grid working, we can refresh the grid with F9. But as far as having this help you with the Dots game to look for the longest particular chain, that is going to be a much tougher problem.
Alright hey, I want to thank you for stopping by, thank Jim for sending that question in, we'll see you next time for another netcast from MrExcel!
