3 x 3 Grid...!!!

[achalrikhi]

Dear Friends,

I tried to make am excel sheet with a 3 x 3 Grid where If I put an integer in the cell " A1 ", for eg. 7, then the grid
i.e. B1,C1,D1 & B2,C2,D2 & B3,C3,D3 will be filled in such a way that all the rows & colmuns in the grid will sum exactly as
the number in the cell " A1 ". Please help me doing this with a formula. I hope, I am not sounding confusing...!!!

Regards,

Achal

 

[achalrikhi]

Are you looking for a magic square?

There are Excel solutions for magic squares posted in numerous places. A magic square does not allow repeated numbers; each square must be distinct. For a 3 x 3 magic square, the smallest sum, using positive integers, is 15.

Here's one where the totals equal 42:
11 16 15
18 14 10
13 12 17

Dear Kaper,

I don't know about the magic square but the last solution you suggested, is with double digit. I wish the solution comes with a single digit. I don't know if it is possible but still I wish I get the solution.

Thanks & Regards,

 

[Kaper]

Surely I can share one combination that I know. i.e. as under...!!!

4 9
3 5 7
8 1 6

Sorry - not!
It has all soms equal 15 While you originaly asked about (and I referred to it in my answer) 7

Please note also that other answer (the one which sums to 42) is not mine - it's by thisoldman

 

[achalrikhi]

Sorry - not!
It has all soms equal 15 While you originaly asked about (and I referred to it in my answer) 7

Please note also that other answer (the one which sums to 42) is not mine - it's by thisoldman

Dear Kaper,

First of All Thank You Very Much for all your time & help and yes, you are right, it was my mistake, I referred thisoldman's post as your post.

Dear thisoldman, sorry for the mistake and thanks to you too for all the help & suggestions you provided.

Regards,
Achal

 

[thisoldman]

No problem, achalrikhi. I'm sorry I haven't particpated in this discussion—I had other matters before me.

I can't provide a proof of these observations, but they seem obvious to me.

The only numbers that have a solution are divisible by three, that is, the Excel formula =MOD(Input, 3)=0 is TRUE.
There will always be a solution for a multiple of three where the output grid cells are all equal: all ones, or all twos, all threes, and so on.

The other class of solutions for multiples of three will be magic squares. Fifteen is the minimum row-column-diagonal sum where the magic square contains only positive integers

When I was taught how to construct magic squares, the base magic square looked like the leftmost one below. The other squares are just rotations of the base square or reflections of the rotations.

8,1,6    4,3,8    2,9,4    6,7,2
3,5,7    9,5,1    7,5,3    1,5,9
4,9,2    2,7,6    6,1,8    8,3,4

4,9,2    2,7,6    6,1,8    8,3,4
3,5,7    9,5,1    7,5,3    1,5,9
8,1,6    4,3,8    2,9,4    6,7,2

The sum for each row, column, and diagonal of the above squares is 15. If permutations aren't significant, you only need the one magic square, all the triplet combinations are there.

Adding one to each cell in the base magic square, gives row-column-diagonal sums of 18.

9, 2, 7
4, 6, 8
5,10, 3

Adding two gives the magic sum of 21.

If you allow zero or negative integers into your solution grid, you can find a magic square where the row-column-diagonal sum is 12, 9, 6, 3, zero, or -3, and so on. Subtracting five from each cell of the base magic square creates a grid where the sums are zero. If you subtract six from the base magic square, the sums are all -3.

 3,-4, 1     2,-5, 0
-2, 0, 2    -3,-1, 1
-1, 4,-3    -2, 3,-4

 

[Kaper]

You may run such brute force macro (create new workbook, open VBA editor by: Alt+F11, then from menu Insert - Module and paste the code) to get all possible combinations of single digit 3x3 grids with equal sums in rows, columns and diagonals:

Sub test1()
Dim i&, j&, k&, l&, m&, outrow&
Columns("A:K").ClearContents


Range("A1").FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("B3").FormulaR1C1 = "=R[-2]C[-1]-SUM(R[-2]C:R[-1]C)"
Range("C3").FormulaR1C1 = "=R[-2]C[-2]-SUM(R[-2]C:R[-1]C)"
Range("D2").FormulaR1C1 = "=R1C[-3]-SUM(RC[-2]:RC[-1])"
Range("D2").AutoFill Destination:=Range("D2:D3"), Type:=xlFillDefault
Range("A2").FormulaR1C1 = "=R[-1]C[1]+RC[2]+R[1]C[3]"
Range("A3").FormulaR1C1 = "=RC[1]+R[-1]C[2]+R[-2]C[3]"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


For i = 1 To 9
  Range("B1") = i
  For j = 1 To 9
    Range("C1") = j
    For k = 1 To 9
      Range("D1") = k
      For l = 1 To 9
        Range("B2") = l
        For m = 1 To 9
          Range("C2") = m
          Application.Calculate
          If (WorksheetFunction.CountIf(Range("A1:D3"), "<1") = 0) And _
              (WorksheetFunction.CountIf(Range("B1:D3"), ">9") = 0) Then
            If (Range("A1") = Range("A2")) And (Range("A2") = Range("A3")) Then
              outrow = Cells(Rows.Count, "G").End(xlUp).Row + 2
              Cells(outrow, "G").Resize(3, 3) = Range("B1:D3").Value
              Cells(outrow, "F") = Range("A1").Value
              Cells(outrow, "J").Resize(4, 1) = Range("A1").Value
              Cells(outrow, "K").Resize(4, 1).Formula = "=row()"
            End If
          End If
Next m, l, k, j, i
Application.Calculate
outrow = outrow + 2
Range("K3:K" & outrow).Value = Range("K3:K" & outrow).Value
Range("F1:K" & outrow).Sort key1:=Range("J1"), order1:=xlAscending, key2:=Range("K1"), order1:=xlAscending, Header:=xlNo
Columns("J:K").ClearContents
Range("A1:D3").ClearContents
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
        
End Sub

Output shall be ready in few seconds depending on computer configuration.

 

[thisoldman]

Oh, drat!

I came upon another solution—adds to six.

2, 1, 3
3, 2, 1
1, 3, 2

and

1, 3, 2
3, 2, 1
2, 1, 3