A Pythagorean quadruple is a set of 4 integers a, b, c, d such that d^2 = a^2+b^2+c^2. Out of a, b, c & d, one is marked as X in given set of data. Work out X for the given set of data. Note – Data is not sorted, so a, b, c and d can appear any where in the data.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 429
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Missing Pythagorean Quadruple with Power Query
Power Query solution 1 for Find Missing Pythagorean Quadruple, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer",
each [
S = Text.Split([Number], ", "),
X = List.RemoveItems(S, {"X"}),
N = List.Transform(X, (f) => Number.Power(Number.From(f), 2)),
M = List.Max(N),
C1 = List.Sum(N),
C2 = Number.Sqrt(2 * M - C1),
TF = Number.Mod(C2, 1) = 0,
R = if TF then C2 else Number.Sqrt(C1)
][R]
)
in
AnswerPower Query solution 2 for Find Missing Pythagorean Quadruple, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
a = Text.Split([Number], ", "),
b = List.Select(a, each try Number.From(_) is number otherwise false),
c = List.Transform(List.Transform(b, Number.From), each _ * _),
d = List.Sum(c),
e = List.Max(c),
f = Number.Sqrt(d),
g = if f = Number.RoundDown(f) then f else Number.Sqrt(2 * e - d)
in
g
)
in
SolPower Query solution 3 for Find Missing Pythagorean Quadruple, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
n = [Number],
a = Text.Split(n, ", "),
b = List.Transform(List.Select(a, each _ <> "X"), Number.From),
c = List.Transform(b, each _ * _),
d = List.Max(c),
e = List.Select(c, each _ <> List.Max(c)),
f = List.Generate(
() => [i = 0],
each [i] < List.Max(b),
each [i = [i] + 1],
each e & {[i] * [i]}
),
g = List.Select(f, each List.Sum(_) = d),
h = Number.Sqrt(List.Last(List.Combine(g))),
i = if h = null then Number.Sqrt(List.Sum(c)) else h
in
i
)
in
SolSolving the challenge of Find Missing Pythagorean Quadruple with Excel
Excel solution 1 for Find Missing Pythagorean Quadruple, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
p,
TEXTSPLIT(
a,
"X",
", ",
1
)^2,
s,
SUM(
p
),
IF(
MOD(
s^0.5,
1
),
MAX(
p
)*2-s,
s
)
)
)
)Excel solution 2 for Find Missing Pythagorean Quadruple, proposed by Rick Rothstein:
=MAP(A2:A10,
LAMBDA(x,
LET(t,
TOROW(
TEXTSPLIT(
x,
","
)^2,
2
),
s,
SUM(
t
),
q,
s^0.5,
IF(MOD(
q,
1
),
(2*MAX(
t
)-s)^0.5,
q))))Excel solution 3 for Find Missing Pythagorean Quadruple, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
SORT(
IFERROR(
-TEXTSPLIT(
x,
,
","
),
)
)^2,
b,
SUM(
n
),
c,
2*@n-b,
IFERROR(
IF(
MOD(
c^0.5,
1
),
b,
c
),
b
)^0.5
)
)
)Excel solution 4 for Find Missing Pythagorean Quadruple, proposed by محمد حلمي:
=MAP(A2:A10,
LAMBDA(a,
LET(e,
TOCOL(
TEXTSPLIT(
a,
","
)^2,
2
),
s,
SUM(
e
),
c,
(2*MAX(
e
)-s)^0.5,
IFERROR(
IFS(
c=INT(
c
),
c
),
s^0.5
))))Excel solution 5 for Find Missing Pythagorean Quadruple, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
SORT(
TOCOL(
TEXTSPLIT(
x,
,
", "
)^2,
3
),
,
-1
),
b,
(@a-SUM(
TAKE(
a,
-2
)
))^0.5,
c,
SUM(
a
)^0.5,
IFERROR(
IF(
INT(
b
)=b,
b,
c
),
c
))))Excel solution 6 for Find Missing Pythagorean Quadruple, proposed by Julian Poeltl:
=MAP(A2:A10,
LAMBDA(N,
LET(SP,
TEXTSPLIT(
N,
", "
)*1,
IN,
FILTER(
SP,
ISNUMBER(
SP
)
),
M,
MAX(
IN
),
S,
SUM(
IN^2
),
Mo,
MOD(
SQRT(
S
),
SEQUENCE(
100
)
)=0,
R,
IF(SUM(
--Mo
)=0,
SQRT((M^2-SUM(
FILTER(
IN,
INExcel solution 7 for Find Missing Pythagorean Quadruple, proposed by Timothée BLIOT:
=MAP(A2:A10,
LAMBDA(z,
LET(A,
--TOCOL(
REGEXEXTRACT(
z,
"d+",
1
)
),
B,
SUM(
A^2
)^0.5,
IF(B=INT(
B
),
B,
(MAX(
A
)^2-SUM((DROP(
SORT(
A
),
-1
))^2))^0.5))))Excel solution 8 for Find Missing Pythagorean Quadruple, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
y,
LET(
a,
TEXTSPLIT(
y,
", "
),
b,
--FILTER(
a,
a<>"X"
)^2,
c,
SUM(
b
),
d,
SQRT(
c
),
IF(
d=INT(
d
),
d,
SQRT(
2*MAX(
b
)-c
)
)
)
)
)Excel solution 9 for Find Missing Pythagorean Quadruple, proposed by Duy Tùng:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
TOCOL(
TEXTSPLIT(
x,
,
", "
)^2,
2
),
b,
ABS(
2*MAX(
a
)-SUM(
a
)
)^0.5,
IF(
b=INT(
b
),
b,
SUM(
a
)^0.5
)
)
)
)Excel solution 10 for Find Missing Pythagorean Quadruple, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
_ts,
TEXTSPLIT(
a,
,
", "
),
_n,
TOCOL(
--_ts,
3
),
_ss,
SQRT(
SUM(
_n ^ 2
)
),
IF(
INT(
_ss
) = _ss,
_ss,
SQRT(
MAX(
_n
) ^ 2 - SUMSQ(
FILTER(
_n,
_n <> MAX(
_n
)
)
)
)
)
)
)
)Excel solution 11 for Find Missing Pythagorean Quadruple, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,
LAMBDA(a,
LET(m,
SORT(
TOCOL(
--TEXTSPLIT(
a,
", "
),
3
)
),
n,
BYCOL(
{1,
-1;1,
-1;1,
1}*m^2,
SUM
)^0.5,
TOROW(n/(n=INT(
n
)),
2))))Excel solution 12 for Find Missing Pythagorean Quadruple, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(A2:A10,
LAMBDA(f,
LET(t,
SORT(
TEXTSPLIT(
f,
"X",
", ",
1
)^2,
,
-1
),
p,
SUM(
t
)^0.5,
IF(INT(
p
)=p,
p,
(2*@t-p^2)^0.5))))Excel solution 13 for Find Missing Pythagorean Quadruple, proposed by Charles Roldan:
=MAP(A2:A10,
LAMBDA(x,
LAMBDA(
y,
XLOOKUP(
0,
y - INT(
y
),
y
)
)(SQRT(MMULT(REDUCE(SEQUENCE(
10,
,
0
),
{0,
0},
LAMBDA(y,
_,
LAMBDA(
y,
FILTER(
y,
LEN(
y
)
)
)(TEXTSPLIT(
x,
y
)))) ^ 2,
{1,
-1,
-1,
1;-1,
1,
-1,
1;-1,
-1,
1,
1})))))Excel solution 14 for Find Missing Pythagorean Quadruple, proposed by Bilal Mahmoud kh.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
IFERROR(
--TEXTSPLIT(
x,
","
),
0
),
b,
MAX(
a
),
c,
POWER(
b,
2
),
d,
POWER(
LARGE(
a,
2
),
2
)+POWER(
LARGE(
a,
3
),
2
),
e,
c-d,
f,
IF(
e<0,
SQRT(
SUM(
POWER(
a,
2
)
)
),
SQRT(
e
)
),
f
)
)
)Excel solution 15 for Find Missing Pythagorean Quadruple, proposed by Nicolas Micot:
=LET(
_eclat;
FRACTIONNER.TEXTE(
A2;
;
", "
);
_nombres;
TRIER(
FILTRE(
_eclat;
_eclat<>"X"
)+0;
;
-1
);
_sommeCarree;
SOMME(
_nombres^2
);
SI(
RACINE(
_sommeCarree
)=ENT(
RACINE(
_sommeCarree
)
);
RACINE(
_sommeCarree
);
RACINE(
PRENDRE(
_nombres;
1
)^2-SOMME(
PRENDRE(
_nombres;
-2
)^2
)
)
)
)Excel solution 16 for Find Missing Pythagorean Quadruple, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
TOROW(
--TEXTSPLIT(
x,
", "
),
3
),
r,
SQRT(
SUM(
n^2
)
),
IF(
r=INT(
r
),
r,
SQRT(
MAX(
n
)^2-SUM(
SMALL(
n,
{1;2}
)^2
)
)
)
)
)
)Excel solution 17 for Find Missing Pythagorean Quadruple, proposed by Ernesto Vega Castillo:
=BYROW(
A2:A10,
LAMBDA(
data,
LET(
num,
POWER(
SUBSTITUTE(
TEXTSPLIT(
data,
,
","
),
"X",
0
),
2
),
_sum,
SUM(
num
),
raiz,
SQRT(
ABS(
_sum-MAX(
num
)*2
)
),
IF(
ROUNDUP(
raiz,
0
)=raiz,
raiz,
SQRT(
_sum
)
)
)
)
)Excel solution 18 for Find Missing Pythagorean Quadruple, proposed by Gabriel Raigosa:
=MAP(A2:A10,LAMBDA(x,LET(d,IFERROR(TEXTSPLIT(x,",")*1,0),o,SORT(d,,-1,1),r,SQRT(SUM(o^2)),IF(INT(r)=r,r,SQRT(ABS(MAX(o)^2+SUM(-(DROP(o,,1)^2))))))))
🔹ES: =MAP(A2:A10,LAMBDA(x,LET(d,SI.ERROR(DIVIDIRTEXTO(x,",")*1,0),o,ORDENAR(d,,-1,1),r,RAIZ(SUMA(o^2)),SI(ENTERO(r)=r,r,RAIZ(ABS(MAX(o)^2+SUMA(-(EXCLUIR(o,,1)^2))))))))Excel solution 19 for Find Missing Pythagorean Quadruple, proposed by Tyler Cameron:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
a,
SORT(
TOCOL(
--TEXTSPLIT(
t,
","
),
3
),
,
-1
)^2,
b,
SQRT(
SUM(
a
)
),
IF(
MOD(
b,
1
)=0,
b,
SQRT(
REDUCE(
MAX(
a
),
DROP(
a,
1
),
LAMBDA(
x,
y,
x-y
)
)
)
)
)
)
)Excel solution 20 for Find Missing Pythagorean Quadruple, proposed by Ben Gutscher:
=MAP(A2:A10,
LAMBDA(r,
LET(nums,
SORT(IFERROR(--(TEXTSPLIT(
r,
,
", "
)),
"X"),
,
-1),
a,
INDEX(
nums,
2
),
b,
INDEX(
nums,
3
),
c,
INDEX(
nums,
4
),
d,
SQRT(
a^2+b^2+c^2
),
IF(
MOD(
d,
1
)=0,
d,
SQRT(
a^2-b^2-c^2
)
))))Excel solution 21 for Find Missing Pythagorean Quadruple, proposed by Peter Compton:
=LET(
answer1,
BYROW(
A2:D10,
LAMBDA(
nos,
SQRT(
SUM(
SUBSTITUTE(
nos,
"x",
0
)^2
)
)
)
),
answer2,
BYROW(
A2:D10,
LAMBDA(
y,
SQRT(
MAX(
VALUE(
DROP(
SORT(
TRANSPOSE(
SUBSTITUTE(
y,
"x",
0
)
)
),
1
)
)
)^2-SUM(
SMALL(
VALUE(
DROP(
SORT(
TRANSPOSE(
SUBSTITUTE(
y,
"x",
0
)
)
),
1
)
),
SEQUENCE(
2
)
)^2
)
)
)
),
final,
IF(
MOD(
answer1,
1
)>0,
answer2,
answer1
),
final
)Excel solution 22 for Find Missing Pythagorean Quadruple, proposed by Peter Compton:
=LET(
data,
VALUE(
SUBSTITUTE(
TEXTSPLIT(
TEXTJOIN(
CHAR(
10
),
,
A16:A24
),
",",
CHAR(
10
)
),
"X",
0
)
),
answer1,
BYROW(
data,
LAMBDA(
x,
SQRT(
SUM(
DROP(
SORT(
TRANSPOSE(
x
)
),
1
)^2
)
)
)
),
answer2,
BYROW(
data,
LAMBDA(
x,
SQRT(
MAX(
x
)^2-SUM(
DROP(
SMALL(
x,
SEQUENCE(
3
)
),
1
)^2
)
)
)
),
IF(
MOD(
answer1,
1
)>0,
answer2,
answer1
)
)Solving the challenge of Find Missing Pythagorean Quadruple with Python
Python solution 1 for Find Missing Pythagorean Quadruple, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
import math
input = pd.read_excel("429 Pythagorean Quadruples.xlsx", sheet_name="Sheet1", usecols="A", nrows=10)
test = pd.read_excel("429 Pythagorean Quadruples.xlsx", sheet_name="Sheet1", usecols="B", nrows=10)
def find_quadr_solution(sides):
sides = sides.replace("X", "0")
numbers = np.array([float(num) for num in sides.split(", ") if num != 'NA'])
numbers = numbers[numbers != 0]
# Calculate missing side assuming d is missing
missing1 = np.sqrt(np.sum(numbers**2))
# Calculate missing side assuming a, b, or c is missing
pot_d = np.max(numbers)
others = numbers[numbers != pot_d]
missing2 = np.sqrt(pot_d**2 - np.sum(others**2))
# Check if missing side is an integer
if missing1 == np.floor(missing1):
missing = missing1
elif missing2 == np.floor(missing2):
missing = missing2
else:
missing = np.nan
missing = np.int64(missing)
return missing
result = input.copy()
result["r"] = result["Number"].apply(find_quadr_solution)
print(result["r"].equals(test["Answer Expected"])) # True
Python solution 2 for Find Missing Pythagorean Quadruple, proposed by Luan Rodrigues:
import pandas as pd
import math
file_path = 'Excel_Challenge_429 - Pythagorean Quadruples.xlsx'
df = pd.read_excel(file_path, usecols='A')
df['Number'] = df['Number'].apply(lambda x: x.split(", "))
dfs_concatenados = []
for i in range(df.shape[0]):
a = df['Number'][i]
for i in a:
if i[-1] == 'X':
a.remove(i)
else:
a
b = [int(i)**2 for i in a ]
c = sum(b)
d = max(b)
e = math.sqrt(c)
if e == math.floor(e):
res = e
else:
res = math.sqrt(2*d-c)
dfs_concatenados.append(res)
print( res )
Solving the challenge of Find Missing Pythagorean Quadruple with Python in Excel
Python in Excel solution 1 for Find Missing Pythagorean Quadruple, proposed by Abdallah Ally:
import pandas as pd
from math import sqrt
import re
file_path = 'Excel_Challenge_429 - Pythagorean Quadruples.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
# Perform data transformation and cleansing
def pythagorean_quadruples(col):
number = 0
result = -1
while True:
values = [int(x) for x in re.sub(r'X, |, X', '', col).split(', ')]
squares = [x ** 2 for x in values]
if number == sqrt(sum(squares)):
result = number
break
for i in range(len(values)):
if values[i] == sqrt(sum([x ** 2 for x in (values[: i] + values[i + 1: ])] + [number ** 2])):
result = number
break
if number == result:
break
number += 1
return result
df['My Answer'] = df['Number'].apply(pythagorean_quadruples)
print(f'nMy Results:n{df}')
Python in Excel solution 2 for Find Missing Pythagorean Quadruple, proposed by Owen Price:
Python NumPy conversion of Rick's formula:
inputs = np.array(xl("A2:A10")[0].str.split(", "))
inputs = np.array([[int(x) for x in row if x.isnumeric()]
for row in inputs])
squares = inputs**2
sums_of_squares = squares.sum(axis=1)
squares_of_missing = np.where(
np.mod(np.sqrt(sums_of_squares),1) != 0,
2*np.max(squares, axis=1)-sums_of_squares,
sums_of_squares)
np.sqrt(squares_of_missing)
Solving the challenge of Find Missing Pythagorean Quadruple with R
R solution 1 for Find Missing Pythagorean Quadruple, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/429 Pythagorean Quadruples.xlsx", range = "A1:A10")
test = read_excel("Excel/429 Pythagorean Quadruples.xlsx", range = "B1:B10")
numbers = str_split(sides, ", ")[[1]] %>%
as.numeric() %>%
na.omit()
missing1 = sqrt(sum(numbers^2)) # if d side is missing
pot_d = max(numbers)
others = numbers[numbers != pot_d]
missing2 = sqrt(pot_d^2 - sum(others^2)) # if a, b or c side is missing
if (missing1 == floor(missing1)) {
missing = missing1
} else if (missing2 == floor(missing2)) {
missing = missing2
} else {
missing = NA
}
return(missing)
}
result = input %>%
mutate(r = map_dbl(Number, find_quadr_solution))
&&