Excel BI Challenge Workout 182

Workouts Excel BI Challenge
21

LinkedIn post by:
Daniel G.
 

=BYROW(A2:A7,LAMBDA(a,LET(_n,IFERROR(--TEXTSPLIT(a,,", "),"x"),_p,XMATCH("x",_n),
_r,IF(_p>2,INDEX(_n,2)-INDEX(_n,1),INDEX(_n,_p+2)-INDEX(_n,_p+1)),
IF(_p>1,INDEX(_n,_p-1)+_r,INDEX(_n,_p+1)-_r))))
22

LinkedIn post by:
Mayoor Bhatt

Sum of digits(1+3+7+9 =20) & divided by 4 ( count Numeric number)simple (20/4=5):pray::india::rofl::joy:

In Excel, go custom & 0 “x”
& sum of above answers :india::pray:

23

LinkedIn post by:
محمد حلمي
 

=MAP(A2:A7,LAMBDA(a,LET(
v,IFERROR(TEXTSPLIT(a,",")+0,""),
r,SUM(LARGE(v,{1,2})*{-1,1}),
IFERROR(r+INDEX(v,XMATCH("",v)+1),MAX(v)-r))))
24

LinkedIn post by:
محمد حلمي
 

=MAP(A2:A7,LAMBDA(a,LET(
v,IFERROR(TEXTSPLIT(a,",")+0,""),
r,SUM(LARGE(v,{1,2})*{-1,1}),
IFERROR(r+INDEX(v,XMATCH("",v)+1),MAX(v)-r))))
25

LinkedIn Post by:
Sunny Baggu
 

=MAP(
 A2:A7,
 LAMBDA(a,
 LET(
 _m, TEXTSPLIT(a, , ", "),
 _d1, DROP(_m, 1),
 _d_1, DROP(_m, -1),
 _val, TAKE(TOCOL(_d1 - _d_1, 2), 1),
 _arr1, _d1 - _val,
 _arr2, _d_1 - _val,
 _arr3, _d_1 + _val,
 XLOOKUP("x", _d_1, _arr1, XLOOKUP("x", _d1, _arr3))
 )
 )
)
26

LinkedIn post by:
Rick Rothstein

=MAP(A2:A7,LAMBDA(x,LET(t,TEXTSPLIT(x,", "),n,XMATCH("x",t),IFERROR(IF(n>2,INDEX(t,n-1)+INDEX(t,2)-INDEX(t,1),2*INDEX(t,n+1)-INDEX(t,n+2)),AVERAGE(INDEX(0+t,{1,3}))))))

27

LinkedIn Post by:
Stevenson Yu
 

=MAP(A2:A7, LAMBDA(X, 
LET(A, TEXTSPLIT(X,", "),
B, COLUMNS(A),
C, MATCH("x", A, 0),
D, IF(C>2, INDEX(A,,2) - INDEX(A,,1), INDEX(A,,B) - INDEX(A,,B-1)),
IF(C=1, INDEX(A,,2)-D, INDEX(A,,C-1)+D))))
28

LinkedIn Post by:
Guillermo Arroyo

Power Query

let
 Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 a = List.Transform(Origen[AP], each Text.Split(_,", ")),
 b = List.Transform(a, each 
 let 
 o = List.Min(List.RemoveItems(_, {"x"})),
 p = List.Max(List.RemoveItems(_, {"x"})),
 q = (Number.FromText(p) - Number.FromText(o)) / (List.PositionOf(_, p) - List.PositionOf(_, o)),
 r = List.PositionOf(_, "x")
 in 
 if r = 0 then Number.FromText(_{1}) - q else Number.FromText(_{r-1}) + q)
in
 b