Excel BI Challenge Workout 182

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 182 —
(Solutions in any language are also welcome for Excel Challenges)

Find the missing numbers in Arithmetic Progressions given.
An arithmetic progression or arithmetic sequence (AP) is a sequence of numbers such that the difference from any succeeding term to its preceding term remains constant throughout the sequence. The constant difference is called common difference of that arithmetic progression.
Ex - 5, 12, 19, 26 - here, the difference between two consecutive terms is 7.

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/dMRR-Aqq

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

image
image800×572 97.7 KB

Excel BI’s LinkedIn Post

2

LinkedIn post By:
Bo Rydobon
 

M-Code
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Ans = Table.TransformRows(Source,each let 
    b = List.Transform(Text.Split([AP],", "),each try Number.From(_) otherwise null), 
    c= List.Min(List.Transform({1..List.Count(b)-1}, each b{_}-b{_-1}))??0,
    d = List.PositionOf(b,null)
    in List.Average({try b{d+1}-c otherwise null ,try b{d-1}+c otherwise null}))
in
  Ans
3

LinkedIn Post by:
Alejandro Simón

adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Answer", each let
a = Text.Split([AP], ", "),
b = List.Transform({0..List.Count(a)-1}, each try Number.From(a{_+1}) - Number.From(a{_}) otherwise null),
c = List.Distinct([List.Select](http://list.select/)(b, each _ <> null)){0},
d = List.PositionOf(a, "x"),
e = if d = 0 then Number.From(a{d+1})-c else Number.From(a{d-1})+c
in e)
in
Sol
4

LinkedIn Post by:
Mahmoud Bani Asadi

Power Query solution:

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Custom = Table.AddColumn(Source, "Result", each [
  a=Text.Split([AP],", "),
  b=List.Transform(a,each Number.From(_)),
  c = List.Skip(b),
  d={0..List.Count(c)},
  e=List.Transform(d,each try c{_}-b{_} otherwise null),
  f=List.Min(e),
  g=List.PositionOf(a,"x"),
  h=try b{g-1}+f otherwise b{g+1}-f
][h])
in
  Custom
5

LinkedIn post by:
Sunny Baggu

=MAP(A2:A7,LAMBDA(a,LET(_m,TEXTSPLIT(a,,", "),_cnt,COUNTA(_m), _diff,DROP(_m,1)-DROP(_m,-1),_diffval,UNIQUE(TOCOL(_diff,3)),_fstval,IFERROR(CHOOSEROWS(_m,1)+0,--CHOOSEROWS(_m,2)-_diffval), _tbl,MAKEARRAY(_cnt,1,LAMBDA(r,c,_fstval+(r-1)*_diffval)),XLOOKUP("x",_m,_tbl))))

6

LinkedIn Post by:
Zoran Milokanović

Aritmetic Progression w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each
let
l = List.Transform(List.ReplaceValue(Text.Split([AP], ", "), "x", "0", Replacer.ReplaceText), each Number.From(_)),
p = List.PositionOf(l, 0)
in
if p = 0 then 2 * l{p + 1} - l{p + 2} else
if p = List.Count(l) - 1 then 2 * l{p - 1} - l{p - 2} else
(l{p - 1} + l{p + 1}) / 2
)
in
Solution
7

LinkedIn Post by:
Luan Rodrigues
 

let
  Fonte = Tabela1,
  res = Table.AddColumn(Fonte, "Personalizar", each [
  a1 = Text.Select(Text.Replace([AP],", ",","),{"0".."9",","}),
  a = Expression.Evaluate(
  Text.Combine(
  List.LastN(
  Text.Split(
  Text.TrimEnd(a1,"," ),","),2),"-"))*-1,
  b = Text.Split([AP],", "),
  c = List.PositionOf(b,"x")+1,
  d = List.Count(b),
  d1 = List.Transform(List.Select(b, each _ <> "x"), Number.From),
  max = List.Max(d1),
  e = if c = 1 then a else 
  if c = d then max + a else Number.From(b{c-2}) + a
  ][e])
in
  res
8

LinkedIn post by
John Jairo V.

Hi to all

One option could be:

:white_check_mark:
=MAP(A2:A7,LAMBDA(x,LET(v,TEXTSPLIT(x,", "),n,TOCOL(-v,2),-TREND(n,XMATCH(n,-v),XMATCH("x",v)))))

Blessings!

9

LinkedIn Post by
Bo Rydobon

Ideal from Taeyong Shin

=MAP(A2:A8,LAMBDA(a,LET(b,IFERROR(--TEXTSPLIT(a,","),""),FORECAST(XMATCH("",b),b,MATCH(b,b)))))

10

LinkedIn Post by:
Guillermo Arroyo

