Fill Arithmetic Progression

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.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 182
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Fill Arithmetic Progression with Power Query

Power Query solution 1 for Fill Arithmetic Progression, proposed 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])

Power Query solution 2 for Fill Arithmetic Progression, proposed by Bo Rydobon 🇹🇭:

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

Power Query solution 3 for Fill Arithmetic Progression, proposed by Zoran Milokanović:

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

Power Query solution 4 for Fill Arithmetic Progression, proposed by Alejandro Simón 🇵🇦 🇪🇸:

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(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

Power Query solution 5 for Fill Arithmetic Progression, proposed 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

Power Query solution 6 for Fill Arithmetic Progression, proposed by Guillermo Arroyo:

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

Power Query solution 7 for Fill Arithmetic Progression, proposed by Mahmoud Bani Asadi:

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

Solving the challenge of Fill Arithmetic Progression with Excel

Excel solution 1 for Fill Arithmetic Progression, proposed 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))))

Excel solution 2 for Fill Arithmetic Progression, proposed by Bo Rydobon 🇹🇭:

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

Excel solution 3 for Fill Arithmetic Progression, proposed by Bo Rydobon 🇹🇭:

=> 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,)),"")))))

Excel solution 4 for Fill Arithmetic Progression, proposed 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}))))))

Excel solution 5 for Fill Arithmetic Progression, proposed by John V.:

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

Excel solution 6 for Fill Arithmetic Progression, proposed 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))))

Excel solution 7 for Fill Arithmetic Progression, proposed 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))
 )
))

Excel solution 8 for Fill Arithmetic Progression, proposed 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))))

Excel solution 9 for Fill Arithmetic Progression, proposed by Julian Poeltl:

=MAP(A2:A7,LAMBDA(A,LET(SP,TEXTSPLIT(A,", "),X,XMATCH("x",SP),C,COLUMNS(SP),IFS(X=1,INDEX(SP,,2)-INDEX(SP,,3)+INDEX(SP,,2),X=C,INDEX(SP,,C-1)-INDEX(SP,,C-2)+INDEX(SP,,C-1),1,(INDEX(SP,,X+1)-INDEX(SP,,X-1))/2+INDEX(SP,,X-1)))))

Excel solution 10 for Fill Arithmetic Progression, proposed by Timothée BLIOT:

=MAP(A2:A7, LAMBDA(a, LET(V,TEXTBEFORE(a,"x"), W,TEXTAFTER(a,"x"),
N, TEXTSPLIT(IF(LEN(V)>LEN(W),V,W),", ",,1), M, INDEX(N,,2)-INDEX(N,,1), IF(LEN(V)>LEN(W),MAX(TEXTSPLIT(V,", ",,1)*1)+M,MIN(TEXTSPLIT(W,", ",,1)*1)-M) )))

Excel solution 11 for Fill Arithmetic Progression, proposed by Hussein SATOUR:

=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))))

Excel solution 12 for Fill Arithmetic Progression, proposed by Oscar Mendez Roca Farell:

=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))))

Excel solution 13 for Fill Arithmetic Progression, proposed by Duy Tùng:

=MAP(A2:A7,LAMBDA(v,LET(a,IFERROR(TEXTSPLIT(v,,", ")*1,"x"),c,SEQUENCE(ROWS(a)),FILTER(FORECAST(c,a,c),a="x"))))

Excel solution 14 for Fill Arithmetic Progression, proposed 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)-_diffval*2),_m,LAMBDA(a,v,a+_diffval)),
XLOOKUP("x",_m,_tbl))))

Excel solution 15 for Fill Arithmetic Progression, proposed 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))))

Excel solution 16 for Fill Arithmetic Progression, proposed 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))
 )
 )
)

Excel solution 17 for Fill Arithmetic Progression, proposed by LEONARD OCHEA 🇷🇴:

= 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))))

Excel solution 18 for Fill Arithmetic Progression, proposed by Md. Zohurul Islam:

=MAP(A2:A7,LAMBDA(y,LET(
a,TEXTSPLIT(y,", "),
sq,SEQUENCE(,COUNTA(a)),
b,FILTER(sq,ISERROR(ABS(a))),
d,IF(b<=2,TAKE(a,,-2),TAKE(a,,2)),
e,ABS(SUM(d*{-1,1})),
f,IF(b=1,INDEX(a,b+1)-e,INDEX(a,b-1)+e),
f)))

Excel solution 19 for Fill Arithmetic Progression, proposed by Pieter de B.:

=MAP(A2:A7,LAMBDA(a,LET(t,--TEXTSPLIT(a,,","),x,XMATCH(TRUE,ISERROR(t)),s,{2,1},i,INDEX(t,x+IF(x<3,s,-s)),j,INDEX(i,2),j*2-INDEX(i,1))))

Excel solution 20 for Fill Arithmetic Progression, proposed by Charles Roldan:

=MAP(A2:A7, LAMBDA(AP, LET(Array, TEXTSPLIT(AP, , ", "),
Step, GCD(TOCOL(ABS(Array-TOROW(Array)), 2)),
ZeroSeq, SEQUENCE(ROWS(Array), 1, 0, Step),
Start, UNIQUE(TOCOL(Array-ZeroSeq, 2)),
Start + Step*(XMATCH("x", Array) - 1))))

Excel solution 21 for Fill Arithmetic Progression, proposed 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))))

Excel solution 22 for Fill Arithmetic Progression, proposed by Daniel Garzia:

=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))))

Excel solution 23 for Fill Arithmetic Progression, proposed by Miguel Angel Franco García:

=ABS(CONCAT(LET(a;ESPACIOS(DIVIDIRTEXTO(A2;","));b;ABS(FILTRAR(a;a<>"x"));c;CONTARA(b)+1;d;K.ESIMO.MAYOR(b;{12});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;""))))

Excel solution 24 for Fill Arithmetic Progression, proposed 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))))

Excel solution 25 for Fill Arithmetic Progression, proposed by Ben Gutscher:

=LET(nums,TEXTSPLIT(A2,", "),diff,MIN(IFERROR(INDEX(nums,2)-INDEX(nums,1),99),IFERROR(INDEX(nums,4)-INDEX(nums,3),99)),xloc,MATCH("x",nums,0),IFERROR(INDEX(nums,xloc+1)-diff,INDEX(nums,xloc-1)+diff))

Solving the challenge of &Fill Arithmetic Progression with Python in Excel

Python in Excel solution 1 for Fill Arithmetic Progression, proposed by Alejandro Campos:

data = xl("A2:A7").to_numpy().tolist()
def find_missing_number(seq):
 nums = [int(x) if x != 'x' else None for x in seq.split(", ")]
 missing_index = nums.index(None)
 for i in range(1, len(nums)):
 if nums[i] is not None and nums[i-1] is not None:
 common_diff = nums[i] - nums[i-1]
 break
 
 if missing_index == 0:
 nums[missing_index] = nums[1] - common_diff
 else:
 nums[missing_index] = nums[missing_index - 1] + common_diff
 
 return nums[missing_index]
df = pd.DataFrame(data, columns=['AP'])
df['Faltantes'] = df['AP'].apply(find_missing_number)
df['Faltantes']

Solving the challenge of Fill Arithmetic Progression with Power Query

Solving the challenge of Fill Arithmetic Progression with Excel

Solving the challenge of &Fill Arithmetic Progression with Python in Excel

Leave a Reply Cancel reply