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

 

[Kaper]

try such set of formulas:
B1: =RANDBETWEEN(0,0.67*A1)
C1: =RANDBETWEEN(0,0.67*(A1-B1))
D1: =A$1-SUM(B1:C1)
and copy this formula down
D2: =A$1-SUM(B2:C2)

then two formulas in row 2
B2: =RANDBETWEEN(0,0.67*(A1-B1))
C2: =RANDBETWEEN(0,0.67*MIN(A1-B2,A1-C1))

and finally row 3:
B3: =$A1-SUM(B1:B2)
and copy this formula right
C3: =$A1-SUM(C1:C2)
D3: =$A1-SUM(D1:D2)

 

[Kaper]

PS. Of course you could use just =$A$1/3 in all nine cells


PS2. Or may be
=Round($A$1/3,0)
in B1:C2, and then other 5 cells (column D and row 3) with formulas presented a post above
D1: =A$1-SUM(B1:C1)
and copy this formula down
D2: =A$1-SUM(B2:C2)
and
B3: =$A1-SUM(B1:B2)
and copy this formula right
C3: =$A1-SUM(C1:C2)
D3: =$A1-SUM(D1:D2)

but I expect you wouldn't like such solutions

 

[achalrikhi]

try such set of formulas:
B1: =RANDBETWEEN(0,0.67*A1)
C1: =RANDBETWEEN(0,0.67*(A1-B1))
D1: =A$1-SUM(B1:C1)
and copy this formula down
D2: =A$1-SUM(B2:C2)

then two formulas in row 2
B2: =RANDBETWEEN(0,0.67*(A1-B1))
C2: =RANDBETWEEN(0,0.67*MIN(A1-B2,A1-C1))

and finally row 3:
B3: =$A1-SUM(B1:B2)
and copy this formula right
C3: =$A1-SUM(C1:C2)
D3: =$A1-SUM(D1:D2)

Dear Kaper,

Thank you brother for replying. Your solution works very fine. Only a couple of things which I want to know.

1. If I put the number 5 in the cell " A1 ", cells B2 & C2 shows 0 & the cell D3 shows -1.

2. Can we avoid " 0 " & negative numbers in the grid for number put in the cell A1 between 1 & 9 ?

3. Can we have the same total even in cross grid ? i.e. The total of B1, C2, & D3 = A1 & D1, C2 & B3 = A1.

If yes, please guide.

Thanks & Regards,
Achal

 

[Kaper]

1) for sure it is not possible to avoid 0's if in A1 there will be 1 or 2.
But please note also, that at these are results of formulas, you can just pres F9 key to recalculate, and you will get new set of results.

2) To have comfortable situation with more control on bottom-right value and posiibly also keeping diagonals sum equal, while maintaining some level of randomness, I'd expect VBA based solution could be much better than formula based one.

 

[achalrikhi]

1) for sure it is not possible to avoid 0's if in A1 there will be 1 or 2.
But please note also, that at these are results of formulas, you can just pres F9 key to recalculate, and you will get new set of results.

2) To have comfortable situation with more control on bottom-right value and posiibly also keeping diagonals sum equal, while maintaining some level of randomness, I'd expect VBA based solution could be much better than formula based one.

Dear Kaper,

Thanks a lot for sparing time & replying. By pressing F9, the results change and I get fantastic results which gives me the result without any negative number & zero also. Only thing left now is the total of cross grids. Sometimes I get even 1 cross grid with the same total but one is still left. I hope even that will be solved. Do you know any way out ?

Thanks & Regards

Achal

 

[Kaper]

Try if this increases chances for non-zero results

B1:   =RANDBETWEEN(1,0.5*A1)
C1:   =RANDBETWEEN(1,0.67*(A1-B1))
B2:   =RANDBETWEEN(1,0.67*(A1-B1))
C2:   =RANDBETWEEN(1,0.67*MIN(A1-B2,A1-C1))

 

[Kaper]

But ...

Can* you show me just 1 sample combination of 3x3 grid of numbers (positive, >0) where all 3 rows, all 3 columns, and both diagonals DO sum to 7?.
I'd expect there is no such combination

For sum equal 9 it's easy to show one (and I'm woried it's the only one in this case), it's:
3 3 3
3 3 3
3 3 3

*If you can - show it, and we will be working on a tool in excel to generate it (and possibly other combinations too).

 

[achalrikhi]

But ...

Can* you show me just 1 sample combination of 3x3 grid of numbers (positive, >0) where all 3 rows, all 3 columns, and both diagonals DO sum to 7?.
I'd expect there is no such combination

For sum equal 9 it's easy to show one (and I'm woried it's the only one in this case), it's:
3 3 3
3 3 3
3 3 3

*If you can - show it, and we will be working on a tool in excel to generate it (and possibly other combinations too).

Dear Kaper,

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

4 9 2
3 5 7
8 1 6

Please have a look at it and see if it is possible for other numbers too...!!!

Regards
Achal

 

[thisoldman]

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