Magic CircleCreate Magic Circle

6-dimensional Magic Cube Something in Big5

To Create Magic Square By Excel

figure2: a 7 x 7 magic square having the total of each row, column or diagonal equal to 175.

A magic square with an odd number of cells on each side can be set up by Excel or any other spreadsheet.-"Fractals, Googols, and other Mathematical Tales" by Theoni Pappas (Wide World Publishing, 1993)

Illustration: In Excel, we are going to construct the above 7 x 7 magic square in cells B2:H8.

1. Put 1 in E2 (the middle cell of the top row).

2. Move up one row and to the right one column to F1. Because row 1 is outside the square, so move to the bottom of the square, i.e. cell F8 and put 2 there.

3. Move up one row and to the right one column to G7. Put 3 there. By the same token, put 4 in H6. The next number 5 would go in I5, but it is outside the square, so it goes to the left most, i.e. B5. Then, 6 is in C4; 7 is in D3. Next would be in E2, but E2 has already been occupied. Therefore, 8 goes directly below D3 (i.e. number 7) to D4. 9 goes in E3, and 10 goes in F2.

4. Following the same pattern, put:

11 in G8, 12 in H7, 13 in B6, 14 in C5, 15 in C6 (since D4 is filled),

16 in D5, 17 in E4, 18 in F3, 19 in G2, 20 in H8, 21 in B7, 22 in B8, 23 in C7,

24 in D6, 25 in E5, 26 in F4, 27 in G3, 28 in H2, 29 in H3 (since B8 is filled),

30 in B2, 31 in C8, 32 in D7, 33 in E6, 34 in F5, 35 in G4, 36 in G5 (since H3 is filled),

37 in H4, 38 in B3, 39 in C2, 40 in D8, 41 in E7, 42 in F6, 43 in F7 (since G5 is filled),

44 in G6, 45 in H5, 46 in B4, 47 in C3, 48 in D2, 49 in E8.

Now the magic square is filled.

To Create a 3X3 magic square:

1. Put 1 in E2 (the middle cell of the top row).

2. Move up one row and to the right one column to F1. Because row 1 is outside the square, so move to the bottom of the square, i.e. cell F4 and put 2 there.

3. Move up one row and to the right one column to G3, but it is outside the square, so 3 goes to the left most, i.e. D3. Then, 4 would be in E2, but E2 has already been occupied. Therefore, 4 goes directly below D3 (i.e. number 3) to D4. 5 goes in E3, and 6 goes in F2. 7 would be in G1, but it is outside the square, and the left most and bottom most are also outside the square. So, 7 goes below F2 (i.e. number 6) to F3.

4. Following the same pattern, put 8 in D2, and 9 in E4.

Now the 3X3 magic square is filled.

To Create a 5X5 magic square:

1. Put 1 in E2 (the middle cell of the top row).

2. Move up one row and to the right one column to F1. Because row 1 is outside the square, so move to the bottom of the square, i.e. cell F6 and put 2 there.

3. Move up one row and to the right one column to G5, put 3 there. Move up one row and to the right one column to H4, but it is outside the square, so 4 goes to the left most, i.e. C4. 5 goes to D3. Then, 6 would be in E2, but E2 has already been occupied. Therefore, 6 goes directly below D3 (i.e. number 5) to D4. 7 goes in E3, and 8 goes in F2. 9 would be in G1, but it is outside the square, so move it to the bottom of the square, i.e. cell G6.

4. Following the same pattern, put 10 in C5, 11 in C6, 12 in D5, 13 in E4, 14 in F3 and 15 in G2.

5. 16 would be in H1, but it is outside the square, and the left most and bottom most are also outside the square. So, 16 goes below G2 (i.e. number 15) to G3.

6. Following the same pattern, put 17 in C2, 18 in D6, 19 in E5, 20 in F4, 21 in F5, 22 in G4, 23 in C3, 24 in D2 and 25 in E6.

Now the 5X5 magic square is filled, and you can create another magic square with an odd number of cells on each side by the same method. It is true so far from 3-by-3 to 253-by-253 magic squares.

You can get a Excel Macro program written by Craig Stinson in PC Magazine Vol. 18 No. 3 February 9, 1999 P.223 to construct a magic square yourself. The following is his Macro program:

Sub Magic()
'Bail out if user hasn't selected a worksheet range
On Error GoTo Trap

Dim Origin As Object

'Turn off screen update to speed execution
Application.ScreenUpdating = False
StatusBarStatus = Application.DisplayStatusBar
Application.DisplayStatusBar = False

'Get dimensions and origin of selection
RowCount = Selection.Rows.Count
ColumnCount = Selection.Columns.Count
Set Origin = Selection.Cells(1, 1)

'Validate selection
If RowCount <> ColumnCount Then
MsgBox "Selection is not square!"
Exit Sub
End If
If RowCount Mod 2 = 0 Then
MsgBox "Square must have odd-numbered sides!"
Exit Sub
End If
If RowCount = 1 Then
MsgBox "Selection too small!"
Exit Sub
End If
If Origin.Row = 1 Or Origin.Offset(RowCount - 1, 0).Row = 65536 _
Or Origin.Offset(0, RowCount - 1).Column = 256 Then
MsgBox "Not enough space!"
Exit Sub
End If

