Today’s challenge is contributed by 🇰🇷 Taeyong Shin Converts the T1 matrix to a T2 matrix and the T2 matrix to a T3 matrix.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 661
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Matrix Transformation Series with Excel
Excel solution 1 for Matrix Transformation Series, proposed by Bo Rydobon 🇹🇭:
=LAMBDA(
z,
y,
LET(
n,
ROWS(
z
),
i,
SEQUENCE(
n
),
j,
TOROW(
i
),
m,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
),
INDEX(
TOCOL(
z
),
IF(
y,
XMATCH(
i*n-n+j,
TOCOL(
m
)
),
m
)
)
)
)(B2:F6,
)
=LAMBDA(
z,
y,
LET(
n,
ROWS(
z
),
i,
SEQUENCE(
n
),
j,
TOROW(
i
),
m,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
),
INDEX(
TOCOL(
z
),
IF(
y,
XMATCH(
i*n-n+j,
TOCOL(
m
)
),
m
)
)
)
)(I2:M6,
1)
T1 => T2
=LET(
z,
B2:F6,
n,
ROWS(
z
),
i,
SEQUENCE(
n
),
j,
TOROW(
i
),
m,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
),
INDEX(
TOCOL(
z
),
m
)
)
=LET(
z,
B2:F6,
n,
ROWS(
z
),
m,
MAKEARRAY(
n,
n,
LAMBDA(
i,
j,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
)
)
),
INDEX(
TOCOL(
z
),
m
)
)
T2=>T3
=LET(
z,
I2:M6,
n,
ROWS(
z
),
i,
SEQUENCE(
n
),
j,
TOROW(
i
),
m,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
),
XLOOKUP(
SEQUENCE(
n,
n
),
TOCOL(
m
),
TOCOL(
z
)
)
)
=LET(
z,
I2:M6,
n,
ROWS(
z
),
m,
MAKEARRAY(
n,
n,
LAMBDA(
i,
j,
i+IF(
i>j,
n*2*j-j^2-n,
n*2*i-i^2+j-n*2
)
)
),
XLOOKUP(
SEQUENCE(
n,
n
),
TOCOL(
m
),
TOCOL(
z
)
)
)Excel solution 2 for Matrix Transformation Series, proposed by John V.:
=LET(n,5,MAKEARRAY(n,n,LAMBDA(r,c,IF(r>c,r-c^2+2*n*c-n,c-r^2+r+2*n*(r-1)))))
T2 ► T1:
=WRAPROWS(SORT(TOCOL(I2:M6)),ROWS(I2:M6))Excel solution 3 for Matrix Transformation Series, proposed by Timothée BLIOT:
=LET(N,
5,
REDUCE(VSTACK(
SEQUENCE(
,
N
),
SEQUENCE(
N-1,
N
)*0
),
SEQUENCE(
N-1
),
LAMBDA(w,
v,
LET(A,
MAKEARRAY(N,
N,
LAMBDA(x,
y,
IF((y=v)*(x>v),
MAX(
w
)+x-v,
0))),
B,
MAKEARRAY(N,
N,
LAMBDA(x,
y,
IF((x=v+1)*(y>v),
MAX(
A
)+y-v,
0))),
w+A+B))))
(ii) Second Matrix =WRAPROWS(
SORT(
TOCOL(
I2:M6
)
),
5
)Excel solution 4 for Matrix Transformation Series, proposed by Oscar Mendez Roca Farell:
=LET(F,LAMBDA(F,n,[i],LET(m,MAKEARRAY(n,n,LAMBDA(r,c,IF((r=1)+(c=1),c*r+(n-1)*(r>1)+i,))),j,ROWS(m)-1,IF(j,m+IFNA(VSTACK(TAKE(m,n-j,1)*0,HSTACK(TAKE(m,1,n-j)*0,F(F,j,INDEX(m,n,n-j)))),),m))),F(F,5))
T2 to T1:
=LET(m,I2:M6,WRAPROWS(SORT(TOCOL(m)),ROWS(m)))Excel solution 5 for Matrix Transformation Series, proposed by Md. Zohurul Islam:
=LET(u,TAKE(B2:F6,1),a,{5,8,8,8,8},b,{6,12,14,14,14},c,{7,13,17,18,18},d,{8,14,18,20,20},v,VSTACK(a,b,c,d),e,IFNA(VSTACK("T2",u,u+v),""),f,IFNA(VSTACK("T3",WRAPROWS(SORT(TOCOL(DROP(e,1))),5)),""),g,IFNA(HSTACK(e,{"",""},f),""),g)Excel solution 6 for Matrix Transformation Series, proposed by Md. Zohurul Islam:
=LET(z,B2:F6,rw,ROWS(z),b,(rw)^2,sq,SEQUENCE(rw-1),d,REDUCE(b,sq,LAMBDA(x,y,LET(p,MIN(x)-2*y-1,q,MIN(x)-y,r,SEQUENCE(,y+1,p),s,SEQUENCE(y,,q),t,HSTACK(s,x),u,VSTACK(r,t),u))),e,WRAPROWS(SORT(UNIQUE(TOCOL(d))),rw),f,VSTACK("T2",d),g,VSTACK("T3",e),j,IFNA(HSTACK(f,{"",""},g),""),j)Excel solution 7 for Matrix Transformation Series, proposed by Philippe Brillault:
=MAKEARRAY(5,5,LAMBDA(r,c,IF(cExcel solution 8 for Matrix Transformation Series, proposed by Andres Rojas Moncada:
=MAKEARRAY(A2,
A2,
LAMBDA(fil,
col,
IF(col>fil-1,
-(fil^2)+((2*A2+1)*fil)-(2*A2)+col,
(A2-col)-(col^2)+((2*A2+1)*col)-(2*A2)+fil)))&&&