Find the missing terms in Arithmetic Progressions given.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 325
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find missing terms in arithmetic progressions with Power Query
Power Query solution 1 for Find missing terms in arithmetic progressions, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Answer Expected",
each
let
f = Number.From,
a = Text.Split([AP], ", "),
p = List.Positions(a),
v = List.Select(p, each a{_} <> "x")
in
Text.Combine(
List.Transform(
List.Difference(p, v),
each Text.From(f(a{v{0}}) + (_ - v{0}) * ((f(a{v{1}}) - f(a{v{0}})) / (v{1} - v{0})))
),
", "
)
)
in
SPower Query solution 2 for Find missing terms in arithmetic progressions, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Cal = Table.AddColumn(
Source,
"Answer",
(k) =>
let
a = Text.Split(k[AP], ", "),
b = {0 .. List.Count(a) - 1},
c = List.Skip,
d = List.Transform(
b,
each List.Transform(
{0 .. List.Count(c(a, _)) - 1},
(y) =>
try (Number.From(c(a, _){y + 1}) - Number.From(c(a, _){0})) / (y + 1) otherwise null
)
),
e = List.RemoveNulls(List.Combine(d)){0},
f = List.PositionOf(a, "x", 2),
g = List.Positions(a),
h = List.Difference(g, f){0},
i = List.Transform(b, each Number.From(a{h}) - e * (h - _)),
j = Text.Combine(List.Transform(f, each Text.From(i{_})), ", ")
in
j
)[[Answer]]
in
CalPower Query solution 3 for Find missing terms in arithmetic progressions, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.Select(Text.Replace([AP], ", ", ","), {"0" .. "9", ","}),
b = Text.Combine(List.LastN(Text.Split(Text.TrimEnd(a, ","), ","), 3), "-"),
c = Text.Contains(Text.TrimStart(b, "-"), "--"),
d =
if c = true then
Number.Abs(Expression.Evaluate(Text.Replace(b, "--", "-")) / 2)
else
Number.Abs(Expression.Evaluate(Text.TrimStart(b, "-"))),
e = List.Transform(List.Select(Text.Split([AP], ", "), each _ <> "x"), Number.From),
f = List.Transform({1 .. List.Count(Text.Split([AP], ", "))}, (x) => (x * d)),
g = List.Transform({1 .. List.Count(Text.Split([AP], ", "))}, (x) => (x * d) - d / 2),
h = if List.AllTrue(List.Transform(e, (x) => List.Contains(g, x))) = true then g else f,
i = Text.Combine(List.Transform(List.Difference(h, e), Text.From), ", ")
][i]
)
in
resPower Query solution 4 for Find missing terms in arithmetic progressions, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Fx_Prog = (t as text) =>
let
T = t,
Sp = Text.Split(T, ", "),
Count = List.Count(Sp),
Pos = {1..Count},
Zipping = List.Zip({Sp,Pos}),
Tr= List.Select(Zipping, each not (List.Contains(_, "x"))),
FN = List.FirstN(Tr, 2),
Divd = Number.From(FN{1}{0}) - Number.From(FN{0}{0}),
Divs = Number.From(FN{1}{1}) - Number.From(FN{0}{1}),
d = Divd / Divs,
an = Number.From(FN{0}{0}),
n = Number.From(FN{0}{1}),
a1 = an - ((n-1) * d),
l2 = List.Accumulate(Pos, {}, (s,c) => s & {a1 + ((c - 1) * d)}),
LT = List.Transform(l2, each Text.From(_)),
LR = List.RemoveItems(LT,Sp),
LF = Text.Combine(LR, ", ")
in
LF,
Anw = Table.AddColumn(Source, "Answer Expected", each Fx_Prog([AP]))[[Answer Expected]]
in
Anw
🧙♂️🧙♂️🧙♂️
Solving the challenge of Find missing terms in arithmetic progressions with Excel
Excel solution 1 for Find missing terms in arithmetic progressions, proposed by Rick Rothstein:
=MAP(A2:A7,LAMBDA(x,LET(t,0+TEXTSPLIT(x,", "),i,ISNUMBER(t),n,CHOOSECOLS(FILTER(t,i,""),1,2),c,COUNTA(t),s,SEQUENCE(,c),p,CHOOSECOLS(TOROW(IF(ISNUMBER(INDEX(t,,s)),s,1/0),2),1,2),d,SUM(n*{-1,1})/SUM(p*{-1,1}),a,TAKE(n,,1)-d*(TAKE(p,,1)-1)+(s-1)*d,TEXTJOIN(", ",,UNIQUE(HSTACK(a,FILTER(t,i,"")),TRUE,TRUE)))))Excel solution 2 for Find missing terms in arithmetic progressions, proposed by John V.:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
IFERROR(
--TEXTSPLIT(
x,
,
","
),
""
),
s,
SEQUENCE(
ROWS(
a
)
),
ARRAYTOTEXT(
FILTER(
FORECAST(
s,
a,
s
),
a=""
)
)
)
)
)Excel solution 3 for Find missing terms in arithmetic progressions, proposed by محمد حلمي:
=MAP(A2:A7,
LAMBDA(a,
LET(
i,
IFERROR(
--TEXTSPLIT(
a,
,
","
),
""
),
m,
MAX(
i
),
n,
MIN(
i
),
v,
MATCH(
n,
i
),
x,
(m-n)/(MATCH(
m,
i
)-v),
TEXTJOIN(", ",
,
IF(N(
i
),
"",
SEQUENCE(ROWS(
i
),
,
n-(v-1)*x,
x))))))Excel solution 4 for Find missing terms in arithmetic progressions, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A7,
LAMBDA(
m,
LET(
y,
TRIM(
TEXTSPLIT(
m,
,
","
)
),
n,
SEQUENCE(
ROWS(
y
)
),
ARRAYTOTEXT(
FORECAST(
FILTER(
n,
y = "x"
),
IFERROR(
--y,
y
),
n
)
)
)
)
)Excel solution 5 for Find missing terms in arithmetic progressions, proposed by Kris Jaganah:
=MAP(
A2:A7,
LAMBDA(
w,
LET(
a,
TEXTSPLIT(
w,
,
", "
),
b,
SEQUENCE(
ROWS(
a
)
),
c,
XLOOKUP(
b-{1,
-1},
b,
a
),
d,
IFERROR(
MAX(
TOCOL(
ABS(
TAKE(
c,
,
1
)-TAKE(
c,
,
-1
)
)/2,
3
)
),
MAX(
TOCOL(
ABS(
a-c
),
3
)
)
),
e,
IF(
@a="x",
CHOOSEROWS(
a,
2
)-d,
@a
),
ARRAYTOTEXT(
FILTER(
VSTACK(
e,
SCAN(
e,
DROP(
a,
1
),
LAMBDA(
y,
z,
y+d
)
)
),
a="x"
)
)
)
)
)Excel solution 6 for Find missing terms in arithmetic progressions, proposed by Timothée BLIOT:
=MAP(A2:A7,
LAMBDA(z,
LET(A,
--TEXTSPLIT(
z,
,
","
),
B,
@UNIQUE(
TOCOL(
MAP(
SEQUENCE(
ROWS(
A
)-1,
,
2
),
LAMBDA(
x,
IFERROR(
INDEX(
A,
x
)-INDEX(
A,
x-1
),
ABS(
INDEX(
A,
x
)-INDEX(
A,
x+2
)
)/2
)
)
),
3
)
),
D,
XMATCH(
TRUE,
ISNUMBER(
A
)
),
E,
SEQUENCE(ROWS(
A
),
,
@(INDEX(
A,
D
)-((D-1)*B)),
B),
ARRAYTOTEXT(
FILTER(
E,
ISERR(
A
)
)
))))Excel solution 7 for Find missing terms in arithmetic progressions, proposed by Hussein SATOUR:
=MAP(A2:A7, LAMBDA(z, LET(a, TEXTSPLIT(z,,", "), b, FILTER(a, a<>"x"), c, MAX((DROP(b,1) - DROP(b,-1))/2, IFERROR(TAKE(-- a, 1), 0)), d, IF(INDEX(a, 1)="x", INDEX(a, 2)-c, INDEX(a, 1)), e, SCAN(d, SEQUENCE(COUNTA(a))^0, LAMBDA(x,y, x+(y*c)))-c, ARRAYTOTEXT(FILTER(e, a="x")))))Excel solution 8 for Find missing terms in arithmetic progressions, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A7, LAMBDA(a, LET(_n, TEXTSPLIT(a, ,", "),_s, SEQUENCE(COUNTA(_n)), _f,--FILTER(HSTACK(_s, _n),_n<>"x"), ARRAYTOTEXT(TREND(DROP(_f, ,1), TAKE(_f, ,1), TOCOL(IF(_n="x",_s, 1/0), 2), 1)))))Excel solution 9 for Find missing terms in arithmetic progressions, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
_ts,
TEXTSPLIT(
a,
,
", "
),
_seq,
SEQUENCE(
ROWS(
_ts
)
),
_tsnum,
TOCOL(
--_ts,
3
),
_s1,
FILTER(
_seq,
_ts <> "x"
),
_s2,
FILTER(
_seq,
_ts = "x"
),
ARRAYTOTEXT(
TREND(
_tsnum,
_s1,
_s2,
TRUE
)
)
)
)
)Excel solution 10 for Find missing terms in arithmetic progressions, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A7,
LAMBDA(a,
LET(n,
TEXTSPLIT(
a,
,
", "
),
c,
COUNTA(
n
),
s,
SEQUENCE(
c
),
x,
TOCOL(
n*s/n,
2
),
y,
TOCOL(
--n,
2
),
f,
TOCOL(s/(n="x"),
2),
ARRAYTOTEXT(
TREND(
y,
x,
f
)
))))Excel solution 11 for Find missing terms in arithmetic progressions, proposed by LEONARD OCHEA 🇷🇴:
=1), for any input data. It does not matter if the input data is not part of an arithmetic progression .
For example, input : 10, 20, x, 15, x, 30,
returns 19 and 16 which are the values of "x" in position 3 and 5
=MAP(A2:A7,LAMBDA(a,LET(n,TEXTSPLIT(a,,", "),c,COUNTA(n),s,SEQUENCE(c),x,TOCOL(n*s/n,2),y,TOCOL(--n,2),m,COUNT(x),f,TOCOL(s/(n="x"),2),k,LINEST(y,x^SEQUENCE(,m-1),1),ARRAYTOTEXT(MAP(f,LAMBDA(c,ROUND(SUM(k*c^SEQUENCE(,m,m-1,-1)),2)))))))Excel solution 12 for Find missing terms in arithmetic progressions, proposed by Md. Zohurul Islam:
=MAP(
A2:A7,
LAMBDA(
z,
LET(
a,
TEXTSPLIT(
z,
,
", "
),
b,
SEQUENCE(
COUNTA(
a
)
),
c,
IFERROR(
ABS(
a
),
a
),
d,
FILTER(
b,
a="x"
),
e,
FORECAST(
d,
c,
b
),
ARRAYTOTEXT(
e
)
)
)
)Excel solution 13 for Find missing terms in arithmetic progressions, proposed by Pieter de Bruijn:
=MAP(A2:A7,
LAMBDA(a,
LET(b,
--TEXTSPLIT(
a,
,
","
),
c,
ISNUMBER(
b
),
n,
TOROW(
b/c,
2
),
m,
TOROW(
SEQUENCE(
ROWS(
b
)
)/c,
2
),
d,
SUM(
INDEX(
n,
{1,
2}
)*{-1,
1}
),
e,
SUM(
INDEX(
m,
{1,
2}
)*{-1,
1}
),
z,
SCAN(@n-@m*(d/e),
b,
LAMBDA(
x,
y,
IFERROR(
y,
x+d/e
)
)),
ARRAYTOTEXT(
TOROW(
z/ISERR(
b
),
2
)
))))Excel solution 14 for Find missing terms in arithmetic progressions, proposed by Giorgi Goderdzishvili:
=
⌨⌨⌨
MAP(A3:A8,
LAMBDA(y,
LET(
sq,
TEXTSPLIT(
y,
", "
),
rl,
IFERROR(
--sq,
sq
),
s_,
SEQUENCE(
,
COLUMNS(
sq
)
),
mx_,
MAX(
rl
),
mn_,
MIN(
rl
),
&
mn_X,
XMATCH(
mn_,
rl,
0
),
st,
(mx_ - mn_)/ (XMATCH(
mx_,
rl,
0
) - XMATCH(
mn_,
rl,
0
)),
x_,
FILTER(
s_,
rl="x"
),
mkr,
MAKEARRAY(1,
COLUMNS(
rl
),
LAMBDA(r,
c,
IF(ISERROR(
XMATCH(
c,
x_,
0
)
),
"",
mn_+(c-mn_X)*st))),
TEXTJOIN(
", ",
TRUE,
mkr
))))Excel solution 15 for Find missing terms in arithmetic progressions, proposed by Edwin Tisnado:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
,
", "
),
c,
SEQUENCE(
ROWS(
b
)
),
y,
FILTER(
b,
b<>"x"
),
x,
FILTER(
c,
b<>"x"
),
ARRAYTOTEXT(
TREND(
--y,
x,
FILTER(
c,
b="x"
)
)
)
)
)
)Excel solution 16 for Find missing terms in arithmetic progressions, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A7,
LAMBDA(z,
LET(a,
TEXTSPLIT(
z,
,
", "
),
b,
SEQUENCE(
COUNTA(
a
)
),
c,
FILTER(
HSTACK(
a,
b
),
a<>"x"
),
d,
TAKE(
c,
2
),
e,
IMSUB(
TAKE(
d,
-1,
1
),
TAKE(
d,
1,
1
)
)/IMSUB(
DROP(
d,
1,
1
),
DROP(
d,
-1,
1
)
),
f,
IF(DROP(
d,
-1,
1
)=1,
--TAKE(
d,
1,
1
),
-(DROP(
d,
-1,
1
)-1)*e+TAKE(
d,
1,
1
))+(SEQUENCE(
MAX(
b
)
)-1)*e,
ARRAYTOTEXT(
FILTER(
f,
a="x"
)
))))Excel solution 17 for Find missing terms in arithmetic progressions, proposed by Diarmuid Early:
=MAP(A2:A7,
LAMBDA(x,
LET(input,
TEXTSPLIT(
x,
", "
),
ind,
SEQUENCE(
,
COUNTA(
input
)
),
firstTwo,
TAKE(
FILTER(
ind,
input<>"x"
),
,
2
),
diff,
SUM(
INDEX(
input,
firstTwo
)*{-1,
1}
)/SUM(
firstTwo*{-1,
1}
),
fullSeq,
diff*(ind-@firstTwo)+INDEX(
input,
@firstTwo
),
gaps,
FILTER(
fullSeq,
input="x"
),
output,
TEXTJOIN(
", ",
,
gaps
),
output)))
The 'diff' calculation is the difference of the first two non-blank terms divided by the difference of their indices - e.g. for row 4,
the first two are 6 and 14 at positions 2 and 4,
so the common differences is (14-6)/(4-2)Excel solution 18 for Find missing terms in arithmetic progressions, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(A2:A7,
LAMBDA(f,
LET(d,
TEXTSPLIT(
f,
", "
),
s,
SEQUENCE(
,
COLUMNS(
d
)
),
y,
VALUE(
FILTER(
d,
d<>"x"
)
),
x,
FILTER(
s,
d<>"x"
),
p,
SLOPE(
y,
x
),
_y0,
MIN(
TOCOL(
y,
2
)
),
_x0,
XMATCH(
_y0&"",
d
),
ARRAYTOTEXT(_y0+p*(FILTER(
s,
d="x"
)-_x0)))))Excel solution 19 for Find missing terms in arithmetic progressions, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(A2:A7,
LAMBDA(f,
LET(d,
TEXTSPLIT(
f,
", "
),
s,
SEQUENCE(
,
COLUMNS(
d
)
),
y,
VALUE(
FILTER(
d,
d<>"x"
)
),
x,
FILTER(
s,
d<>"x"
),
p,
SLOPE(
y,
x
),
_y0,
MIN(
TOCOL(
y,
2
)
),
_x0,
XMATCH(
_y0&"",
d
),
ARRAYTOTEXT(_y0+p*(FILTER(
s,
d="x"
)-_x0)))))Excel solution 20 for Find missing terms in arithmetic progressions, proposed by Hazem Hassan:
=MAP(A2:A7,LAMBDA(x,LET(a,TRIM(TEXTSPLIT(x,,",")),b,IFERROR(a*1,""),c,MAX(b),d,MIN(b),q,MATCH(c,b),v,MATCH(d,b),yy,q-v,e,(c-d)/(yy),f,yy=1,r,IF(f,c-d,e),s,ISNUMBER(a*1),t,MATCH(TRUE,s,0),o,XLOOKUP(TRUE,s,a),z,COUNTA(a),ARRAYTOTEXT(FILTER(IF(t>1,VSTACK(SORT(SEQUENCE(t,,o,-1*r)),SEQUENCE(z-t,,o+r,r)),SEQUENCE(z,,TEXTBEFORE(x,","),r)),a="x")))))Excel solution 21 for Find missing terms in arithmetic progressions, proposed by Gabriel Raigosa:
=BYROW(
A2:A7,
LAMBDA(
x,
LET(
t,
IFERROR(
TEXTSPLIT(
x,
","
)*1,
""
),
s,
SEQUENCE(
,
COUNTA(
t
)
),
x,
FORECAST(
s,
t,
s
),
TEXTJOIN(
", ",
,
IF(
t=x,
"",
x
)
)
)
)
)Excel solution 22 for Find missing terms in arithmetic progressions, proposed by Stevenson Yu:
=MAP(A2:A7,
LAMBDA(a,
LET(b,
TEXTSPLIT(
a,
,
", "
),
c,
SEQUENCE(
COUNTA(
b
)
),
d,
IF(
b="x",
"",
c
),
e,
SMALL(
d,
2
) - SMALL(
d,
1
),
f,
IFERROR(
--b,
1000
),
g,
SMALL(
f,
2
) - SMALL(
f,
1
),
h,
SEQUENCE(COUNTA(
b
),
,
MIN(f - (g/e * (c-1))),
g/e),
TEXTJOIN(
", ",
1,
IF(
b="x",
h,
""
)
))))Solving the challenge of Find missing terms in arithmetic progressions with Python in Excel
Python in Excel solution 1 for Find missing terms in arithmetic progressions, proposed by John V.:
Hi everyone!
One [Python] option could be:
s = np.array(t.split(", "))
i = np.where(s != "x")[0]
v = s[i].astype(int)
d = (v[-1] - v[0]) / (i[-1] - i[0])
return ", ".join(str(int(i)) for i in v[0] + d * (np.where(s == "x")[0] - i[0]))
[f(i) for i in xl("A2:A7")[0]]
Blessings!
Solving the challenge of Find missing terms in arithmetic progressions with R
R solution 1 for Find missing terms in arithmetic progressions, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(stringr)
library(readxl)
input = read_excel("Missing Number in AP_2.xlsx", range = "A1:A7")
test = read_excel("Missing Number in AP_2.xlsx", range = "B1:B7")
find_missing_numbers <- function(input_string) {
elements <- str_split(input_string, ",\s*")[[1]]
numbers <- map(elements, ~ if (str_detect(.x, "\d+")) as.numeric(.x) else NA_real_)
missing_indices <- which(is.na(numbers))
non_missing_indices <- which(!is.na(numbers))
if (length(non_missing_indices) >= 2) {
first_point <- non_missing_indices[1]
last_point <- non_missing_indices[length(non_missing_indices)]
common_difference <- (numbers[last_point][[1]] - numbers[first_point][[1]]) / (last_point - first_point)
} else {
common_difference <- 0
}
numbers[missing_indices] <- map(missing_indices, ~ numbers[first_point][[1]] + (.x - first_point) * common_difference)
missing_numbers_str <- numbers[missing_indices] %>% map_chr(as.character) %>% str_c(collapse = ", ")
return(missing_numbers_str)
}
result = input %>%
mutate(missing_numbers = map_chr(AP, find_missing_numbers))
&&