Forum Discussion
Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
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鈥擜rcGIS 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!
