Fill in the digit marked as X to make the number a perfect square. When more than one answers, write them comma separated. If not possible, write NP.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 524
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Complete Square by X Placeholder with Power Query
Power Query solution 1 for Complete Square by X Placeholder, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = List.Transform(
Table.ToList(Source),
each List.Accumulate(
List.Transform(Text.ToList(_), (x) => if x = "X" then {"0" .. "9"} else {x}),
{"0"},
(a, b) => List.TransformMany(a, each b, (x, y) => x & y)
)
),
S2 = List.Transform(
S1,
each try
Text.RemoveRange(
List.Accumulate(
_,
"",
(a, b) =>
if Number.Mod(Number.Sqrt(Expression.Evaluate(b)), 1) = 0 then
a & "," & Text.RemoveRange(b, 0, 1)
else
a
),
0,
1
)
otherwise
""
)
in
S2Power Query solution 2 for Complete Square by X Placeholder, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList([Numbers]),
b = List.Transform(a, each if _ = "X" then {"0" .. "9"} else _),
c = List.PositionOf(a, "X", 2),
d = Table.FromRows({b}),
e = List.Transform(c, each Table.ColumnNames(d){_}),
f = List.Accumulate(e, d, (s, c) => Table.ExpandListColumn(s, c)),
g = Table.ToRows(f),
h = List.Transform(g, each Number.From(Text.Combine(_))),
i = List.Select(h, each Number.Mod(Number.Sqrt(_), 1) = 0),
j = if i = {} then "NP" else Text.Combine(List.Transform(i, Text.From), ", ")
in
j
)
in
SolSolving the challenge of Complete Square by X Placeholder with Excel
Excel solution 1 for Complete Square by X Placeholder, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
n,
LET(
s,
SEQUENCE(
SUBSTITUTE(
n,
"X",
9
)^0.5
)^2,
ARRAYTOTEXT(
FILTER(
s,
SEARCH(
SUBSTITUTE(
n,
"X",
"?"
),
s&"aa"&n
)=1,
"NP"
)
)
)
)
)Excel solution 2 for Complete Square by X Placeholder, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(x,LET(r,ROUNDUP(SQRT(SUBSTITUTE(x,"X",{0,9})),0),s,(SEQUENCE(SUM(r*{-1,1}))+TAKE(r,,1)-1)^2,IFERROR(TEXTJOIN(", ",,FILTER(s,ISNUMBER(SEARCH(SUBSTITUTE(x,"X","?"),s)))),"NP"))))Excel solution 3 for Complete Square by X Placeholder, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
i,
REDUCE(
x,
ROW(
1:4
),
LAMBDA(
a,
v,
TOCOL(
SUBSTITUTE(
a,
"X",
SEQUENCE(
,
10
)-1,
1
)
)
)
),
ARRAYTOTEXT(
UNIQUE(
FILTER(
i,
MOD(
i^0.5,
1
)=0,
"NP"
)
)
)
)
)
)
or faster way:
✅=MAP(
A2:A10,
LAMBDA(
x,
LET(
i,
REDUCE(
x,
SEQUENCE(
ROWS(
TEXTSPLIT(
x,
,
"X"
)
)-1
),
LAMBDA(
a,
v,
TOCOL(
SUBSTITUTE(
a,
"X",
SEQUENCE(
,
10
)-1,
1
)
)
)
),
ARRAYTOTEXT(
FILTER(
i,
MOD(
i^0.5,
1
)=0,
"NP"
)
)
)
)
)Excel solution 4 for Complete Square by X Placeholder, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(e,LET(s,SEQUENCE(,9),i,REDUCE(e,s,LAMBDA(a,v,UNIQUE(TOCOL(SUBSTITUTE(a,"X",s,1),2)))),IFERROR(ARRAYTOTEXT(TOCOL(i/(MOD(i^0.5,1)=0),2)),"NP"))))Excel solution 5 for Complete Square by X Placeholder, proposed by محمد حلمي:
= SQRT(i)
When we take the root of a number
We have two possibilities
It has a root
So there will be no decimal fraction
Or it has no root and therefore has a decimal fraction
16^2 = 4
15^2 = 3.873
We search in this Challenge on number that has root
so
We use MOD to test every number is it has root or no
by using MOD
MOD(2,1) = 0
MOD(2.5,1) = .5
When result of MOD = 0 this means that number is own of answer
i/(mod(i^.5,1)=0)
this step = if(mod(i^.5,1)=0,i,error)
But For the sake of brevity we have used this approach to TOCOL solve this problem
=TOCOL(5/SEQUENCE(4,,0),2)Excel solution 6 for Complete Square by X Placeholder, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
N,
LET(
S,
SEQUENCE(
,
10,
0
),
R,
REDUCE(
N,
S,
LAMBDA(
A,
B,
UNIQUE(
TOCOL(
SUBSTITUTE(
A,
"X",
S,
1
),
3
)
)
)
),
TEXTJOIN(
", ",
,
IFERROR(
FILTER(
R,
MOD(
SQRT(
R
),
1
)=0
),
"NP"
)
)
)
)
)Excel solution 7 for Complete Square by X Placeholder, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z, ARRAYTOTEXT(IFERROR(TOCOL(REGEXEXTRACT( SEQUENCE(500000)^2,"^"&SUBSTITUTE(z,"X",".")&"$"),3),"NP"))))Excel solution 8 for Complete Square by X Placeholder, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
a,
LEN(
v
)-LEN(
SUBSTITUTE(
v,
"X",
""
)
),
c,
BYROW(
MID(
TEXT(
SEQUENCE(
10^a,
,
0
),
REPT(
"0",
a
)
),
SEQUENCE(
,
a
),
1
),
LAMBDA(
z,
REDUCE(
v,
SEQUENCE(
a
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
"X",
INDEX(
z,
,
y
),
1
)
)
)
)
),
IFERROR(
ARRAYTOTEXT(
FILTER(
c,
SQRT(
c
)=INT(
SQRT(
c
)
)
)
),
"NP"
)
)
)
)Excel solution 9 for Complete Square by X Placeholder, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10, LAMBDA(a, LET(n, LEN(a)-LEN(SUBSTITUTE(a, "X", "")), m, -MAP(BASE(SEQUENCE(10^n)-1, 10, n), LAMBDA(b, -TEXTJOIN(MID(b, SEQUENCE(n), 1), , TEXTSPLIT(0&a, "X")))), ARRAYTOTEXT(FILTER(m, MOD(m^0.5, 1)=0, "NP")))))Excel solution 10 for Complete Square by X Placeholder, proposed by Sunny Baggu:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
a,
LET(
_a,
REDUCE(
a,
SEQUENCE(
4
),
LAMBDA(
a,
v,
VSTACK(
a,
TOCOL(
SUBSTITUTE(
a,
"X",
SEQUENCE(
,
10,
0
),
1
)
)
)
)
),
_b,
SQRT(
_a
) = INT(
SQRT(
_a
)
),
ARRAYTOTEXT(
UNIQUE(
TOCOL(
IF(
_b,
_a,
x
),
3
)
)
)
)
)
),
"NP"
)Excel solution 11 for Complete Square by X Placeholder, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,
LAMBDA(x,
LET(t,
"X",
n,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
t,
""
)
),
m,
REDUCE(
x,
SEQUENCE(
n
),
LAMBDA(
a,
b,
SUBSTITUTE(
a,
t,
MID(
BASE(
SEQUENCE(
10^n
)-1,
10,
n
),
b,
1
),
1
)
)
),
r,
m^0.5,
IFERROR(ARRAYTOTEXT(TOROW(m/(r=INT(
r
)),
2)),
"NP"))))Excel solution 12 for Complete Square by X Placeholder, proposed by Bilal Mahmoud kh.:
=IFERROR(MAP(A2:A10,LAMBDA(r,LET(a,TEXTJOIN(",",TRUE,MAP(SEQUENCE(10,,0),LAMBDA(x,TEXTJOIN(",",,MAP(SEQUENCE(10,,0),LAMBDA(y,TEXTJOIN(",",,MAP(SEQUENCE(10,,0),LAMBDA(z,TEXTJOIN(",",,MAP(SEQUENCE(10,,0),LAMBDA(n,LET(m,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(r,"X",x,1),"X",y,1),"X",z,1),"X",n,1),IF(INT(SQRT(--m))=SQRT(--m),m,"")))))))))))))),TEXTJOIN(",",,UNIQUE(TEXTSPLIT(a,,",")))))),"NP")Solving the challenge of Complete Square by X Placeholder with Python
Python solution 1 for Complete Square by X Placeholder, proposed by Konrad Gryczan, PhD:
import pandas as pd
import itertools
import math
path = "524 Fill in Digits to make Perfect Square.xlsx"
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B")
def find_square(x):
chars = list(x)
mapped_values = [(range(10) if ch == 'X' else [int(ch)]) for ch in chars]
combinations = itertools.product(*mapped_values)
result = [
int(''.join(map(str, combo)))
for combo in combinations
if math.isqrt(int(''.join(map(str, combo)))) ** 2 == int(''.join(map(str, combo)))
]
return result[0] if len(result) == 1 else (', '.join(map(str, result)) if len(result) > 1 else "NP")
input['Perfect Square'] = input['Numbers'].apply(find_square)
print(input)
print(test)
Solving the challenge of Complete Square by X Placeholder with Python in Excel
Python in Excel solution 1 for Complete Square by X Placeholder, proposed by Alejandro Campos:
import math
def f(n):return math.isqrt(n)**2==n
def g(s):
r,p=[],[i for i,c in enumerate(s)if c=='X']
for i in range(10**len(p)):
x=list(s);d=str(i).zfill(len(p))
for j,k in zip(p,d):x[j]=k
if f(int(''.join(x))):r.append(''.join(x))
return r or['NP']
n=xl("A2:A10")[0]
df=pd.DataFrame([(x,', '.join(g(x)))for x in n],columns=['Número','Resultados'])
Solving the challenge of Complete Square by X Placeholder with R
R solution 1 for Complete Square by X Placeholder, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/524 Fill in Digits to make Perfect Square.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
find_square = function(x) {
result = x %>%
strsplit("") %>%
.[[1]] %>%
map(~ if (.x == "X") 0:9 else as.numeric(.x)) %>%
expand.grid() %>%
unite(num, everything(), sep = "") %>%
mutate(num = as.numeric(num)) %>%
filter(sqrt(num) == floor(sqrt(num))) %>%
pull(num)
if (length(result) == 0) return("NP")
else if (length(result) == 1) return(as.character(result))
else return(paste(result, collapse = ", "))
}
output = input %>%
mutate(`Answer Expected` = map_chr(Numbers, find_square)) %>%
select(-Numbers) %>%
bind_cols(test)
print(output)
&&