3 x 3 Grid...!!!

achalrikhi

New Member
Joined
Nov 20, 2013
Messages
38
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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)
 
Upvote 0
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 :-)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try if this increases chances for non-zero results
Code:
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))
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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