A right angle triangle is that for whom following property is satisfied. a^2+b^2=c^2 where a, b and c are its 3 sides. You need to find missing side of a Right Angle Triangle which has to be Integer. If Side1=4 and Side2 = 5, then missing side is 3 as 3^2+4^2=5^2. If it doesn’t satisfy right angle property, populate “NA”
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 21
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Missing Triangle Side Integer with Power Query
Power Query solution 1 for Missing Triangle Side Integer, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Result = Table.AddColumn(
Source,
"Result",
each [
a = Number.Power([Side1], 2),
b = Number.Power([Side2], 2),
c = Number.Sqrt(a + b),
d = Number.Sqrt(b - a),
e = if Number.Mod(c, 1) = 0 then c else if Number.Mod(d, 1) = 0 then d else "NA"
][e]
)
in
ResultPower Query solution 2 for Missing Triangle Side Integer, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
RightTriangleRaw,
{{"Side1", Int64.Type}, {"Side2", Int64.Type}}
),
Side3 = Table.AddColumn(
Source,
"Side3",
each (Number.Sqrt(([Side2] * [Side2]) - ([Side1] * [Side1])))
),
Side3aT = Table.AddColumn(
Side3,
"Side3a",
each (Number.Sqrt(([Side2] * [Side2]) + ([Side1] * [Side1])))
),
ExpectedAnswer = Table.RemoveColumns(
Table.AddColumn(
Side3aT,
"Expected Answer",
each
if Number.Mod(_[Side3], 1) = 0 then
_[Side3]
else if Number.Mod(_[Side3a], 1) = 0 then
_[Side3a]
else
"NA"
),
{"Side3", "Side3a"}
)
in
ExpectedAnswerPower Query solution 3 for Missing Triangle Side Integer, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Triangle"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Side3",
each
let
c2 = Number.Power([Side2], 2),
b2 = Number.Power([Side1], 2),
a = Number.Sqrt(c2 - b2)
in
if a = Int64.From(a) then
a
else
let
a2 = Number.Power([Side1], 2),
b2 = Number.Power([Side2], 2),
c = Number.Sqrt(a2 + b2)
in
if c = Int16.From(c) then c else "NA"
)
in
#"Added Custom"Power Query solution 4 for Missing Triangle Side Integer, proposed by Venkata Rajesh:
let
Source = Data,
Side3 = Table.AddColumn(
Source,
"Side",
each
let
x = Number.Power([Side2], 2),
y = Number.Power([Side1], 2),
a = Number.Sqrt(x + y),
b = Number.Sqrt(x - y)
in
if Number.Round(b, 0) - b = 0 then b else if Number.Round(a, 0) - a = 0 then a else "NA"
)
in
Side3Power Query solution 5 for Missing Triangle Side Integer, proposed by Khawar Malik:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each
if Number.Mod(Number.Sqrt(Number.Power([Side2], 2) - Number.Power([Side1], 2)), 1) = 0 then
Number.Sqrt(Number.Power([Side2], 2) - Number.Power([Side1], 2))
else if Number.Mod(Number.Sqrt(Number.Power([Side2], 2) + Number.Power([Side1], 2)), 1) = 0 then
Number.Sqrt(Number.Power([Side2], 2) + Number.Power([Side1], 2))
else
"NA"
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Side1", "Side2"})
in
#"Removed Columns"Solving the challenge of Missing Triangle Side Integer with Excel
Excel solution 1 for Missing Triangle Side Integer, proposed by Rick Rothstein:
=LET(
A,
A2:A10,
B,
B2:B10,
SP,
SQRT(
A*A+B*B
),
SM,
SQRT(
B*B-A*A
),
IF(
ISERROR(
FIND(
".",
SP
)
),
SP,
IF(
ISERROR(
FIND(
".",
SM
)
),
SM,
"NA"
)
)
)Excel solution 2 for Missing Triangle Side Integer, proposed by John V.:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(r,
(b^2+a^2*{-1;1})^.5,
XLOOKUP(
0,
MOD(
r,
1
),
r,
"NA"
))))Excel solution 3 for Missing Triangle Side Integer, proposed by محمد حلمي:
=BYROW(
A2:B10;
LAMBDA(
s;
LET(
a;
SQRT(
SUMSQ(
s
)
);
b;
SQRT(
ABS(
INDEX(
s;
;
1
)^2-INDEX(
s;
;
2
)^2
)
);
IF(
a=INT(
a
);
a;
IF(
b=INT(
b
);
b;
"NA"
)
)
)
)
)Excel solution 4 for Missing Triangle Side Integer, proposed by Julian Poeltl:
=BYROW(
A2:B10,
LAMBDA(
A,
LET(
L,
SQRT(
INDEX(
A,
,
1
)^2+INDEX(
A,
,
2
)^2
),
S,
SQRT(
MAX(
A
)^2-MIN(
A
)^2
),
IFS(
INT(
L
)=L,
L,
INT(
S
)=S,
S,
1,
"NA"
)
)
)
)Excel solution 5 for Missing Triangle Side Integer, proposed by Alejandro Campos:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
IF(
MOD(
SQRT(
x ^ 2 + y ^ 2
),
1
) = 0,
SQRT(
x ^ 2 + y ^ 2
),
IF(
MOD(
SQRT(
ABS(
x ^ 2 - y ^ 2
)
),
1
) = 0,
SQRT(
ABS(
x ^ 2 - y ^ 2
)
),
"NA"
)
)
)
)Excel solution 6 for Missing Triangle Side Integer, proposed by Aditya Kumar Darak 🇮🇳:
= BYROW(
data,
LAMBDA(
a,
LET(
_tbl,
HSTACK(
SQRT(
SUM(
a ^ 2
)
),
SQRT(
MAX(
a
) ^ 2 - MIN(
a
) ^ 2
)
),
XLOOKUP(
0,
MOD(
_tbl,
1
),
_tbl,
"NA"
)
)
)
)Excel solution 7 for Missing Triangle Side Integer, proposed by Timothée BLIOT:
=LET(
a,
MIN(
A2:B2
),
bc,
MAX(
A2:B2
),
number1,
((a^2)+(bc^2))^(1/2),
number2,
(-((a^2)-(bc^2))^(1/2)),
IF(
ROUND(
number1,
0
)=number1,
number1,
IF(
ROUND(
number2,
0
)=number2,
number2,
"NA"
)
))Excel solution 8 for Missing Triangle Side Integer, proposed by Oscar Mendez Roca Farell:
=IFERROR(AGGREGATE(14;
6;
SQRT(
B2^2+{1;
-1}*A2^2
)/(MOD(
SQRT(
B2^2+{1;
-1}*A2^2
);
1
)=0);
1);
"NA")Excel solution 9 for Missing Triangle Side Integer, proposed by Bhavya Gupta:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
S_1,
S_2,
LET(
a,
VSTACK(
SQRT(
S_1^2+S_2^2
),
SQRT(
ABS(
S_1^2-S_2^2
)
)
),
XLOOKUP(
0,
MOD(
a,
1
),
a
)
)
)
)Excel solution 10 for Missing Triangle Side Integer, proposed by Charles Roldan:
=BYROW(SQRT(ABS(MMULT(A2:B10^2,{1,1;-1,1}))),LAMBDA(x,REDUCE("NA",x,LAMBDA(a,b,IF(MOD(b,1),a,b)))))Excel solution 11 for Missing Triangle Side Integer, proposed by Owen Price:
=LET(
a,
A2:A10^2,
b,
B2:B10^2,
f,
LAMBDA(
num,
LET(
x,
SQRT(
ABS(
num
)
),
IF(
INT(
x
)=x,
x,
0
)
)
),
BYROW(
HSTACK(
f(
a+b
),
f(
a-b
)
),
LAMBDA(
r,
LET(
y,
MAX(
r
),
IF(
y=0,
"NA",
y
)
)
)
)
)Excel solution 12 for Missing Triangle Side Integer, proposed by Amardeep Singh:
=LET(
h,
SQRT(
SUMSQ(
A2:B2
)
),
s,
SQRT(
ABS(
A2^2 - B2^2
)
),
IFS(
MOD(
h,
1
)=0,
h,
MOD(
s,
1
)=0,
s,
TRUE,
"NA"
)
)Excel solution 13 for Missing Triangle Side Integer, proposed by Juliano Santos Lima:
=IF(
MOD(
SQRT(
B2:B10^2-A2:A10^2
),
1
)=0,
SQRT(
B2:B10^2-A2:A10^2
),
"NA"
)Excel solution 14 for Missing Triangle Side Integer, proposed by Nazmul Islam Jobair:
=BYROW(
A2:B10,
LAMBDA(
_nums,
LET(
_large,
MAX(
_nums
),
_small,
MIN(
_nums
),
_hyp,
SQRT(
_large^2+_small^2
),
_side,
SQRT(
_large^2-_small^2
),
SWITCH(
TRUE,
MOD(
_hyp,
1
)=0,
_hyp,
MOD(
_side,
1
)=0,
_side,
"NA"
)
)
)
)Solving the challenge of Missing Triangle Side Integer with Python in Excel
Python in Excel solution 1 for Missing Triangle Side Integer, proposed by Alejandro Campos:
df = xl("A1:B10", headers=True)
def compute_value(s1, s2):
a = np.array([np.sqrt(s1**2 + s2**2), np.sqrt(abs(s1**2 - s2**2))])
mod_a = np.mod(a, 1)
if np.any(mod_a == 0):
return a[np.where(mod_a == 0)][0]
else:
return 'NA'
df['Result'] = df.apply(lambda row: compute_value(row['Side1'], row['Side2']), axis=1)
df
Solving the challenge of Missing Triangle Side Integer with DAX
DAX solution 1 for Missing Triangle Side Integer, proposed by Zoran Milokanović:
EVALUATE
ADDCOLUMNS(Input, "Answer Expected",
VAR A = Input[Side1]
VAR B = Input[Side2]
VAR C = SQRT(B * B - A * A)
VAR D = SQRT(B * B + A * A)
RETURN
SWITCH(
TRUE(),
MOD(C, 1) = 0, C,
MOD(D, 1) = 0, D,
"NA"
)
)