Apologies!! Scheduling didn’t work today.. Generate 10×10 grid starting with 1 and traversing from left to right, each number is sum of already filled in surrounding cells. Ex. for C3, already filled in surrounding cells are B2+C2+D2+B3
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 532
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum-Based Neighboring Grid Fill with Power Query
Power Query solution 1 for Sum-Based Neighboring Grid Fill, proposed by John V.:
let
N = Number.From,
R =
List.Accumulate({0..8}, {List.Repeat({1}, 10)},
(s, c) =>
s & {List.Buffer(List.Skip(
List.Generate(
() => [i = 0, x = 0],
each [x] < 11,
each [i = [i] + List.Sum(List.Range(s{c}, [x] - N([x] > 0), 3 - N([x] = 0))), x = 1 + [x]],
each [i]
)
))}
)
in
Table.FromRows(R)
Blessings!
Power Query solution 2 for Sum-Based Neighboring Grid Fill, proposed by Rafael González B.:
let
S = List.Sum, R = List.Range,
G = List.Generate(
() => [i = 1, LB = List.Repeat({1}, 10)],
each [i] <= 10,
each [
i = [i] + 1,
LB = List.LastN( List.Accumulate(
{0..9},
[LB],
(s,c) => s & (if c = 0 then {S(R([LB], 0, 2))}
else {S(R([LB], c- 1, 3) & {List.Last(s)})})
), 10)
],
each [LB]
),
Anw = Table.FromRows(G)
in
Anw
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Solving the challenge of Sum-Based Neighboring Grid Fill with Excel
Excel solution 1 for Sum-Based Neighboring Grid Fill, proposed by Bo Rydobon 🇹🇭:
=LET(s,SEQUENCE(,10),REDUCE(s^0,DROP(s,,1),LAMBDA(a,r,VSTACK(a,
SCAN(0,s,LAMBDA(p,i,SUM(p,INDEX(HSTACK(0,TAKE(a,-1),0),i+{0;1;2}))))))))Excel solution 2 for Sum-Based Neighboring Grid Fill, proposed by John V.:
=LET(s,
COLUMN(
A:J
),
REDUCE(s^0,
ROW(
1:9
),
LAMBDA(b,
y,
VSTACK(b,
SCAN(0,
s,
LAMBDA(a,
v,
SUM(a,
TAKE(DROP(
b,
,
v-2
),
-1,
3-(MOD(
v,
9
)=1)))))))))Excel solution 3 for Sum-Based Neighboring Grid Fill, proposed by محمد حلمي:
=REDUCE(
SEQUENCE(
,
10
)^0,
SEQUENCE(
9
),
LAMBDA(
a,
v,
VSTACK(
a,
SCAN(
0,
TAKE(
a,
-1
)+
HSTACK(
0,
TAKE(
a,
-1,
9
)
)+HSTACK(
TAKE(
a,
-1,
-9
),
0
),
sum
)
)
)
)Excel solution 4 for Sum-Based Neighboring Grid Fill, proposed by محمد حلمي:
=REDUCE(
SEQUENCE(
,
10
)^0,
SEQUENCE(
9
),
LAMBDA(
a,
v,
VSTACK(
a,
SCAN(
0,
TAKE(
a,
-1
)+HSTACK(
0,
TAKE(
a,
-1,
9
)
)+
HSTACK(
TAKE(
a,
-1,
-9
),
0
),
LAMBDA(
q,
w,
q+w
)
)
)
)
)Excel solution 5 for Sum-Based Neighboring Grid Fill, proposed by محمد حلمي:
=LET(s,SEQUENCE(,10),
REDUCE(s^0,DROP(s,,1),LAMBDA(a,v,VSTACK(a,
SCAN(0,MAP(s,LAMBDA(x,SUM(IFERROR(INDEX(a,
ROWS(a),IF(x=1,{1,2},x+{-1,0,1})),0)))),LAMBDA(q,w,q+w))))))Excel solution 6 for Sum-Based Neighboring Grid Fill, proposed by Julian Poeltl:
=WRAPROWS(
REDUCE(
1,
SEQUENCE(
99,
,
2
),
LAMBDA(
A,
B,
LET(
M,
MOD(
B,
10
),
VSTACK(
A,
IFS(
B<11,
1,
M=1,
INDEX(
A,
B-10
)+INDEX(
A,
B-9
),
M=0,
INDEX(
A,
B-11
)+INDEX(
A,
B-10
)+INDEX(
A,
B-1
),
1,
INDEX(
A,
B-11
)+INDEX(
A,
B-10
)+INDEX(
A,
B-9
)+INDEX(
A,
B-1
)
)
)
)
)
),
10
)Excel solution 7 for Sum-Based Neighboring Grid Fill, proposed by Julian Poeltl:
=WRAPROWS(REDUCE(1,
SEQUENCE(
,
99,
,
0
),
LAMBDA(A,
B,
VSTACK(A,
LET(R,
ROWS(
A
)+1,
M,
MOD(
R,
10
),
IFS((R>10)*(M<>1)*(M<>0),
INDEX(
A,
R-11
)+INDEX(
A,
R-10
)+INDEX(
A,
R-9
)+INDEX(
A,
R-1
),
(M=1)*(R>10),
INDEX(
A,
R-10
)+INDEX(
A,
R-9
),
(M=0)*(R>10),
INDEX(
A,
R-11
)+INDEX(
A,
R-10
)+INDEX(
A,
R-1
),
1,
B))))),
10)Excel solution 8 for Sum-Based Neighboring Grid Fill, proposed by Timothée BLIOT:
=LET(
A,
SEQUENCE(
,
10
),
REDUCE(
A/A,
SEQUENCE(
9
),
LAMBDA(
w,
v,
VSTACK(
w,
SCAN(
0,
A,
LAMBDA(
y,
x,
y+SUM(
DROP(
TAKE(
w,
-1,
MIN(
x+1,
10
)
),
,
MAX(
x-2,
0
)
)
)
)
)
)
)
)
)Excel solution 9 for Sum-Based Neighboring Grid Fill, proposed by Pieter de B.:
=LET(i,SEQUENCE(,10,2)-{0;1;2},REDUCE(TAKE(i,1)^0,TAKE(i,1,9),LAMBDA(x,y,VSTACK(x,SCAN(,MMULT({1,1,1},IFERROR(INDEX(TAKE(x,-1),,i/i^0),0)),LAMBDA(a,b,a+b))))))Excel solution 10 for Sum-Based Neighboring Grid Fill, proposed by Bilal Mahmoud kh.:
=REDUCE(SEQUENCE(,10,1,0),SEQUENCE(10),LAMBDA(x,y,IF(y=1,x,VSTACK(x,LET(a,TAKE(x,-1),b,MAP(SEQUENCE(,10),LAMBDA(n,IF(n=1,INDEX(a,1,n)+INDEX(a,1,n+1),IF(n=10,INDEX(a,1,n)+INDEX(a,1,n-1),INDEX(a,1,n)+INDEX(a,1,n-1)+INDEX(a,1,n+1))))),SCAN(,b,LAMBDA(s,r,s+r)))))))Excel solution 11 for Sum-Based Neighboring Grid Fill, proposed by JvdV -:
=DROP(LET(s,
SEQUENCE(
,
10
),
REDUCE(s^0,
s,
LAMBDA(x,
y,
VSTACK(x,
SCAN(0,
s,
LAMBDA(a,
b,
SUM(a,
TAKE(TAKE(
x,
-1,
b+1
),
,
(b=10)-3)))))))),
-1)Excel solution 12 for Sum-Based Neighboring Grid Fill, proposed by Celia Alves:
=WRAPROWS(
REDUCE({1},SEQUENCE(,99,2),
LAMBDA(current,new,
IF(new<=10,HSTACK(current,{1}),
IF(MOD(new,10)=1,HSTACK(current,SUM(INDEX(current,,new-10),INDEX(current,,new-9))),
IF(MOD(new,10)=0,HSTACK(current,SUM(INDEX(current,,new-11),INDEX(current,,new-10),INDEX(current,,new-1))),
HSTACK(current,SUM(INDEX(current,,new-11),INDEX(current,,new-10),INDEX(current,,new-9),INDEX(current,,new-1))))))))
,10)Solving the challenge of Sum-Based Neighboring Grid Fill with Python
Python solution 1 for Sum-Based Neighboring Grid Fill, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
test = pd.read_excel(path, usecols="B:K", skiprows =1 , header=None).values
matrix_size = 10
m = np.zeros((matrix_size, matrix_size))
m[0, 0] = 1
for i in range(matrix_size):
for j in range(matrix_size):
if i != 0 or j != 0:
m[i, j] = np.sum(m[max(i-1, 0):min(i+2, matrix_size),
max(j-1, 0):min(j+2, matrix_size)])
print(np.array_equal(m, test)) # True
Solving the challenge of Sum-Based Neighboring Grid Fill with Python in Excel
Python in Excel solution 1 for Sum-Based Neighboring Grid Fill, proposed by Alejandro Campos:
grid = np.zeros((10, 10), dtype=int)
grid[0, 0] = 1
def sum_surrounding_cells(i, j, grid):
sum_value = 0
for x in range(max(0, i-1), min(i+2, 10)):
for y in range(max(0, j-1), min(j+2, 10)):
if x == i and y == j:
continue
sum_value += grid[x, y]
return sum_value
for i in range(10):
for j in range(10):
if i == 0 and j == 0:
continue
grid[i, j] = sum_surrounding_cells(i, j, grid)
df = pd.DataFrame(grid)
df
Solving the challenge of Sum-Based Neighboring Grid Fill with R
R solution 1 for Sum-Based Neighboring Grid Fill, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
test = read_excel(path, range = "B2:K11", col_names = FALSE) %>% as.matrix()
matrix_size <- 10
m <- matrix(0, nrow = matrix_size, ncol = matrix_size)
m[1, 1] <- 1
for (i in 1:matrix_size) {
for (j in 1:matrix_size) {
if (i != 1 || j != 1) {
m[i, j] <- sum(m[pmax(i-1, 1):pmin(i+1, matrix_size),
pmax(j-1, 1):pmin(j+1, matrix_size)])
}
}
}
all.equal(m, test, check.attributes = FALSE) # TRUE
&&&