=MAP(A2:A7,LAMBDA(a,LET(b,IFERROR(--TEXTSPLIT(a,", "),""),f,LAMBDA(i,j,XMATCH(i,j)),c,(MAX(b)-MIN(b))/(f(MAX(b),b)-f(MIN(b),b)),IF(f("",b)-1,INDEX(b,1,f("",b)-1)+c,INDEX(b,1,2)-c))))

11

LinkedIn Post by:
Omid Motamedisedeh

= Table.AddColumn(Source, "Answer Expected", each [a=List.Transform(Text.Split([AP],", "), each try Number.From(_) otherwise -1),b=List.PositionOf(a,-1),c= try 2*a{b+1}-a{b+2} otherwise try (a{b+1}+a{b-1})/2 otherwise 2*a{b-1}-a{b-2}][c])

12

LinkedIn Post by:
Miguel Angel Franco García

Envio mi soluccion…

=ABS(CONCAT(LET(a;ESPACIOS(DIVIDIRTEXTO(A2;","));b;ABS(FILTRAR(a;a<>"x"));c;CONTARA(b)+1;d;K.ESIMO.MAYOR(b;{1\2});e;INDICE(d;;1)-INDICE(d;;2);f;MIN(b);g;SI(INDICE(a;1;1)="x";APILARH(INDICE(a;1;3)-INDICE(a;1;2);b);SECUENCIA(;c;f;MIN(e)));SI(a="x";g;""))))

13

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=MAP(A2:A7, LAMBDA(y, LET(a, TEXTSPLIT(y, ", "), b, --FILTER(a, a<>"x"), c, TOCOL(b - INDEX(b, {1;2})), d, MIN(FILTER(c, c>0)), e, XMATCH("x", a), IF(e=1, INDEX(a, e+1) - d, INDEX(a, e-1) + d))))

14

LinkedIn post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=BYROW(A2:A7, LAMBDA(r, LET(_m, TEXTSPLIT(r, ,", "),_n, MAX( TOROW( DROP(_m, 1)-DROP(_m, -1), 2)),_v, TAKE(_m, 1), IF(_v<>"x",_v+_n*(MATCH("x",_m,)-1),_n))))

15

LinkedIn Post by:
Sunny Baggu
 

=MAP(A2:A7,LAMBDA(a,LET(_m,TEXTSPLIT(a,,", "),_diff,DROP(_m,1)-DROP(_m,-1),_diffval,UNIQUE(TOCOL(_diff,3)),
_tbl,SCAN(IFERROR(TAKE(_m,1)-_diffval,CHOOSEROWS(_m,2)-
16

LinkedIn Post by:
Bo Rydobon

=MAP(A2:A7,LAMBDA(a,LET(b,TEXTSPLIT(a,,", "),c,MIN(IFERROR(DROP(b,1)-b,"")),d,XMATCH("x",b),IF(d>1,INDEX(b,d-1)+c,INDEX(b,d+1)-c))))

17

LinkedIn post by:
LEONARD OCHEA

Using almost the same method as the great Bo Rydobon y Taeyong Shin, but with the INTERCEPT and SLOPE functions
y = ax + b a=> SLOPE b=> INTERCEPT

=MAP(A2:A7,LAMBDA(a,LET(b,TEXTSPLIT(a,,", "),p,XMATCH("x",b),x,SEQUENCE(COUNTA(b)),y,IFERROR(b*1,b), p*SLOPE(y,x)+INTERCEPT(y,x))))

18

LinkedIn Post by:
Kris Jaganah

=MAP(A2:A7,LAMBDA(x,LET(a,IFERROR(--TEXTSPLIT(x,,", "),0),b,SEQUENCE(ROWS(a)),e,MODE(a-VSTACK(0,DROP(a,-1))),IFERROR(FILTER(a,b=XLOOKUP(0,a,b-1))+e,FILTER(a,b=XLOOKUP(0,a,b+1))-e))))

19

LinkedIn Post by:
Taeyong Shin
 

=MAP(A2:A7, LAMBDA(m,
 LET(
 n, TEXTSPLIT(m, , ", "),
 s, FILTER(HSTACK(SEQUENCE(ROWS(n)), n), n <> "x"),
 FORECAST.LINEAR(XMATCH("x", n), --TAKE(s, , -1), TAKE(s, , 1))
 )
))
20

LinkedIn Post by:
Bo Rydobon

for A8 => 9, x, 27 B8=> 18

=MAP(A2:A8,LAMBDA(a,LET(o,{1,-1},b,TEXTSPLIT(a,,", "),AVERAGE(IFERROR(INDEX(b,XMATCH("x",b)+o)-o*MAX(IFERROR(DROP(b,1)-b,)),"")))))