Express the numbers as sum of 2 or more unique consecutive numbers. If there is no solution, then leave the result as blank. Ex. 7 = 3+4 Ex. 8 = Can’t be expressed as sum of 2 or more unique consecutive numbers.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 359
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum of Unique Consecutive Numbers with Power Query
Power Query solution 1 for Sum of Unique Consecutive Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(k) =>
let
a = List.Last(
List.Skip(
List.Select(
List.Generate(
() => [x = 0, z = 1],
each [z] >= 1,
each [x = [x] + 1, z = (k[Numbers] - List.Sum({0 .. [x]})) / x],
each [z]
),
each Int64.From(_) = _
)
)
),
b = List.Generate(
() => [x = 1, y = a, z = a],
each [y] <= k[Numbers],
each [x = [x] + 1, z = [z] + 1, y = [y] + z],
each [z]
),
c = List.Transform(b, Text.From),
d = if List.Count(c) = 1 then null else Text.Combine(c, " + ")
in
d
)[[Answer]]
in
SolPower Query solution 2 for Sum of Unique Consecutive Numbers, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
a = x,
b = List.Buffer({1 .. a - 1}),
c = List.Generate(
() => [m = 0, n = 1],
each [m] < List.Count(b),
each if [n] = List.Count(b) then [m = [m] + 1, n = [m] + 2] else [m = [m], n = [n] + 1],
each List.Range(b, [m], [n] - [m])
),
d = List.Select(c, each List.Count(_) > 1 and List.Sum(_) = a),
e = try Text.Combine(List.Transform(d{0}, Text.From), " + ") otherwise null
in
e,
Sol = Table.AddColumn(Origen, "Answer Expected", each Fx([Numbers]))
in
SolSolving the challenge of Sum of Unique Consecutive Numbers with Excel
Excel solution 1 for Sum of Unique Consecutive Numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
TEXTJOIN(
"
",
,
MAP(
SEQUENCE(
SQRT(
2*a
)
)+1,
LAMBDA(
n,
LET(
m,
2*a/n-n+1,
IF(
MOD(
m,
2
),
"",
TEXTJOIN(
"+",
,
SEQUENCE(
n,
,
m/2
)
)
)
)
)
)
)
)
)Excel solution 2 for Sum of Unique Consecutive Numbers, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
1+SEQUENCE(
x^0.5
),
i,
x/s-0.5-s/2,
b,
INT(
i
)=i,
IFNA(
TEXTJOIN(
"+",
,
LOOKUP(
1,
0/b,
i
)+SEQUENCE(
LOOKUP(
1,
0/b,
s
)
)
),
""
)
)
)
)Excel solution 3 for Sum of Unique Consecutive Numbers, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
LAMBDA(
j,
MAP(
SEQUENCE(
15,
,
2,
0.5
),
LAMBDA(
x,
LET(
i,
SEQUENCE(
x,
,
INT(
a/x
)-j
),
TEXTJOIN(
" + ",
,
TAKE(
i,
XMATCH(
a,
SCAN(
0,
i,
LAMBDA(
c,
v,
c+v
)
)
)
)
)
)
)
)
),
w,
UNIQUE(
IFNA(
e(
0
),
e(
1
)
)
),
TEXTJOIN(
CHAR(
10
),
,
FILTER(
w,
IFNA(
w,
)>0,
""
)
)
)
)
)Excel solution 4 for Sum of Unique Consecutive Numbers, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
LAMBDA(
j,
MAP(
SEQUENCE(
15,
,
2,
0.5
),
LAMBDA(
x,
LET(
i,
SEQUENCE(
x,
,
INT(
a/x
)-j
),
TEXTJOIN(
" + ",
,
TAKE(
i,
XMATCH(
a,
SCAN(
0,
i,
LAMBDA(
c,
v,
c+v
)
)
)
)
)
)
)
)
),
IFNA(
LOOKUP(
"z",
IFNA(
e(
0
),
e(
1
)
)
),
""
)
)
)
)Excel solution 5 for Sum of Unique Consecutive Numbers, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(w,
LET(n,
w,
o,
SEQUENCE(
ROUNDUP(
n/2,
0
)
),
p,
BYCOL(
IFERROR(
INDEX(
o,
o+TOROW(
o
)
),
0
),
LAMBDA(
z,
LET(
a,
SCAN(
0,
z,
LAMBDA(
x,
y,
x+y
)
),
b,
FILTER(
a,
a<=n
),
c,
IF(
TAKE(
b,
-1
)=n,
TAKE(
z,
ROWS(
b
)
),
""
),
TEXTJOIN(
" + ",
,
IF(
c=0,
"",
c
)
)
)
)
),
FILTER(p,
(p<>"")*(LEN(
p
)=MAX(
LEN(
p
)
)),
""))))Excel solution 6 for Sum of Unique Consecutive Numbers, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
SEQUENCE(
99,
,
2
),
B,
CEILING(
z/A,
1
),
C,
MAP(
A,
B,
LAMBDA(
a,
b,
TEXTJOIN(
" + ",
,
b-FLOOR(
a/2,
1
)+SEQUENCE(
,
a,
0
)
)
)
),
TAKE(
FILTER(
C,
MAP(
C,
LAMBDA(
x,
SUM(
--TEXTSPLIT(
x,
" + "
)
)=z
)
),
""
),
-1
)
)
)
)
Only positive solutions:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
SEQUENCE(
99,
,
2
),
B,
CEILING(
z/A,
1
),
C,
MAP(
A,
B,
LAMBDA(
a,
b,
TEXTJOIN(
" + ",
,
b-FLOOR(
a/2,
1
)+SEQUENCE(
,
a,
0
)
)
)
),
D,
FILTER(
C,
MAP(
C,
LAMBDA(
x,
SUM(
--TEXTSPLIT(
x,
" + "
)
)=z
)
)
),
TAKE(
FILTER(
D,
LEFT(
D
)<>"-",
""
),
-1
)
)
)
)Excel solution 7 for Sum of Unique Consecutive Numbers, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
LAMBDA(a,
LET(_n,
SEQUENCE(
a/2
),
_m,
_n*(TOROW(
_n-1
)+(_n+1)/2),
_f,
FILTER(
_m,
MMULT(
TOROW(
_n
)^0,
N(
_m=a
)
)
),
IFERROR(
TEXTJOIN(
"+",
,
UNIQUE(
SCAN(
,
TAKE(
_f,
,
1
),
LAMBDA(
i,
x,
IF(
x>a,
i,
i+1
)
)
)
)
),
""
))))Excel solution 8 for Sum of Unique Consecutive Numbers, proposed by Bhavya Gupta:
= 7+8+9+10+11+12,
57 = 18+19+20,
57 = 28+29
=MAP(A2:A10,
LAMBDA(num,
TEXTJOIN(CHAR(
10
),
,
IFERROR(TOCOL(MAKEARRAY(ROUNDUP(
num/2+1,
),
ROUNDUP(
SQRT(
num
)+1,
),
LAMBDA(a,
n,
IF((n*(2*a+n-1))=2*num,
TEXTJOIN(
"+",
,
SEQUENCE(
n,
,
a
)
),
1/0))),
3),
""))))Excel solution 9 for Sum of Unique Consecutive Numbers, proposed by Charles Roldan:
=MAP(A2:A10,
LAMBDA(x,
LET(
s,
SEQUENCE(
,
SQRT(
2*x + 1/4
) - 1/2
),
n,
XLOOKUP(
0,
MOD(
x - COMBIN(
s,
2
),
s
),
s,
,
,
-1
),
IFNA(TEXTJOIN(" + ",
,
SEQUENCE(n,
,
x/n - (n - 1)/2)),
""))))Excel solution 10 for Sum of Unique Consecutive Numbers, proposed by JvdV -:
=MAP(A2:A10,LAMBDA(s,LET(q,SEQUENCE(s),x,LAMBDA(f,n,IFNA(REPT(TEXTJOIN("+",,TAKE(q+n,XMATCH(s,SCAN(,q+n,SUM)))),n<>s-1),f(f,n+1))),x(x,))))Excel solution 11 for Sum of Unique Consecutive Numbers, proposed by Giorgi Goderdzishvili:
= MAP(
A2:A10,
LAMBDA(
t,
LET(
_p,
t,
_nm,
INT(
_p/2
)+1,
_sq,
SEQUENCE(
,
_nm
),
_m,
SEQUENCE(
15
),
_ind,
_sq&"-"&_m,
_mp,
MAP(
_ind,
LAMBDA(
x,
LET(
_st,
TEXTBEFORE(
x,
"-"
),
_ln,
& TEXTAFTER(
x,
"-"
),
IFERROR(
SUM(
--INDEX(
_sq,
1,
SEQUENCE(
,
_ln,
_st
)
)
),
0
)
)
)
),
_fl,
FILTER(
TOCOL(
_ind
),
TOCOL(
_mp
)=_p,
""
),
_fn,
INDEX(
_fl,
ROWS(
_fl
),
1
),
IFERROR(
TEXTJOIN(
" + ",
TRUE,
SEQUENCE(
,
TEXTAFTER(
_fn,
"-"
),
TEXTBEFORE(
_fn,
"-"
)
)
),
""
)
)
)
)Excel solution 12 for Sum of Unique Consecutive Numbers, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,
LAMBDA(a,
LET(B,
SEQUENCE(
a/2+1
),
C,
SEQUENCE(
,
a/2+1
),
D,
--(SCAN(
0,
B,
LAMBDA(
X,
Y,
X+Y
)
)-
SCAN(
0,
C-1,
LAMBDA(
X,
Y,
X+Y
)
)=a),
E,
@TAKE(
FILTER(
B,
BYROW(
D,
LAMBDA(
X,
SUM(
X
)
)
)
),
1
),
F,
@TAKE(
FILTER(
C,
BYCOL(
D,
LAMBDA(
X,
SUM(
X
)
)
)
),
,
1
),
IFERROR(
TEXTJOIN(
" + ",
,
SEQUENCE(
E-F+1,
,
F
)
),
""
))))Excel solution 13 for Sum of Unique Consecutive Numbers, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,
LAMBDA(a,
LET(B,
SEQUENCE(
a/2+1
),
C,
SEQUENCE(
,
a/2+1
),
D,
--(SCAN(
0,
B,
LAMBDA(
X,
Y,
X+Y
)
)-SCAN(
0,
C-1,
LAMBDA(
X,
Y,
X+Y
)
)=a),
E,
TAKE(
FILTER(
B,
BYROW(
D,
LAMBDA(
X,
SUM(
X
)
)
)
),
1
),
F,
TAKE(
FILTER(
C,
BYCOL(
D,
LAMBDA(
X,
SUM(
X
)
)
)
),
,
1
),
TEXTJOIN(
" + ",
,
SEQUENCE(
E-F+1,
,
F
)
))))Excel solution 14 for Sum of Unique Consecutive Numbers, proposed by Arden Nguyen, CPA:
=LET(
ref,
A2,
g,
LAMBDA(_n,
(2*ref/_n-_n+1)/2),
f,
LAMBDA(
_i,
_n,
_self,
IF(
MOD(
_n,
1
)<>0,
IF(
_n>=0,
_self(
_i+1,
g(
_i+1
),
_self
),
""
),
SEQUENCE(
_i,
,
_n
)
)
),
TEXTJOIN(
" + ",
TRUE,
f(
2,
g(
2
),
f
)
)
)Solving the challenge of Sum of Unique Consecutive Numbers with R
R solution 1 for Sum of Unique Consecutive Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/359 Express as Sum of Consecutive Digits.xlsx", range = "A1:A10")
test = read_excel("Excel/359 Express as Sum of Consecutive Digits.xlsx", range = "B1:B10") %>%
mutate(`Answer Expected` = str_remove_all(as.character(`Answer Expected`), "\s"))
find_consecutive_sums <- function(target) {
results <- tibble()
for (start_num in 1:(target/2 + 1)) {
sum <- start_num
next_num <- start_num
while (sum < target) {
next_num <- next_num + 1
sum <- sum + next_num
if (sum == target) {
results <- bind_rows(results, tibble(start = start_num, end = next_num))
}
}
}
if (nrow(results) == 0) {
return(tibble(Numbers = target, seq = NA_character_))
} else {
sqs <- results %>%
mutate(
Numbers = target,
seq = map2_chr(start, end, ~paste(.x:.y, collapse = "+"))
) %>%
select(Numbers, seq)
return(sqs)
}
}
result = map(input$Numbers, find_consecutive_sums) %>%
bind_rows() %>%
group_by(Numbers) %>%
slice(1)
Solving the challenge of Sum of Unique Consecutive Numbers with Excel VBA
Excel VBA solution 1 for Sum of Unique Consecutive Numbers, proposed by Nicolas Micot:
VBA solution:
Function f_trouveSomme(sommeCherchee As Integer) As String
Dim nombreDep As Integer, nombre As Integer, somme As Integer, nombreFin As Integer
Dim resultat As String
nombreFin = 1 + sommeCherchee 2
For nombreDep = 1 To nombreFin
somme = 0
For nombre = nombreDep To nombreFin
somme = somme + nombre
If somme >= sommeCherchee Then Exit For
Next nombre
If somme = sommeCherchee Then
nombreFin = nombre
For nombre = nombreDep To nombreFin
resultat = resultat & IIf(resultat = "", "", " + ") & nombre
Next nombre
Exit For
End If
Next nombreDep
f_trouveSomme = resultat
End Function
&&