List first 500 numbers (skip single digit numbers) where number is a perfect square and also sum of squares of its digits is also a perfect square. Ex. 4225 : Square root is 65, hence perfect square. Sum of square of its digits = 4^2+2^2+2^2+5^2 = 49. Square root is 7, hence this is also a perfect square.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 508
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Perfect Square with Digit-Square Sum with Power Query
Power Query solution 1 for Perfect Square with Digit-Square Sum, proposed by Aditya Kumar Darak 🇮🇳:
let
Generate = List.Generate(
() => [a = 3, c = false, z = 0],
each [z] <= 500,
each [
a = [a] + 1,
sq = a * a,
t = Text.From(sq),
l = Text.ToList(t),
g = List.Transform(l, (f) => Number.Power(Number.From(f), 2)),
s = Number.Sqrt(List.Sum(g)),
c = Int64.From(s) = s,
z = [z] + Number.From(c)
],
each if [c] then [sq] else null
),
Return = List.RemoveNulls(Generate)
in
ReturnPower Query solution 2 for Perfect Square with Digit-Square Sum, proposed by Abdallah Ally:
let
IsPerfectSumSquare = (num) =>
let
sumDigitsSquare = List.Sum(
List.Transform(Text.ToList(Text.From(num)), each Number.Power(Number.FromText(_), 2))
),
cond = Number.RoundDown(Number.Sqrt(sumDigitsSquare))
= Number.Round(Number.Sqrt(sumDigitsSquare), 9)
in
cond,
GenerateNumbers = (n, end) =>
let
Source = List.Transform(List.Buffer({10 .. end}), each _ * _),
FilteredNums = List.Select(Source, each IsPerfectSumSquare(_)),
Result =
if List.Count(FilteredNums) >= n then
List.FirstN(FilteredNums, n)
else
@GenerateNumbers(n, end * 10)
in
Result,
Numbers = GenerateNumbers(500, 1000)
in
NumbersSolving the challenge of Perfect Square with Digit-Square Sum with Excel
Excel solution 1 for Perfect Square with Digit-Square Sum, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(SEQUENCE(12250,,4)^2,LAMBDA(n,n/(MOD(SUM((0&MID(n,SEQUENCE(9),1))^2)^0.5,1)=0))),3)Excel solution 2 for Perfect Square with Digit-Square Sum, proposed by Rick Rothstein:
=TOCOL(MAP(SEQUENCE(12248,,10)^2,LAMBDA(x,LET(n,SUM(MID(x,SEQUENCE(LEN(x)),1)^2),h,n^0.5,IF(h=INT(h),x,1/0)))),3)Excel solution 3 for Perfect Square with Digit-Square Sum, proposed by محمد حلمي:
=TOCOL(MAP(SEQUENCE(
12250,
,
9
)^2,
LAMBDA(a,
a/(
MOD(
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2
)^0.5,
1
)=0))),
2)Excel solution 4 for Perfect Square with Digit-Square Sum, proposed by محمد حلمي:
=TOCOL(MAP(SEQUENCE(
12250
)+9,
LAMBDA(a,
a^2/(MOD(
SUM(
MID(
a^2,
SEQUENCE(
LEN(
a^2
)
),
1
)^2
)^0.5,
1
)=0))),
2)Excel solution 5 for Perfect Square with Digit-Square Sum, proposed by Julian Poeltl:
=TAKE(
LET(
S,
SEQUENCE(
12250,
,
10
),
SS,
S^2,
SD,
SQRT(
MAP(
SS,
LAMBDA(
A,
SUM(
--MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)^2
)
)
)
),
FILTER(
SS,
INT(
SD
)=SD
)
),
500
)Excel solution 6 for Perfect Square with Digit-Square Sum, proposed by Timothée BLIOT:
=LET(A,SEQUENCE(10^5,,10)^2,B,MAP(A,LAMBDA(x,SUM((MID(x,SEQUENCE(LEN(x)),1))^2))),TAKE(FILTER(A,B^0.5=INT(B^0.5)),500))Excel solution 7 for Perfect Square with Digit-Square Sum, proposed by Duy Tùng:
=TOCOL(MAP(SEQUENCE(15000,,10)^2,LAMBDA(s,LET(a,SUM(REGEXEXTRACT(s,".",1)^2),s/(MOD(a,a^0.5)=0)))),3)Excel solution 8 for Perfect Square with Digit-Square Sum, proposed by Sunny Baggu:
=LET(
_s, SEQUENCE(12249, , 4),
FILTER(
_s,
MAP(
_s,
LAMBDA(k,
LET(
n, k ^ 2,
a, SUM(MID(n, SEQUENCE(LEN(n)), 1) ^ 2),
SQRT(a) = INT(SQRT(a))
)
)
)
) ^ 2
)Excel solution 9 for Perfect Square with Digit-Square Sum, proposed by LEONARD OCHEA 🇷🇴:
=LET(
s,
SEQUENCE(
12240,
,
10
)^2,
t,
SEQUENCE(
,
9
),
m,
MMULT(
IFERROR(
MID(
s,
t,
1
)^2,
0
),
TOCOL(
t
)^0
),
n,
m^0.5,
TOCOL(
IF(
n=INT(
n
),
s,
z
),
3
)
)Excel solution 10 for Perfect Square with Digit-Square Sum, proposed by Anshu Bantra:
= 10
lst = []
while len(lst) < 500:
sq_ = num**(2)
digits_ = sum([int(_)**2 for _ in str(sq_)])**(1/2)
if digits_.is_integer():
lst.append(sq_)Excel solution 11 for Perfect Square with Digit-Square Sum, proposed by Bilal Mahmoud kh.:
=LET(
n,
SEQUENCE(
13000,
,
10
),
p,
POWER(
n,
2
),
m,
MAP(
p,
LAMBDA(
x,
LET(
a,
SUM(
POWER(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
2
)
),
b,
SQRT(
a
),
INT(
b
)=b
)
)
),
FILTER(
p,
m
)
)Excel solution 12 for Perfect Square with Digit-Square Sum, proposed by Imam Hambali:
=LET(
a,
SEQUENCE(
12500,
,
10
)^2,
b,
MAP(
a,
LAMBDA(
x,
SUM(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)^2
)^0.5
)
),
TAKE(FILTER(a,
(MOD(
b,
1
)=0) * (MOD(
a^0.5,
1
)=0)),
500)
)Excel solution 13 for Perfect Square with Digit-Square Sum, proposed by Eddy Wijaya:
=LET(
limit,500,
seqDB,SEQUENCE(13000,,10),
power2,POWER(seqDB,2),
textPower2,DROP(REDUCE(0,power2,LAMBDA(a,v,IFNA(VSTACK(
a,
LET(
power2SplittedDigit,POWER(TOROW(VALUE(MID(v,SEQUENCE(LEN(v)),1))),2),
sumSplittedDigit,SUM(power2SplittedDigit),
integerChecker,MOD(SQRT(sumSplittedDigit),1)=0,
HSTACK(integerChecker,v,sumSplittedDigit,power2SplittedDigit))),""))),1),
filtertextPower2,FILTER(textPower2,TAKE(textPower2,,1)=TRUE),
res,HSTACK(SEQUENCE(ROWS(filtertextPower2)),CHOOSECOLS(filtertextPower2,1,2)),
CHOOSECOLS(FILTER(res,TAKE(res,,1)<=limit),-1))Excel solution 14 for Perfect Square with Digit-Square Sum, proposed by Edwin Tisnado:
=TOCOL(
MAP(
ROW(
10:12250
)^2,
LAMBDA(
x,
x/ISERR(
SEARCH(
".",
SUM(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2
)^0.5
)
)
)
),
2
)Excel solution 15 for Perfect Square with Digit-Square Sum, proposed by Ricardo Alexis Domínguez Hernández:
=LET(b,
SEQUENCE(
100000
),
a,
MAP(
b^2,
LAMBDA(
x,
MOD(
SQRT(
SUM(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)^2
)
),
1
)
)
),
TAKE(FILTER(IF(a=0,
(a+1)*b^2,
0),
IF(a=0,
(a+1)*b^2,
0)>9),
500))Excel solution 16 for Perfect Square with Digit-Square Sum, proposed by Tyler Cameron:
=TOCOL(
MAP(
SEQUENCE(
12246,
,
4
)^2,
LAMBDA(
x,
IF(
MOD(
SUM(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2
)^0.5,
1
)=0,
x,
a
)
)
),
3
)Solving the challenge of Perfect Square with Digit-Square Sum with Python in Excel
Python in Excel solution 1 for Perfect Square with Digit-Square Sum, proposed by Alejandro Campos:
import math
def es_cuadrado_perfecto(n):
return math.isqrt(n) ** 2 == n
def suma_cuadrados_digitos(n):
return sum(int(digito) ** 2 for digito in str(n))
def encontrar_numeros_especiales(cantidad):
resultado = []
num = 10
while len(resultado) < cantidad:
if es_cuadrado_perfecto(num):
suma_cuadrados = suma_cuadrados_digitos(num)
if es_cuadrado_perfecto(suma_cuadrados):
resultado.append(num)
num += 1
return resultado
numeros_especiales = encontrar_numeros_especiales(100)
numeros_especiales
Show translation
Python in Excel solution 2 for Perfect Square with Digit-Square Sum, proposed by Abdallah Ally:
from itertools import islice, count
from math import isqrt, sqrt
def is_perfect_sum_square(num):
sum_digits_squre = sum([int(x) ** 2 for x in str(num)])
cond = isqrt(sum_digits_squre) == round(sqrt(sum_digits_squre), 9)
return cond
def generate_numbers(n):
end_num = 1000
while True:
nums = map(lambda x: x ** 2, range(10, end_num))
nums = list(filter(is_perfect_sum_square, nums))
if len(nums) >= n:
return nums[ : n]
else:
end_num *= 10
numbers = generate_numbers(500)
numbers
Solving the challenge of Perfect Square with Digit-Square Sum with R
R solution 1 for Perfect Square with Digit-Square Sum, proposed by Konrad Gryczan, PhD:
Excecution time - 0.11 sec
library(tidyverse)
library(readxl)
test = read_excel(path, range = "A1:A501")
is_perfect_square <- function(n) {
sqrt_n <- sqrt(n)
sqrt_n == floor(sqrt_n)
}
result <- integer(0)
i <- 1
while (length(result) < 500) {
if (nchar(as.character(test[i, 1])) > 1) {
if (is_perfect_square(test[i, 1]) && is_perfect_square(sum(as.numeric(strsplit(as.character(test[i, 1]), "")[[1]])^2))) {
result <- c(result, test[i, 1])
}
}
i <- i + 1
}
result = data.frame(Result = unlist(result))
identical(result$Result, test$`Answer Expected`)
# [1] TRUE
&&&