Arrange the given numbers in such a fashion that sum of preceding + current number is a perfect square. This is not applicable for first number so this sum checking has to start from second number. Multiple solutions may be possible. One possible solution is given. Explanation is following – 8+1 = 9, 1+15 = 16, 15+10 = 25, 10+6 = 16, 6+30 = 36, 30+34 = 64, 34+47 = 81 and 47+2 = 49
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 517
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Perfect Square Predecessor Checker with Power Query
Power Query solution 1 for Perfect Square Predecessor Checker, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl = List.Accumulate(
{1 .. List.Count(Source[Numbers]) - 1},
Source,
(s, c) =>
Table.ExpandListColumn(
Table.AddColumn(
s,
Text.From(c),
each
let
a = Record.ToList(_),
b = List.Difference(Source[Numbers], a)
in
b
),
Text.From(c)
)
),
Sol = Table.SelectRows(
Tbl,
each
let
a = Record.ToList(_),
b = List.Transform(
{1 .. List.Count(a) - 1},
each Number.Mod(Number.Sqrt(a{_} + a{_ - 1}), 1) = 0
)
in
List.AllTrue(b)
)
in
SolSolving the challenge of Perfect Square Predecessor Checker with Excel
Excel solution 1 for Perfect Square Predecessor Checker, proposed by Bo Rydobon 🇹🇭:
=LET(n,
TEXT(
A2:A10,
"-000"
),
m,
TOROW(
n
),
--TRANSPOSE(TEXTSPLIT(CONCAT(REDUCE(n,
SEQUENCE(
ROWS(
n
)-1
),
LAMBDA(a,
i,
TOCOL(IFS(ISERR(
FIND(
m,
a
)
)*(MOD(
SQRT(
RIGHT(
a,
3
)-m
),
1
)=0),
a&m),
3)))&"_"),
"-",
"_",
1)))Excel solution 2 for Perfect Square Predecessor Checker, proposed by Bo Rydobon 🇹🇭:
=LET(n,
A2:A10,
m,
TOROW(
n
),
p,
IF(MOD(
SQRT(
n+m
),
1
)+(n=m),
"",
n&-m),
x,
XLOOKUP(
1,
BYROW(
N(
p>""
),
SUM
),
p,
,
1
),
REDUCE(--TEXTSPLIT(
@FILTER(
x,
x>""
),
,
"-"
),
SEQUENCE(
ROWS(
n
)-2
)+1,
LAMBDA(a,
i,
LET(b,
@INDEX(
a,
i
),
c,
XLOOKUP(
b,
n,
p
),
VSTACK(a,
--TEXTAFTER(@FILTER(c,
ISNA(
XMATCH(
m,
a
)
)*(c>"")),
"-"))))))Excel solution 3 for Perfect Square Predecessor Checker, proposed by John V.:
=LET(
n,
A2:A10,
q,
SEQUENCE,
s,
q(
,
ROWS(
n
)
),
d,
INDEX(
n,
MID(
REDUCE(
"",
s,
LAMBDA(
a,
v,
TOCOL(
REPLACE(
a,
q(
,
v
),
,
v
)
)
)
),
s,
1
)
),
TRANSPOSE(
FILTER(
d,
BYROW(
d,
LAMBDA(
r,
AND(
MOD(
SQRT(
DROP(
r,
,
1
)+DROP(
r,
,
-1
)
),
1
)=0
)
)
)
)
)
)Excel solution 4 for Perfect Square Predecessor Checker, proposed by محمد حلمي:
=LET(d,A2:A10,s,SEQUENCE(,ROWS(d)),
j,INDEX(d,MID(REDUCE("",s,LAMBDA(a,v,TOCOL(
REPLACE(a,SEQUENCE(,v),,v)))),s,1)),TRANSPOSE(FILTER(j,
BYROW(MOD((DROP(j,,1)+DROP(j,,-1))^0.5,1)=0,AND))))Excel solution 5 for Perfect Square Predecessor Checker, proposed by محمد حلمي:
=LET(d,
A2:A10,
s,
SEQUENCE(
,
ROWS(
d
)
),
j,
INDEX(
d,
MID(
REDUCE(
"",
s,
LAMBDA(
a,
v,
TOCOL(
REPLACE(
a,
SEQUENCE(
,
v
),
,
v
)
)
)
),
s,
1
)
),
TRANSPOSE(FILTER(j,
BYROW(j,
LAMBDA(c,
AND(MOD((DROP(
c,
,
1
)+DROP(
c,
,
-1
))^0.5,
1)=0))))))Excel solution 6 for Perfect Square Predecessor Checker, proposed by محمد حلمي:
=LET(d,
A2:A10,
s,
SEQUENCE(
,
ROWS(
d
)
),
j,
INDEX(
d,
MID(
REDUCE(
"",
s,
LAMBDA(
a,
v,
TOCOL(
REPLACE(
a,
SEQUENCE(
,
v
),
,
v
)
)
)
),
s,
1
)
),
TOCOL(INDEX(j,
XMATCH(1,
--BYROW(j,
LAMBDA(c,
AND(MOD((DROP(
c,
,
1
)+DROP(
c,
,
-1
))^0.5,
1)=0))),
,
-1),
)))Excel solution 7 for Perfect Square Predecessor Checker, proposed by Julian Poeltl:
=LET(N,
A2:A10,
M,
TOROW(
N
),
P,
IF(MOD(
SQRT(
N+M
),
1
)+(N=M),
"",
N&-M),
X,
XLOOKUP(
1,
BYROW(
N(
P>""
),
LAMBDA(
A,
SUM(
A
)
)
),
P,
,
1
),
DROP(
REDUCE(
1*TEXTBEFORE(
FILTER(
X,
X>""
),
"-"
),
N,
LAMBDA(
A,
B,
VSTACK(
A,
LET(
F,
FILTER(
N,
ISNA(
XMATCH(
N,
A
)
)
),
IFERROR(
INDEX(
F,
XMATCH(
0,
MOD(
SQRT(
F+TAKE(
A,
-1
)
),
1
)
)
),
1
)
)
)
)
),
-1
))Excel solution 8 for Perfect Square Predecessor Checker, proposed by Julian Poeltl:
=LET(
N,
A2:A10,
P,
N+TOROW(
N
),
C,
N&"|"&TOROW(
N
)&"|",
F,
MOD(
SQRT(
P
),
1
)=0,
PF,
FILTER(
TOROW(
P
),
TOROW(
F
)
),
UPF,
UNIQUE(
PF,
1
),
CPF,
MAP(
UPF,
LAMBDA(
A,
COLUMNS(
FILTER(
PF,
PF=A
)
)
)
),
CF,
FILTER(
TOROW(
C
),
TOROW(
F
)
),
UC,
MAP(
UPF,
LAMBDA(
A,
TAKE(
FILTER(
CF,
PF=A
),
,
1
)
)
),
SA,
TAKE(
FILTER(
UC,
CPF=2
),
,
1
),
SN,
--SUBSTITUTE(
IF(
SUM(
--ISNUMBER(
SEARCH(
TEXTBEFORE(
SA,
"|"
)&"|",
CF
)
)
)>SUM(
--ISNUMBER(
SEARCH(
TEXTAFTER(
SA,
"|"
),
CF
)
)
),
TEXTAFTER(
SA,
"|"
),
TEXTBEFORE(
SA,
"|"
)
),
"|",
""
),
DROP(
REDUCE(
SN,
N,
LAMBDA(
A,
B,
VSTACK(
A,
LET(
F,
FILTER(
N,
ISNA(
XMATCH(
N,
A
)
)
),
IFERROR(
INDEX(
F,
XMATCH(
0,
MOD(
SQRT(
F+TAKE(
A,
-1
)
),
1
)
)
),
1
)
)
)
)
),
-1
)
)Excel solution 9 for Perfect Square Predecessor Checker, proposed by Timothée BLIOT:
=LET(
A,
TOROW(
A2:A10
),
B,
CHAR(
SEQUENCE(
,
COUNTA(
A
)
)+64
),
C,
A&B,
D,
REDUCE(
"",
ROW(
1:9
),
LAMBDA(
w,
v,
LET(
E,
FILTER(
w,
LEN(
w
)-LEN(
SUBSTITUTE(
w,
":",
""
) =v-1
)
),
TOCOL(
IF(
ISERR(
FIND(
C,
E
)
),
E&C&":",
1/0
),
3
)
)
)
),
H,
FILTER(
D,
MAP(
D,
LAMBDA(
x,
LET(
F,
TOCOL(
--REGEXEXTRACT(
x,
"d+",
1
)
),
PRODUCT(
--MAP(
ROW(
2:9
),
LAMBDA(
y,
LET(
G,
SUM(
DROP(
TAKE(
F,
y
),
MAX(
y-2,
0
)
)
)^0.5,
INT(
G
)=G
)
)
)
)
)
)
)
),
--REGEXEXTRACT(
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"|",
,
H
),
":",
"|",
1
)
),
"d+",
1
)
)Excel solution 10 for Perfect Square Predecessor Checker, proposed by Bilal Mahmoud kh.:
=INT(
SQRT(
n+p
)
)),
n,
""))),
h,
INDEX(
FILTER(
r,
r<>"",
0
),
1,
1
),
i,
REDUCE(
a,
VSTACK(
IF(
COUNTA(
x
)>1,
DROP(
x,
-1
),
x
),
h
),
LAMBDA(
s,
u,
TEXTJOIN(
" ",
TRUE,
MAP(
TEXTSPLIT(
s,
,
" "
),
LAMBDA(
v,
IF(
--v=--u,
"",
v
)
)
)
)
)
),
VSTACK(
IF(
COUNTA(
x
)>1,
DROP(
x,
-1
),
x
),
h,
i
)))),
b)Solving the challenge of Perfect Square Predecessor Checker with Python
Python solution 1 for Perfect Square Predecessor Checker, proposed by Konrad Gryczan, Ph&D:
import pandas as pd
import math
import itertools
path = "517 Arrange Numbers to Form Square Chains.xlsx"
input = pd.read_excel(path, usecols="A").values.flatten()
test = pd.read_excel(path, usecols="B").values.flatten()
def is_perfect_square(x):
return math.isqrt(x)**2 == x
def is_valid_sequence(nums):
return all(is_perfect_square(nums[i] + nums[i+1]) for i in range(len(nums) - 1))
def find_valid_permutation(nums):
for perm in itertools.permutations(nums):
if is_valid_sequence(perm):
return list(perm)
return None
result = find_valid_permutation(input)
result = " ".join(map(str, result[::-1]))
test = " ".join(map(str, test))
print(result == test) # True
Solving the challenge of Perfect Square Predecessor Checker with Python in Excel
Python in Excel solution 1 for Perfect Square Predecessor Checker, proposed by Alejandro Campos:
import itertools
import math
numbers = xl("A2:A10")[0]
def is_perfect_square(n):
return math.isqrt(n) ** 2 == n
def is_valid_permutation(perm):
for i in range(1, len(perm)):
if not is_perfect_square(perm[i-1] + perm[i]):
return False
return True
permutations = list(itertools.permutations(numbers))
valid_permutations = [perm for perm in permutations if is_valid_permutation(perm)]
df = pd.DataFrame(valid_permutations).T
df
Python in Excel solution 2 for Perfect Square Predecessor Checker, proposed by Abdallah Ally:
from math import sqrt, isqrt
df = xl("A1:B10", headers=True)
# Perform data munging
items = []
values = df['Numbers'].tolist()
while len(items) != len(values):
num1 = items[-1] if items else values[0]
num2 = [
x for x in values if x != num1 and x not in items
and sqrt(x + num1) == isqrt(x + num1)
]
if items and num2:
items += [num2[0]]
else:
items += [num2[0], num1]
df['My Answer'] = items
df
Python in Excel solution 3 for Perfect Square Predecessor Checker, proposed by Anshu Bantra:
import random
nums = list(xl("A1:A10", headers=True).values)
def all_perfect_sqs():
while True:
random.shuffle(nums)
lst = [np.sqrt(int(nums[_-1]+nums[_])).is_integer() for _ in range(1, len(nums)-1)]
if np.all(lst):
return nums
all_perfect_sqs()
Solving the challenge of Perfect Square Predecessor Checker with R
R solution 1 for Perfect Square Predecessor Checker, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(combinat)
path = "Excel/517 Arrange Numbers to Form Square Chains.xlsx"
input = read_excel(path, range = "A1:A10") %>% unlist()
test = read_excel(path, range = "B1:B10") %>% unlist()
is_perfect_square <- function(x) {
sqrt_x <- sqrt(x)
sqrt_x == floor(sqrt_x)
}
is_valid_sequence <- function(nums) {
all(map2_lgl(nums[-length(nums)], nums[-1], ~ is_perfect_square(.x + .y)))
}
find_valid_permutation <- function(nums) {
permutations <- permn(nums)
valid_perm <- keep(permutations, is_valid_sequence)
if (length(valid_perm) > 0) {
return(valid_perm[[1]])
} else {
return(NULL)
}
}
result = find_valid_permutation(input)
all.equal(unname(result), unname(test))
# [1] TRUE
&&