Forum Discussion
Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
INDEX can be used with an array of row and column numbers to transform the board into a table of records for each cell and its neighbors. Since INDEX does not trigger an error when 0 is provided as the row and/or column number, a logical test is needed to handle out-of-scope references for the outer edges of the board. From there, BYROW-SUM is used to get the total number of live neighbors for each cell, and WRAPROWS returns the new board back to its original dimensions.
=IF(
NOT(IterationsA),
board,
LET(
h, ROWS(board),
w, COLUMNS(board),
i, TOCOL(IFNA(SEQUENCE(h),board))+{0,0,0,1,1,1,-1,-1,-1},
j, TOCOL(IFNA(SEQUENCE(,w),board))+{0,1,-1,0,1,-1,0,1,-1},
t, i*j*(i<=h)*(j<=w),
REDUCE(
board,
SEQUENCE(IterationsA),
LAMBDA(arr,num,
LET(
a, IF(t,INDEX(arr,i,j),0),
n, BYROW(DROP(a,,1),SUM),
WRAPROWS(TAKE(a,,1)*(n=2)+(n=3),w)
)
)
)
)
)Alternatively, you could EXPAND the board to add an outer border of 0's, then DROP the appropriate number of rows and/or columns to shift the entire board in each of the 8 directions, to identify all neighbors at once.
=IF(
NOT(IterationsB),
BigBoard,
LET(
h, ROWS(BigBoard)+1,
w, COLUMNS(BigBoard)+1,
i, EXPAND(0,h+1,,0),
j, EXPAND(0,,w,0),
REDUCE(
BigBoard,
SEQUENCE(IterationsB),
LAMBDA(arr,num,
LET(
a, HSTACK(i,VSTACK(j,EXPAND(arr,h,w,0))),
↑, DROP(DROP(a,,1),-2,-1),
↓, DROP(DROP(a,2,1),,-1),
←, DROP(DROP(a,1),-1,-2),
→, DROP(DROP(a,1,2),-1),
↖, DROP(a,-2,-2),
↙, DROP(a,2,-2),
↗, DROP(a,-2,2),
↘, DROP(a,2,2),
n, ↑ + ↓ + ← + → + ↖ + ↙ + ↗ + ↘,
arr*(n=2)+(n=3)
)
)
)
)
)This can also be done systematically with MAP and REDUCE, which is demonstrated in the attached workbook. I've also included two boards that loop/repeat, so you can just click and hold the spinner button to watch it go!
Cheers!
Matrix Flow Accumulation adaptation using the same basic concept to transform the matrix into a table of records, then lambda recursion to generate a hierarchy table summarized with GROUPBY:
=LET( rng, BigFlowMatrix, h, ROWS(rng), w, COLUMNS(rng), n, SEQUENCE(h,w), i, TOCOL(IF(n,SEQUENCE(h)))+{0,0,-1,-1,-1,0,1,1,1}, j, TOCOL(IF(n,SEQUENCE(,w)))+{0,-1,-1,0,1,1,1,0,-1}, t, i*j*(i<=h)*(j<=w), val, IF(t,INDEX(rng,i,j),0), num, IF(t,INDEX(n,i,j),0), tst, DROP(val,,1)={1,2,4,8,16,32,64,128}, pId, TOCOL(IFS(tst,TAKE(num,,1)),2), cId, TOCOL(IFS(tst,DROP(num,,1)),2), fnλ, LAMBDA(me,arr,[acc], LET( acc, IF(ISOMITTED(acc),arr,acc), inc, ISNUMBER(XMATCH(DROP(arr,,1),pId,,2)), IF( OR(inc), LET( a, TRANSPOSE(FILTER(arr,inc)), b, DROP(a,1)=pId, c, HSTACK(TOCOL(IFS(b,TAKE(a,1)),2),TOCOL(IFS(b,cId),2)), me(me,c,VSTACK(acc,c)) ), GROUPBY(TAKE(acc,,1),DROP(acc,,1),ROWS,0,0) ) ) ), tbl, fnλ(fnλ,HSTACK(pId,cId)), XLOOKUP(n,TAKE(tbl,,1),DROP(tbl,,1),0,,2) )😊
Excel has come a long way in the last 6-7 years giving us multiple ways to solve problems. A 4th solution would make this a solution party!
Our approaches are similar but differ a bit with the recursive function and the use of GROUPBY to do the aggregations. I like GROUPBY because it lessens the burden on REDUCE to handle such calculations.
There is another matrix that precedes the directional matrix provided in the sample. This matrix contains elevation data which is checked with another algorithm. The algorithm is summarized here:
How Derive Continuous Flow works—ArcGIS Online | Documentation
It sounds do-able but certainly a bit of a time sink!
Interesting. To build a larger dataset for test purposes, I started out by superposing a contour map on the grid, so I appear to be working on the right lines. I had to avoid local minima (sinks) because they would simply fill without limit the way I modelled.
The thinking then went: depressions form lakes; increased volume changes the effective cell elevation; the lake spreads until it reaches a point at which it overflows the containment; redraw the contour map with erosion in mind? It could go on for ever!