'Is the selection blank? If not, OK to proceed?
Flag = 0
'Is the square empty?
For Each Cell In Selection.Cells
If Cell <> Empty Then
Flag = 1
Exit For
End If
Next Cell
'Is the row below empty?
If Flag = 0 Then
For i = 0 To RowCount - 1
If Origin.Offset(RowCount, i).Value <> Empty Then
Flag = 1
Exit For
End If
Next i
'Is the column to the right empty?
If Flag = 0 Then
For i = 0 To RowCount - 1
If Origin.Offset(i, RowCount).Value <> Empty Then
Flag = 1
Exit For
End If
Next i
'Is cell below and to right empty?
If Flag = 0 Then
If Origin.Offset(RowCount, RowCount).Value <> Empty Then
Flag = 1
End If
'Is cell above and to right empty?
If Flag = 0 Then
If Origin.Offset(-1, RowCount).Value <> Empty Then
Flag = 1
End If
End If
End If
End If
End If
'Warn user if we're going to overwrite data
If Flag = 1 Then
Answer = MsgBox _
("Macro will overwrite data! Proceed anyway?", vbYesNo, "Magic Squares")
If Answer = vbNo Then Exit Sub
'OK to proceed, so clear the selection
Selection.Clear
End If

'Populate square
'number of cells = side ^ 2
MaxVal = RowCount ^ 2

'Start in the middle of the top row
RowOffset = 0
ColumnOffset = RowCount \ 2
CurrentVal = 1
While CurrentVal <= MaxVal
Origin.Offset(RowOffset, ColumnOffset).Value = CurrentVal
CurrentVal = CurrentVal + 1
'Save this position, in case next cell is occupied
OldRowOff = RowOffset
OldColOff = ColumnOffset
'Move up and to the right
RowOffset = RowOffset - 1
ColumnOffset = ColumnOffset + 1
'If we're over the top, go to the bottom
If RowOffset = -1 Then
RowOffset = RowCount - 1
End If
'If we're beyond the right edge, go to the left edge
If ColumnOffset = RowCount Then
ColumnOffset = 0
End If
'If next cell is occupied, move down one in current column
If Origin.Offset(RowOffset, ColumnOffset).Value <> Empty Then
RowOffset = OldRowOff + 1
ColumnOffset = OldColOff
End If
'Allow other programs to run
Dummy = DoEvents
Wend

'Add a thick border around the magic square
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

'Calculate and display row sums
For i = 0 To RowCount - 1
Total = 0
For j = 0 To RowCount - 1
Total = Total + Origin.Offset(i, j).Value
Next j
Origin.Offset(i, RowCount).Value = Total
Next i

'Calculate and display column sums
For i = 0 To RowCount - 1
Total = 0
For j = 0 To RowCount - 1
Total = Total + Origin.Offset(j, i).Value
Next j
Origin.Offset(RowCount, i).Value = Total
Next i

'Calculate and display lower-right-corner diagonal sum
Total = 0
For i = 0 To RowCount - 1
Total = Total + Origin.Offset(i, i).Value
Next i
Origin.Offset(RowCount, RowCount).Value = Total

'Calculate and display upper-right-corner diagonal sum
Total = 0
For i = 0 To RowCount - 1
Total = Total + Origin.Offset(RowCount - i - 1, i)
Next i
Origin.Offset(-1, RowCount) = Total

'Restore user's status bar setting
Application.DisplayStatusBar = StatusBarStatus
Exit Sub

'We get here if the selection is not a worksheet range
Trap:
MsgBox "Please select a worksheet range!"

End Sub

To build the above Macro program:

1. Open your Excel;

2. Select Tools/Macro/Visual Basic Editor;

3. Double click Sheet1 (Sheet1) under VBA Project;

4. Copy the above Macro to the blank field on the right;

5. Save the file;

6. Close the Visual Basic worksheet and the Excel worksheet appears;

7. Highlight a square with an odd number of cells on each side, but remember to leave one more row at the top of the square;

8. Select Tools/Macro/Macro and a dialog box appears;

9. Select Run and a magic square with an odd number of cells on each side will appear.

Reference:

A reply letter about Magic Square by Craig Stinson - PC Magazine Vol. 18 No. 3 February 9, 1999 P.223

P.S. Craig Stinson's Excel Macro program is true up to the 253-by-253 magic square. It may not imply that the method to create a magic square is also limited to the 253-by-253 magic square. It may be due to the limitation of Excel itself. For more details about the limitations of Excel, please click HERE.

Do you want to know something about Magic Circle or Magic Polyhedron? If yes, please click .

[Recurring Pattern] [Relation with the Magic Square] [Create a Magic Square by Excel]

[Magic Circle = Magic Polyhedron ?] [Something Other Than Mathematics (in Big5)] [Home]