The first and last terms in an Arithmetic Progressions (AP) are given in each row. Find the in between numbers marked as x.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 609
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Missing AP Sequence with Power Query
solution 1 Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform(
Table.ToRows(A),
(z) =>
[
a = List.Select(z, each _ is number),
b = List.Count(List.Select(z, each _ = "x")) + 1,
c = (a{1} - a{0}) / b,
d = List.Accumulate(z, {}, (v, w) => v & {if w = "x" then List.Last(v) + c else w})
][d]
),
C = Table.FromRows(B, Table.ColumnNames(A))
in
Csolution 2 Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
(x) =>
let
a = List.RemoveNulls(Record.ToList(x)),
b = List.Count(a) - 1,
c = List.Select(a, each _ is number),
d =
let
aa = (List.Last(c) - c{0}) / b,
bb = {c{0}} & List.Transform({1 .. b}, each c{0} + aa * _)
in
bb,
e = List.FirstN(Record.FieldNames(x), b + 1),
f = Table.FromRows({d}, e)
in
f
)[A]
)
in
Solsolution 3 Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Transform(Table.ToRows(S), List.RemoveNulls),
Fx = (x) =>
let
b = (List.Last(x) - List.First(x)) / (List.Count(x) - 1),
c = List.Generate(
() => [i = x{0}, j = 0],
each [j] < List.Count(x),
each [i = [i] + b, j = [j] + 1],
each [i]
)
in
c,
Sol = Table.FromColumns(List.Zip(List.Transform(a, each Fx(_))), Table.ColumnNames(S))
in
Solsolution 4 Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = List.Transform(
Table.ToRows(Source),
each [
N = List.RemoveNulls(_),
F = List.First(N),
C = List.Count(N),
L = List.Last(N),
S = (L - F) / (C - 1),
R = List.Numbers(F, C, S)
][R]
),
Res = Table.FromColumns(List.Zip(Rows), Table.ColumnNames(Source))
in
Ressolution 5 Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (x) => List.Last(List.RemoveNulls(x)),
C = (x) =>
List.Generate(() => x{0}, each _ <= B(x), each _ + (B(x) - x{0}) / (List.NonNullCount(x) - 1)),
D = Table.FromList(Table.ToList(A, C), each _, Value.Type(A))
in
Dsolution 6 Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlPSUaoggA1NlWJ1opUMsUmB9CMQLmXI2MwUptKICLvNIHabQLkWKNah2GuOwwQTS1R1pnhsMzFHUogtaIww/QvyhSHI00YGIAJkm7EFzCxTM5iPzUFesDBWio0FAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Term1 = _t,
Term2 = _t,
Term3 = _t,
Term4 = _t,
Term5 = _t,
Term6 = _t,
Term7 = _t,
Term8 = _t,
Term9 = _t,
Term10 = _t
]
),
Ls = List.Transform(
Table.ToRows(Source),
each [
a = _,
b = List.RemoveItems(a, {""}),
c = Number.From(List.First(b)),
d = Number.From(List.Last(b)),
e = List.Count(b) - 1,
f = (d - c) / (e),
z = List.Zip({{e}, {c}, {f}})
][z]{0}
),
NLs = Table.Combine(
List.Transform(
Ls,
(x) =>
Table.FromRows({List.Accumulate({1 .. x{0}}, {x{1}}, (s, c) => s & {List.Last(s) + x{2}})})
)
),
Result = Table.RenameColumns(NLs, List.Zip({Table.ColumnNames(NLs), Table.ColumnNames(Source)}))
in
ResultSolving the challenge of Find Missing AP Sequence with Excel
solution 1 Bo Rydobon 🇹🇭:
=REDUCE(A1:J1,A2:A10,LAMBDA(a,v,LET(r,TAKE(v:J10,1),c,COLUMN(r),VSTACK(a,IF(r="","",FORECAST(c,r,c))))))Excel solution 2 for Find Missing AP Sequence, proposed by John V.:
=LET(
i,
A2:J10,
z,
COLUMNS(
i
),
IF(
i>0,
MAKEARRAY(
ROWS(
i
),
z,
LAMBDA(
r,
c,
FORECAST(
c,
INDEX(
i,
r,
),
SEQUENCE(
,
z
)
)
)
),
""
)
)solution 3 🇰🇷 Taeyong Shin:
=LET(
d,
A2:J10,
n,
COLUMNS(
d
),
MAKEARRAY(
ROWS(
d
),
n,
LAMBDA(
r,
c,
IF(
INDEX(
d,
r,
c
)>0,
FORECAST(
c,
INDEX(
d,
r,
),
SEQUENCE(
,
n
)
),
""
)
)
)
)solution 4 Kris Jaganah:
=REDUCE(A1:J1,
SEQUENCE(
ROWS(
A2:J10
)
),
LAMBDA(v,
w,
VSTACK(v,
LET(u,
INDEX(
A2:J10,
w,
),
a,
SUM(
N(
u="x"
)
)+1,
b,
MIN(
u
),
c,
(MAX(
u
)-b)/a,
d,
SCAN(
,
u,
LAMBDA(
x,
y,
IF(
y="x",
x+c,
y
)
)
),
IF(
d,
d,
""
)))))solution 5 Julian Poeltl:
=LET(T,
A2:J10,
M,
BYROW(
T,
MIN
),
X,
BYROW(
T,
MAX
),
C,
BYROW(
T,
COUNTA
),
IFNA(REDUCE(A1:J1,
SEQUENCE(
ROWS(
T
)
),
LAMBDA(A,
B,
VSTACK(A,
SEQUENCE(,
@INDEX(
C,
B
),
@INDEX(
M,
B
),
(@INDEX(
X,
B
)-@INDEX(
M,
B
))/(@INDEX(
C,
B
)-1))))),
""))solution 6 Aditya Kumar Darak 🇮🇳:
=LET(
_data,
A2:J10,
_frst,
TAKE(
_data,
,
1
),
_cols,
COLUMNS(
_data
),
_lst,
BYROW(
_data,
LAMBDA(
a,
TAKE(
TOROW(
a,
1
),
,
-1
)
)
),
_cnt,
BYROW(
_data,
LAMBDA(
a,
COUNTIFS(
a,
"x"
)
)
),
_blnk,
IF(
_cnt,
_cnt,
_cols - 2
),
_seq,
SEQUENCE(
1,
_cols
),
_dif,
(_lst - _frst) / (_blnk + 1),
_exp,
IFS(_blnk + 3 > _seq,
(_seq - 1) * _dif + _frst,
1,
""),
_exp
)solution 7 Timothée BLIOT:
=IFNA(REDUCE("Term"&SEQUENCE(
,
10
),
ROW(
1:9
),
LAMBDA(w,
v,
LET(A,
INDEX(
A2:J10,
v,
),
B,
COUNTA(
A
),
C,
MAX(
A
)-MIN(
A
),
D,
C/(B-1),
VSTACK(
w,
SEQUENCE(
,
C/D+1,
MIN(
A
),
D
)
)))),
"")solution 8 Hussein SATOUR:
=TEXTSPLIT(CONCAT(BYROW(A2:J10,LAMBDA(x,LET(a,COUNTA(x),b,MIN(x),ARRAYTOTEXT(SEQUENCE(a,,0)*INT((MAX(x)-b)/(a-1))+b))))&"/"),", ","/",1,,"")solution 9 Oscar Mendez Roca Farell:
=REDUCE(
A1:J1,
A2:A10,
LAMBDA(
i,
x,
LET(
t,
TAKE(
x:J10,
1
),
s,
SEQUENCE(
,
COLUMNS(
t
)
),
VSTACK(
i,
IF(
t>0,
FORECAST(
s,
t,
s
),
""
)
)
)
)
)solution 10 Duy Tùng:
=LET(a,
A2:A10,
b,
A2:J10,
VSTACK(A1:J1,
IF(b=0,
"",
IF(b<"",
b,
(BYROW(
b,
MAX
)-a)/(BYROW(
N(
b="x"
),
SUM
)+1)*(COLUMN(
b
)-1)+a))))solution 11 Sunny Baggu:
=LET(
_rng, A2:J10,
_n, BYROW(N(_rng <> ""), LAMBDA(a, SUM(a))),
_r, _n - 1,
_d, BYROW(_rng, LAMBDA(a, MAX(a) - MIN(a))),
_st, _d / _r,
IFNA(
DROP(
REDUCE(
"🌼",
SEQUENCE(ROWS(_rng)),
LAMBDA(a, v,
VSTACK(
a,
SEQUENCE(, INDEX(_n, v, 1), INDEX(_rng, v, 1), INDEX(_st, v, 1))
)
)
),
1
),
""
)
)solution 12 LEONARD OCHEA 🇷🇴:
=LET(m,A2:J10,c,COLUMNS(m),IF(m>0,MAKEARRAY(ROWS(m),c,LAMBDA(x,y,FORECAST(y,INDEX(m,x,),SEQUENCE(,c)))),""))solution 13 LEONARD OCHEA 🇷🇴:
=LET(
d,
A2:J10,
MAP(
d,
LAMBDA(
x,
IF(
x>0,
FORECAST(
COLUMNS(
A2:x
),
INDEX(
d,
ROWS(
A2:x
),
),
SEQUENCE(
,
COLUMNS(
d
)
)
),
""
)
)
)
)solution 14 Md. Zohurul Islam:
=LET(
hdr,
A1:J1,
data,
A2:J10,
First,
TAKE(
data,
,
1
),
Last,
BYROW(
data,
MAX
),
num,
BYROW(
data,
COUNTA
),
a,
Last-First,
b,
num-1,
d,
a/b,
e,
MAP(
num,
First,
d,
LAMBDA(
x,
y,
z,
ARRAYTOTEXT(
SEQUENCE(
1,
x,
y,
z
)
)
)
),
f,
IFNA(
REDUCE(
hdr,
e,
LAMBDA(
p,
q,
LET(
u,
--TEXTSPLIT(
q,
", "
),
v,
VSTACK(
p,
u
),
v
)
)
),
""
),
f
)solution 15 ferhat CK:
=IFNA(REDUCE(A1:J1,SEQUENCE(ROWS(A2:J10)),LAMBDA(i,j,VSTACK(i,LET(a,CHOOSEROWS(A2:J10,j),b,FILTER(a,a>0),SEQUENCE(,COLUMNS(b),MIN(b),ROUNDUP((MAX(b)-MIN(b)+1)/COLUMNS(b),0)))))),"")solution 16 Meganathan Elumalai:
=LET(z,A2:J10,s,SEQUENCE,r,s(ROWS(z)),IFNA(REDUCE(A1:J1,r,LAMBDA(a,v,VSTACK(a,LET(arr,INDEX(z,v,),mx,MAX(arr),mn,MIN(arr),s(,COUNTA(arr),mn,(mx-mn)/(COUNTA(arr)-1)))))),""))solution 17 JvdV -:
=LET(s,A2:J10,i,A2:A10,f,BYROW,IFS(s="","",s="x",(f(s,MAX)-i)/(f(s,COUNTA)-1)*(COLUMN(s)-1)+i,1,s))solution 18 Mihai Radu O:
LET(fct,
LAMBDA(a,
LET(b,
TOCOL(
a,
1
),
c,
MIN(
b
),
d,
MAX(
b
),
e,
ROWS(
b
),
f,
(d-c)/(e-1),
SEQUENCE(
,
e,
c,
f
))),
REDUCE(
A1:J1,
SEQUENCE(
ROWS(
A2:J10
)
),
LAMBDA(
x,
y,
VSTACK(
x,
fct(
INDEX(
&A2:J10,
y,
)
)
)
)
)
)solution 19 Jorge Alvarez:
=EXCLUIR(
REDUCE(
"";
BYROW(
A2:J10;
LAMBDA(
va;
LET(
ca;
UNIRCADENAS(
",";
;
va
);
x;
SECUENCIA(
SUMA(
ESNUMERO(
HALLAR(
",";
EXTRAE(
ca;
SECUENCIA(
LARGO(
ca
)
);
1
)
)
)*1
)+1
);
y;
SI.ERROR(
--DIVIDIRTEXTO(
ca;
;
","
);
""
);
UNIRCADENAS(
" ";
;
PRONOSTICO.LINEAL(
x;
y;
x
)
)
)
)
);
LAMBDA(
a;
v;
LET(
r;
DIVIDIRTEXTO(
v;
" "
);
SI.ND(
APILARV(
a;
r
);
""
)
)
)
);
1
)solution 20 Luis Enrique Charca Ponce:
=MAKEARRAY(9,
10,
LAMBDA(r,
c,
LET(raw,
A2:J10,
val,
INDEX(
raw,
r,
c
),
step,
BYROW(A2:J10,
LAMBDA(iRow,
LET(n,
COUNTIF(
iRow,
"x"
),
IF(n=0,
0,
(INDEX(
iRow,
n+2
)-INDEX(
iRow,
1
))/(n+1))))),
IF(val="x",
INDEX(
raw,
r,
1
)+INDEX(
step,
r
)*(c-1),
IF(
val="",
"",
val
)))))Solving the challenge of Find Missing AP Sequence with Python
solution 1 Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "609 Missing Terms in AP_3.xlsx"
input = pd.read_excel(path, usecols="A:J", nrows=10).apply(pd.to_numeric, errors='coerce')
test = pd.read_excel(path, usecols="A:J", skiprows=12, nrows=10).astype(np.float64)
input['LastPopulatedColumn'] = input.apply(lambda row: row.last_valid_index(), axis=1)
input['LastPopulatedValue'] = input.apply(lambda row: row[row['LastPopulatedColumn']], axis=1)
input['FirstValue'] = input.apply(lambda row: row['Term1'], axis=1)
input['Length'] = input['LastPopulatedColumn'].apply(lambda x: int(''.join(filter(str.isdigit, x))))
def generate_sequence(row):
step = (row['LastPopulatedValue'] - row['FirstValue']) / (row['Length'] - 1)
return [row['FirstValue'] + i * step for i in range(row['Length'])]
input['Sequence'] = input.apply(generate_sequence, axis=1)
sequence_df = pd.DataFrame(input['Sequence'].tolist(), columns=[f'Term{i+1}' for i in range(input['Length'].max())]).astype(np.float64)
print(sequence_df.equals(test)) # True
Solving the challenge of Find Missing AP Sequence with Python in Excel
solution 1 Alejandro Campos:
data = xl("A1:J10", headers=True).fillna('')
columns_count = len(data.columns)
def parse_row(series):
filtered = series[series != '']
x_count = (filtered == 'x').sum()
if x_count:
first, last = int(filtered.iloc[0]), int(filtered.iloc[-1])
return list(np.linspace(
first, last, x_count + 1, endpoint=False, dtype=int)) + [last]
return filtered.tolist()
final_data = [row + [''] * (columns_count - len(row)) for row in (
parse_row(data.iloc[i, :]) for i in range(len(data)))]
new_headers = [f'Term{i}' for i in range(1, 11)]
df_final = pd.DataFrame(final_data, columns=new_headers)
df_final
solution 2 Aditya Kumar Darak 🇮🇳:
data = xl("A1:J10", headers=True)
toList = data.values.tolist()
gnrt = [[x for x in i if not pd.isna(x)] for i in toList]
gnrt = [
[
s := i[0],
e := i[-1],
l := len(i),
d := (e - s) / (l - 1),
r := list(range(s, int(e) + 1, int(d))),
][-1]
for i in gnrt
]
result = pd.DataFrame(gnrt, columns=data.columns).fillna("")
result
solution 3 Anshu Bantra:
df = xl("A1:J10", headers=True).fillna('')
num_cols = len(df.columns)
def first_last_value(srs: pd.Series) -> int|list:
srs = srs[srs != '']
xs = (srs == 'x').sum()
if xs:
return int(srs[0]), int(srs[-1]), xs
else:
return srs
def complete_list(lst: list, cols: int) -> list:
return lst.extend( [''] * (num_cols - len(lst)) )
new_list = []
for _ in range(len(df)):
val = first_last_value(df.iloc[_,:])
if type(val) == tuple:
strt, end, xs = val
lst = list(np.linspace(start=strt, stop=end, num=xs+1, endpoint=False, dtype=int))
lst.append(end)
else:
lst = list(val)
complete_list(lst, num_cols)
new_list.append(lst)
new_list
Solving the challenge of Find Missing AP Sequence with R
solution 1 Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/609 Missing Terms in AP_3.xlsx"
input = read_excel(path, range = "A1:J10", col_types = "numeric")
test = read_excel(path, range = "A13:J22")
result = input %>%
rowwise() %>%
mutate(last_col = max(which(!is.na(c_across(everything())))),
last_val = c_across(everything())[last_col],
first_val = c_across(everything())[1]) %>%
mutate(seq = list(seq(first_val, last_val, by = (last_val - first_val) / (last_col - 1)))) %>%
unnest_wider(seq, names_sep = "_u") %>%
select(starts_with("seq")) %>%
set_names(names(input))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&