MrExcel.com - Your One Stop for Excel Tips & Solutions

Create a Magic Square for Any Number

September 15, 2004 - by

Many years ago, I worked at Telxon with Dave Strong. Dave had mentioned that his grandfather would perform a trick where he would ask someone in the room for any number. He would then think very hard and slowly but deliberately build a 4x4 matrix. Every row, column, and diagonal of numbers would add up to exactly the number chosen. This type of figure is called a magic square.

Dave's grandfather was Walter Wills Strong. He was with the YMCA in Europe during World War I and would amaze the troops with this mental trick. When Dave introduced the problem to me, I found a solution for creating the basic magic square with numbers 1 through 16 that adds to 34. However, Dave's grandfather was able to come up with a magic square for any number. Now, many years later, Dave has discovered the formula for how his grandfather did this trick. With a little practice, you can learn the trick yourself. This week's tip will talk about magic squares. It will show the process used by Dave's grandfather. It will also offer two Excel files. One Excel file rapidly calculates a magic square for any number. The other Excel file attempts to replicate the conjuring nature of the performance Dave's grandfather must have given, complete with a bearded wizard.

The Theory for a Basic Magic Square

A 4x4 matrix of numbers has two diagonals. In the image below, one diagonal is comprised of the 4 yellow squares. One diagonal is comprised of the 4 red squares. The remaining 8 edge cells are colored green.

To construct a Magic Square for 34, you simply write in the numbers from 1 through 16 in order. There is one easy twist. If you are about to write a number in a yellow or red square, you would have to write the number in the cell that is diagonally opposite from that square. For example, the 1 that would go in the upper left corner falls on a yellow square. The cell diagonally opposite this square is actually the 16th square, in the lower right corner. Instead of writing the 1 in the upper left square, write it in the lower right square.

The next two numbers, 2 and 3 would fall in green squares, so write them in their normal place. The number 4 would fall in a red square, so instead of writing it in the upper right corner, write the number 4 in the lower left corner.

The number 5 gets written in it's correct place. 6 & 7 need to move diagonally, and 8 gets written in it's correct place.

Continue this pattern for the numbers 9 through 16. You eventually end up with a simple magic square that adds up to 34 in all directions

An Interesting Twist

Dave's grandfather had a bit of a twist on this. For Dave's grandfather, he had the opposite rule. Anything that fell on a red or yellow square was written in the right place. Anything that fell on a green edge cell was written in the diagonally opposite square. His basic square would have looked like this one.

I suggest that you learn either of the two above patterns and stick with it. I will use the pattern where numbers in the red or yellow diagonals are written diagonally opposite their normal location.

Creating a Magic Square for Any Number

The secret employed by Dave's grandfather was to adjust his starting number. He used a calculation in his head to figure out a starting number other than 1. If you think about the math, every sum in the magic square is comprised of 4 cells. If you added one to every cell, the magic square would total 38, because all 4 cells would be incremented by 1. Here is a magic square created using the integers from 2 through 17 instead of 1 through 16. It totals to 38 instead of 34. All of the other logic remains the same.

The key to creating a magic square that adds up to any number is to vary the starting number. With a little bit of algebra, you can understand why the starting number follows this formula: 

[(Desired Number - 34) / 4 ] + 1

Here is the Excel workbook to create any desired Magic Square: AnyMagicSquare.xls.

Magic Square Genie

This workbook uses Excel VBA Macros. In order for the genie to work, you must allow macros to run when you open this workbook. To enable macros, follow these steps before downloading the workbook.

  • Open Excel
  • From the menu, select Tools > Macro > Security
  • Change the setting to Medium
  • Download and open the workbook
  • As the workbook is opening, you will be notified that macros are present. Choose to Enable.

I wrote this program to simulate the performance given by Dave's grandfather. Although not as impressive as someone doing the math in person with a pencil and a paper, it still gives you the idea of how the performance would go. Click on the Genie to start and he will ask you for a number. The Genie then thinks about the problem.

The genie slowly begins filling in numbers.

As rows are completed, the row and column totals light up to indicate that the rows are right.

Eventually, the genie gets the correct square and offers to do another.

Download a zipped version of Magic Square Genie.

A tip of the hat to Dave Strong and his grandfather Walter Wills Strong for passing on this technique.

To learn more about using VBA to automate Excel problems, check out VBA & Macros for Microsoft Excel, written by Bill Jelen and Tracy Syrstad.

Update from Dec 2005

Another Method Using Only Integers

In November 2005, Ray Battersby wrote that there must be a way to do a magic square for any number above 30 using only integers without decimals. Ray identified that you could add one to four specific cells in the matrix. In the Magic Square for 34, arrange the numbers in numerical order and take every other cell starting with the lowest. In the image below, Ray identified the cells that contain 1, 3, 5, and 7.

To change this to a magic square for 35, add one to each of the yellow cells.

To use Ray's method, subtract 30 from the desired result. Divide that number by 4. The integer portion becomes the starting digit and the remainder becomes the number that you add to the four yellow cells. For example, to create a magic square for 33:

  • 33-30 is 3
  • 3 divided by 4 is 0 with a remainder of 3
  • The starting number is 0 as shown as the Intermediate Result below
  • Add 3 to each of the yellow cells as shown in the Final Result below

As Ray notes, this means that some of the digits are repeated in the matrix.

Thanks to Ray for sharing this method.

Update from Jan 2008

Richard Letsinger wrote in to note that Ray’s method would work for any integer, positive or negative. The method is not just limited to integers above 30.