My wife brought home a book of logic problems recently. It’s been years since I did one of these puzzles, but I think they’re fun. These are the kind where you set up a grid, like this
Then you get a series of clues to help you identify matches. The puzzle part is that you use the process of elimination to deduce relationships that aren’t explicitly given in the clues. The only thing more fun than solving these puzzles is creating a spreadsheet that makes solving them less fun. Wait, what was that again?
The above example comes from an puzzle at puzzles.com.
This spreadsheet fills in X’s when it can be determined that a relationship doesn’t exist. For instance, when you place an asterisks to indicate that Allan’s color is Brown and you’ve already determined that Allan’s month isn’t March, the spreadsheet will fill in an X to indicate that Brown’s month isn’t March. It doesn’t, however, fill in asterisks. But when you see a row or column with only one blank cell, you know what goes there.
It is setup to help solve puzzles that have four sets of five objects. Other puzzles have more or less, but this isn’t customizable for them. You could do less, you just have to fill in a bunch of X’s for the rows and columns that aren’t applicable.
It only fills in X’s when you enter an asterisk. So if you place an X in a row or column that already has an asterisk, you have to re-enter the asterisk to make sure everything’s up to date. After about the third clue, it usually isn’t necessary to do this because there’s so much redundancy in where it places X’s. I was going to have it update for placement of X’s as well as asterisks, but the latter took enough of my time that it wasn’t worth it. But if you want to know why I didn’t disable events, that’s why.
I wanted to be able to go through the clues one time and solve the puzzle. This is possible for some puzzles, but puzzles that have relative clues, like sooner/later, higher/lower, and the like, usually require a second pass.
I really had to brute force finding where the X’s go. I tried to find a cleaner algorithm, but nothing worked. So I’m looping through all the possible cells each time an asterisk is entered. It’s very fast on my machine, but your mileage may vary.
You can download LogicPuzzles.zip, which contains one xlsm file. Sorry, I don’t have a 2003 version.
This is actually pretty cool. You’re right though – using this spreadsheet turns a fun cerebral activity into an administrative activity.
I love these puzzles. They’re like suduko without the numbers. Thanks for the link!
Nice macros. I’ld love to use this to solve a bigger logic puzzle – one with 6 classes and 5 options for each.
I’d really appreciate any tips you can give an amateur on how to adapt it.
Cheers!
You might want to try my downloadable Logic Puzzle Solver at http://www.logicpuzzlesolver.com. It’s not an Excel application, but written in Free Basic. It has a clunky, DOS-like feel to it, some may call it “retro”! However it can solve virtually all sizes of logic problems (only constrained by the screen size). Thanks.
Anyone know of a program (Excel or otherwise :) that does the opposite? That is, helps you create this kind of problem. Maybe it would allow you to set up a grid and then delete information until the program tells you the puzzle is unsolvable – and even better how to write the clues?
Ive got a grand daughter in second grade whose favorite subject is math and I’d like to expand what ‘math’ is to her to include logic and logical thinking…
How lovely the way you build up this spreadsheet!
I hope someday I reach this leval of understanding Excel and its VBA macros!
Congrats!
=)
Is there a way to add more rows and columns?
No, it’s fixed at four groups of five members.
Hi – I’ve just used your 2010 blog on logic problems as inspiration for one in a newsletter I just sent out – try it using the website link below if you like! I’d love to include a discussion of your macros and functions for this in next month’s issue, showing people how to build their own logic puzzle in Excel, along with things from your world keyboard shortcut day blogs (if this doesn’t exist, it should). I would obviously include links back to your site. Is this something you’d allow? You look like someone after our own heart (if you doubt this, check out Flappy Owl in VBA at https://www.wiseowl.co.uk/blog/s398/flappy-bird-excel-vba-index.htm). Andy
Andy: I’m a subscriber to your Youtube channel and a fan of your videos. You are welcome to use my code in a newsletter with proper attribution.
Many thanks! If you sign up for our website, you’ll get the chance to get our newsletter once a month – it’s probably worth it. You’ll also see how I’ve used anything from your site (with attribution, definitely). I was very impressed with the functions behind the logic puzzle grid! It’s the other Andrew who did the VBA videos (I did the SSIS ones), but I know VBA pretty well too.
I’ve just published a long blog on creating a word search, which has a link to your logic puzzle page at the top. Might be of interest to your readers? It shows not only how to use the word search, but how I went about building the system.