Magic Squares

Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
13,347
This is not a typical question thread, although I hope it may be of interest to those who play around with Excel for fun. It's somewhat in line with the "Tough Problem" series that ran a few years ago in this forum. People with spare time and interest might read on.

With the advent of the new array functions, I started thinking about some other existing array formulas and how they might change. One such formula is how to create a monthly calendar with one formula. My favorite version I got from Barry Houdini in a post on this forum, although I've seen several versions around the internet:

ABCDEFGH

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]

</tbody>
Calendar​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2:H7[/TH]
[TD="align: left"]{=IFERROR(DAY(TEXT(A1,"yyyy-mm-")&{2,3,4,5,6,7,8}+{0;7;14;21;28;35}-WEEKDAY(A1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Just put the first of the month in A1. I don't have the new functions yet, but my assumption is that you'd just need to enter the formula in B2 without CSE, and it would just spill into the other cells.


Another formula I came up with generates odd order magic squares.

ABCDEFGH

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>
SEQUENCE​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2:H8[/TH]
[TD="align: left"]{=MOD(TRANSPOSE(ROW(INDIRECT("1:"&A1))+INT(A1/2))+(ROW(INDIRECT("1:"&A1))-1),A1)*A1+MOD(TRANSPOSE(ROW(INDIRECT("1:"&A1))*2-1)+ROW(INDIRECT("1:"&A1))-1,A1)+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If you change A1 to 3 or 5 or 7, it will generate a magic square of that size. If you create a larger selection when entering the formula, it will create larger squares. This is sometimes called the Siamese method of creation.



I don't have the new array functions yet, but my assumption is that the ROW(INDIRECT structures could be replaced with SEQUENCE. So I wrote up a UDF that I believe imitates SEQUENCE and came up with this version:

ABCDEFGHI

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>
SEQUENCE​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2:I8[/TH]
[TD="align: left"]{=MOD(INT(SEQUENCE(A1,A1,(A1+1)/2,1+1/A1)),A1)*A1+MOD(INT(SEQUENCE(A1,A1,,2+1/A1)),A1)+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Much shorter. If someone has the functions and can test that, I'd be interested to see if it really works.

To keep this post from getting too long, I'll cut it short now. But there are all kinds of outstanding questions that follow from this:

1) The Wikipedia article on Siamese Method has an alternate method. It would be interesting if it's amenable to the same type of formula.
2) There's a method that generates magic squares for doubly-even orders (4k, or 4, 8, 12, 16, etc.) Different algorithm, but it's also workable in this type of formula.
3) Serious bonus points to anyone who can come up with a 4k+2 (6, 10, 14, etc.) method. I've seen at least 3 different methods, but they all seem a bit too tough for an array formula.
4) More of an Excel question, but what's the best way to get rid of the #N/As?
5) I'm more or less limiting this inquiry to formulas, since there are a lot of VBA solutions, including some on this board. But it might be interesting to see if anyone can come up with something succinct.

6) Does anyone have any other interesting array formulas, by which I mean a formula which actually generates a n X m array? Or an idea for one?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Re #4 , the only way I know of would be to add an Index function wrapper so that each cell attempts to return an individual value, so you can use IFERROR to handle the resulting error.
 
RoryA said:
Re #4 , the only way I know of would be to add an Index function wrapper so that each cell attempts to return an individual value, so you can use IFERROR to handle the resulting error.
Click to expand...

I managed to use Conditional Formatting using ISERROR and a font color matching the sheet. I also created a grid around it in a similar fashion.


For anyone wanting to take a shot at the first question, or to see how the existing formula works, put this formula in B10 and copy down and across:

=BASE(B2-1,$A$1,2)

You should see the pattern, and with some knowledge of bases be able to replicate it.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top