List the Insolite Numbers – A number is Insolite if it is divisible by both Sum of square of its digits AND Product of square of its digits Ex. 1122112 which is divisible by both 1^2+1^2+2^2+2^2+1^2+1^2+2^2 = 8 and =1^2*1^2*2^2*2^2*1^2*1^2*2^2 = 64
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 350
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Insolite Numbers with Power Query
Power Query solution 1 for Find Insolite Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
T = Text.From([Numbers]),
L = Text.ToList(T),
Sq = List.Transform(L, (f) => Number.Power(Number.From(f), 2)),
S = List.Sum(Sq),
P = List.Product(Sq),
R = Number.Mod([Numbers], S) = 0 and Number.Mod([Numbers], P) = 0
][R]
)
in
ReturnPower Query solution 2 for Find Insolite Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = Text.ToList(Text.From([Numbers])),
b = List.Transform(a, each Number.Power(Number.From(_), 2)),
c = List.Sum(b),
d = List.Product(b),
e = List.Transform({c, d}, (x) => Number.Mod([Numbers], x) = 0)
in
List.AllTrue(e)
)
in
SolPower Query solution 3 for Find Insolite Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [
a = List.Transform(Text.ToList(Text.From([Numbers])), each Number.Power(Number.From(_), 2)),
b = try Int64.From([Numbers] / List.Product(a)) = [Numbers] / List.Product(a) otherwise false,
c = try Int64.From([Numbers] / List.Sum(a)) = [Numbers] / List.Sum(a) otherwise false,
d = List.AllTrue({b} & {c})
][d]
)
in
resPower Query solution 4 for Find Insolite Numbers, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Filter = Table.SelectRows(
Source,
each [
Digits = List.Transform(Text.ToList(Number.ToText([Numbers])), Number.FromText),
Sq = List.Transform(Digits, (d) => d * d),
Sum = List.Sum(Sq),
Prod = List.Product(Sq),
Result = Number.Mod([Numbers], Sum) = 0 and Number.Mod([Numbers], Prod) = 0
][Result]
)
in
FilterPower Query solution 5 for Find Insolite Numbers, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.SelectRows(
Source,
each
let
T = Text.From([Numbers]),
a = Text.ToList(T),
b = List.Transform(a, each Number.Power(Number.From(_), 2)),
c = Number.Mod(Number.From(T), List.Sum(b)),
d = Number.Mod(Number.From(T), List.Product(b)),
e = c = 0 and d = 0
in
e
)
in
ResultSolving the challenge of Find Insolite Numbers with Excel
Excel solution 1 for Find Insolite Numbers, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,
LAMBDA(n,
LET(m,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)^2,
n/(n=LCM(
n,
SUM(
m
),
PRODUCT(
m
)
))))),
3)Excel solution 2 for Find Insolite Numbers, proposed by Rick Rothstein:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(x,
LET(d,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2,
(1-ISNUMBER(
FIND(
".",
x/SUM(
d
)
)
))*(1-ISNUMBER(
FIND(
".",
x/PRODUCT(
d
)
)
))))))Excel solution 3 for Find Insolite Numbers, proposed by John V.:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(n,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2,
b,
x^2/SUM(
n
)/PRODUCT(
n
),
x/(b=INT(
b
))))),
2)Excel solution 4 for Find Insolite Numbers, proposed by محمد حلمي:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2,
v,
a/HSTACK(
PRODUCT(
e
),
SUM(
e
)
),
a/AND(
v=INT(
v
)
)
)
)
),
2
)Excel solution 5 for Find Insolite Numbers, proposed by Julian Poeltl:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(N,
LET(S,
MID(
N,
SEQUENCE(
1,
LEN(
N
)
),
1
),
SSum,
SUM(
S^2
),
SP,
PRODUKT(
S^2
),
IFERROR(IF(AND((ROUND(
N/SSum,
0
)=N/SSum),
(ROUND(
N/SP,
0
)=N/SP)),
TRUE,
FALSE),
FALSE))))=TRUE)Excel solution 6 for Find Insolite Numbers, proposed by Timothée BLIOT:
=TOCOL(
IF(
MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
)^2,
B,
LAMBDA(
n,
INT(
n
)=n
),
C,
z/SUM(
A
),
D,
z/PRODUCT(
A
),
B(
C
)*B(
D
)
)
)
),
A2:A10,
1/0
),
3
)Excel solution 7 for Find Insolite Numbers, proposed by Sunny Baggu:
=TOCOL(
A2:A10 * 1 /
MAP(
A2:A10,
LAMBDA(
a,
LET(
_m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
) ^ 2,
_s,
SUM(
_m
),
_p,
PRODUCT(
_m
),
_r,
a / HSTACK(
_s,
_p
),
AND(
_r = INT(
_r
)
)
)
)
),
3
)Excel solution 8 for Find Insolite Numbers, proposed by Pieter de Bruijn:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(n,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2,
s,
a/SUM(
n
),
p,
a/PRODUCT(
n
),
a/(s-INT(
s
)+p-INT(
p
)=0)))),
2)Excel solution 9 for Find Insolite Numbers, proposed by Nicolas Micot:
=FILTRE(A2:A9;
MAP(A2:A9;
LAMBDA(l_nombre;
LET(_carres;
STXT(
l_nombre;
SEQUENCE(
NBCAR(
l_nombre
)
);
1
) ^ 2;
(MOD(
l_nombre;
SOMME(
_carres
)
) = 0)*SI(PRODUIT(
_carres
) = 0;
0;
(MOD(
l_nombre;
PRODUIT(
_carres
)
)=0)))))=1)Excel solution 10 for Find Insolite Numbers, proposed by Nicolas Micot:
=FILTRE(A2:A10;
MAP(A2:A10
;
LAMBDA(l_nombre;
LET(_carres;
STXT(
l_nombre;
SEQUENCE(
NBCAR(
l_nombre
)
);
1
) ^ 2;
(TRONQUE(
l_nombre/SOMME(
_carres
)
) = l_nombre/SOMME(
_carres
))*SI(PRODUIT(
_carres
) = 0;
0;
(TRONQUE(
l_nombre/PRODUIT(
_carres
)
)=l_nombre/PRODUIT(
_carres
))))))=1)Excel solution 11 for Find Insolite Numbers, proposed by Giorgi Goderdzishvili:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(
_nm,
x,
_cr,
1*MID(
_nm,
SEQUENCE(
,
LEN(
_nm
)
),
1
),
_sm,
SUM(
_cr^2
),
_ml,
PRODUCT(
_cr^2
),
_ch,
(LCM(
_nm,
_sm
) + LCM(
_nm,
_ml
))=2*_nm,
_nm/_ch))),
3)Excel solution 12 for Find Insolite Numbers, proposed by Edwin Tisnado:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2,
x/(x=LCM(
x,
SUM(
a
),
PRODUCT(
a
)
))))),
2)Excel solution 13 for Find Insolite Numbers, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
f,
LAMBDA(
n,
LET(
l,
LEN(
n
),
s,
SEQUENCE(
l
),
d,
MID(
n,
s,
1
),
AND(
INT(
n/SUM(
d^2
)
)=n/SUM(
d^2
),
INT(
n/PRODUCT(
d^2
)
)=n/PRODUCT(
d^2
)
)
)
),
FILTER(
A2:A10,
IFERROR(
MAP(
A2:A10,
LAMBDA(
x,
f(
x
)
)
),
0
)
)
)Excel solution 14 for Find Insolite Numbers, proposed by Gabriel Raigosa:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
c,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2,
LCM(
x,
SUM(
c
),
PRODUCT(
c
)
)=x
)
)
)
)
▶️ES:
=FILTRAR(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
c,
EXTRAE(
x,
SECUENCIA(
LARGO(
x
)
),
1
)^2,
M.C.M(
x,
SUMA(
c
),
PRODUCTO(
c
)
)=x
)
)
)
)Solving the challenge of Find Insolite Numbers with Python in Excel
Python in Excel solution 1 for Find Insolite Numbers, proposed by Alejandro Campos:
def es_insolito(numero):
digitos = [int(d) for d in str(numero)]
suma_cuadrados = sum(d ** 2 for d in digitos)
producto_cuadrados = 1
for d in digitos:
producto_cuadrados *= d ** 2
es_divisible_por_suma = (numero % suma_cuadrados == 0)
es_divisible_por_producto = (producto_cuadrados != 0 and numero % producto_cuadrados == 0)
return es_divisible_por_suma and es_divisible_por_producto
def verificar_numeros_insolitos(numeros):
resultados = []
for numero in numeros:
if es_insolito(numero):
resultados.append(numero)
return resultados
data = xl("A1:A10", headers=True)
numeros = data['Numbers'].tolist()
numeros_insolitos = verificar_numeros_insolitos(numeros)
df = pd.DataFrame({'Solución en PY': numeros_insolitos})
df
Show translation
Solving the challenge of Find Insolite Numbers with R
R solution 1 for Find Insolite Numbers, proposed by Krzysztof Nowak:
options(scipen = 9990)
IsInsolite <- function(x) {
X <- x
Y <- as.character(X)
Split <- as.numeric(unlist(str_split(Y, "")))
Sum_of_square <- sum(Split^2)
Product_of_square &<- prod(Split^2)
result <- X %% Sum_of_square == 0 && X %% Product_of_square == 0
return(result)
}
Answer <- df |>
filter(map(Numbers,IsInsolite) == TRUE)
Answer
&&