Calculate first 10000 entries of Recaman’s sequence – a(0) = 0, for n > 0, a(n) = a(n-1) – n if nonnegative and not already in the sequence, otherwise a(n) = a(n-1) + n
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 479
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Recaman’s Sequence with Power Query
Power Query solution 1 for Generate Recaman’s Sequence, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = List.Generate(
() => [a = 0, b = {0}, c = 0],
each [a] < 10000,
each [
a = [a] + 1,
b = x & {c},
x = List.Buffer([b]),
c = [c] + (if tf1 and not tf2 then - a else a),
tf1 = [c] > a,
tf2 = List.Contains(x, [c] - a)
],
each [c]
)
in
SourcePower Query solution 2 for Generate Recaman’s Sequence, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Generate(()=> [x=1, y={0}],
each [x]<=10000,
each [
x=[x]+1,
y = List.Buffer(if List.Last([y])-[x] < 0 or List.Contains([y], List.Last([y])-[x]) then [y] & {List.Last([y])+[x]} else [y] & {List.Last([y])-[x]})],
each List.Last([y]))
in
Source
Show translation
Show translation of this commentPower Query solution 3 for Generate Recaman’s Sequence, proposed by Abdallah Ally:
let
n = 10000,
Sequence = List.Numbers(1, n - 1),
Numbers = List.Accumulate(
Sequence,
{0},
(x, y) =>
let
a = List.Last(x),
b =
if a - y > 0 and List.PositionOf(x, a - y) = - 1 then
List.Buffer(x & {a - y})
else
List.Buffer(x & {a + y})
in
b
)
in
NumbersPower Query solution 4 for Generate Recaman’s Sequence, proposed by Daniel Madhadha:
let
Recamans = List.Generate(
() => [a = 1, x = 0, y = {}],
each [a] < 10000,
each
let
b = [x] - [a]
in
if b > 0 and not List.Contains([y], b) then
[a = [a] + 1, x = b, y = [y] & {b}]
else
[a = [a] + 1, x = [x] + [a], y = [y] & {[x] + [a]}],
each [x]
)
in
RecamansSolving the challenge of Generate Recaman’s Sequence with Excel
Excel solution 1 for Generate Recaman’s Sequence, proposed by Bo Rydobon 🇹🇭:
=LET(L,
LAMBDA(
x,
TEXT(
x,
"00000"
)
),
rs,
TOCOL(--MID(DROP(REDUCE({0;"00000"},
SEQUENCE(
9999
),
LAMBDA(a,
n,
LET(d,
TAKE(
a,
-1
),
r,
RIGHT(
d,
5
),
c,
L(r+IF((--r>n)*AND(
ISERR(
FIND(
L(
r-n
),
a
)
)
),
-n,
n)),
TOCOL(
IF(
LEN(
d
)+6>2^15,
VSTACK(
a,
c
),
VSTACK(
DROP(
a,
-1
),
d&" "&c
)
),
3
)))),
1),
SEQUENCE(
,
2^15/6,
,
6
),
6),
3),
rs)Excel solution 2 for Generate Recaman’s Sequence, proposed by Bo Rydobon 🇹🇭:
=REDUCE(0,
SEQUENCE(
9999
),
LAMBDA(a,
n,
LET(b,
TAKE(
a,
-1
),
VSTACK(a,
b+IF((b>n)*ISNA(
MATCH(
b-n,
a,
)
),
-n,
n)))))Excel solution 3 for Generate Recaman’s Sequence, proposed by Bo Rydobon 🇹🇭:
=LET(R,
LAMBDA(r,
a,
LET(n,
ROWS(
a
),
b,
TAKE(
a,
-1
),
c,
VSTACK(a,
b+IF((b>n)*ISNA(
MATCH(
b-n,
a,
)
),
-n,
n)),
IF(
n+1<2730,
r(
r,
c
),
c
))),
R(
R,
0
))Excel solution 4 for Generate Recaman’s Sequence, proposed by Rick Rothstein:
=REDUCE(0,
SEQUENCE(
9999
),
LAMBDA(a,
x,
LET(b,
TAKE(
a,
-1
),
VSTACK(a,
IF((x>=b)+(ISNUMBER(
XMATCH(
b-x,
a
)
)),
b+x,
b-x)))))Excel solution 5 for Generate Recaman’s Sequence, proposed by John V.:
=REDUCE(0,
ROW(
1:9999
),
LAMBDA(a,
n,
LET(i,
TAKE(
a,
-1
),
VSTACK(a,
i+n*-1^((i>n)*ISNA(
MATCH(
i-n,
a,
)
))))))Excel solution 6 for Generate Recaman’s Sequence, proposed by محمد حلمي:
=REDUCE(
0,
SEQUENCE(
999
),
LAMBDA(
a,
v,
LET(
i,
TAKE(
a,
-1
),
VSTACK(
a,
i+IF(
OR(
i-v=a,
i-v<1
),
v,
-v
)
)
)
)
)Excel solution 7 for Generate Recaman’s Sequence, proposed by Julian Poeltl:
=REDUCE(
,
SEQUENCE(
10000,
,
0
),
LAMBDA(
A,
B,
LET(
C,
TAKE(
A,
-1
),
VSTACK(
A,
C+IF(
OR(
B>C,
ISNUMBER(
XMATCH(
C-B,
A
)
)
),
B,
-B
)
)
)
)
)Excel solution 8 for Generate Recaman’s Sequence, proposed by Timothée BLIOT:
=REDUCE(0,
SEQUENCE(
10^4-1
),
LAMBDA(w,
v,
LET(A,
TAKE(
w,
-1
),
VSTACK(w,
IF(AND(A-v>0,
SUM(--(w=A-v))=0),
A-v,
A+v)))))Excel solution 9 for Generate Recaman’s Sequence, proposed by Abdallah Ally:
=REDUCE({0},
SEQUENCE(
10^4-1
),
LAMBDA(x,
y,
LET(a,
TAKE(
x,
-1
),
VSTACK(x,
a+IF((a-y>0)*(SUM(--(a-y=x))=0),
-y,
y)))))Excel solution 10 for Generate Recaman’s Sequence, proposed by Nicolas Micot:
=SI(
A2=0;
0;
SI(
ET(
B1-A2>0;
NB.SI(
B$1:B1;
B1-A2
)=0
);
B1-A2;
B1+A2
)
)Excel solution 11 for Generate Recaman’s Sequence, proposed by Ziad A.:
=REDUCE(
0,
SEQUENCE(
9999
),
LAMBDA(
a,
i,
{a;LET(
x,
INDEX(
a,
i
)-i,
x+OR(
x<0,
COUNTIF(
a,
x
)
)*2*i
)}
)
)Excel solution 12 for Generate Recaman’s Sequence, proposed by Jackson Fontana:
=IF(
AND(
B1-A2>0,
ISNA(
MATCH(
B1-A2,
$B$1:B1,
0
)
)
),
B1-A2,
B1+A2
)Solving the challenge of Generate Recaman’s Sequence with Python
Python solution 1 for Generate Recaman’s Sequence, proposed by Konrad Gryczan, PhD:
import pandas as pd
import time
path = "479 Recaman Sequence.xlsx"
test = pd.read_excel(path)
def recaman_sequence(n):
recaman = [0] * n
seen = set()
seen.add(0)
for i in range(1, n):
next_value = recaman[i - 1] - i
if next_value > 0 and next_value not in seen:
recaman[i] = next_value
else:
next_value = recaman[i - 1] + i
recaman[i] = next_value
seen.add(recaman[i])
return recaman
start_time = time.time()
sequence = recaman_sequence(10000)
end_time = time.time()
print(f"Time taken: {end_time - start_time} seconds")
# Time taken: 0.0019996166229248047 seconds
print(all(sequence[i] == test["Answer Expected"][i] for i in range(len(sequence))))
# True
Solving the challenge of Generate Recaman’s Sequence with Python in Excel
Python in Excel solution 1 for Generate Recaman’s Sequence, proposed by Alejandro Campos:
n = xl("A2")
aa = [0]
for i in range(1, n):
t1 = aa[-1] - i
t2 = aa[-1] + i
if (t1 > 0) * (t1 not in aa):
aa.append(t1)
else:
aa.append(t2)
aa
def add_to_plot(i):
c, r = (aa[i+1] + aa[i]) / 2, abs(aa[i+1] - aa[i]) / 2
x = np.linspace(-r, r, 1000)
y = np.sqrt(r**2 - x**2) * (-1)**i
color = cm(i/n)
X = x+c
f = np.sqrt(2)
ax.plot((X-y)*f, (X+y)*f, c=color, lw=1)
fig, ax = plt.subplots(facecolor='k')
ax.axis('equal')
ax.axis('off')
cm = plt.get_cmap('Spectral')
for i in range(0, xl("A2")-2):
add_to_plot(i)
Python in Excel solution 2 for Generate Recaman’s Sequence, proposed by Abdallah Ally:
import pandas as pd
def recaman_sequence(size):
numbers = [0]
for n in range(1, size):
num = numbers[-1]
if num - n > 0 and num - n not in numbers:
numbers.append(num - n)
else:
numbers.append(num + n)
return numbers
file_path = 'Excel_Challenge_479 - Recaman Sequence.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = recaman_sequence(len(df))
df['Check'] = df['Answer Expected'] == df['My Answer']
df
Solving the challenge of Generate Recaman’s Sequence with R
R solution 1 for Generate Recaman’s Sequence, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(tictoc)
library(memoise)
path = "Excel/479 Recaman Sequence.xlsx"
test = read_excel(path)
recaman_sequence <- function(n) {
recaman <- integer(n)
recaman[1] <- 0
seen <- setNames(logical(n * 3), 0:(n * 3 - 1))
seen[1] <- TRUE
for (i in 2:n) {
prev_value <- recaman[i - 1]
next_value <- prev_value - (i - 1)
if (next_value > 0 && !seen[next_value + 1]) {
recaman[i] <- next_value
} else {
next_value <- prev_value + (i - 1)
recaman[i] <- next_value
}
seen[recaman[i] + 1] <- TRUE
}
return(recaman)
}
tic()
recaman_sequence(10000)
toc()
# 0.05 sec elapsed
result = recaman_sequence(10000)
identical(result, test$`Answer Expected`)
# [1] TRUE
R solution 2 for Generate Recaman’s Sequence, proposed by Anil Kumar Goyal:
library(tidyverse)
n <- 10000
reduce(
seq(0, n-1, 1),
~ { if (tail(.x, 1) - .y < 0 | (tail(.x, 1) - .y) %in% .x) {
c(.x, tail(.x, 1) + .y)
} else {
c(.x, tail(.x, 1) - .y)
}}
)
&&&