Find the minimum product which results from multiplying any 3 numbers from N1 to N6. Taking row 2 as example, minimum product result is 7*7*-3 = -147 For row 3, minimum product result is -7*-8*-4 = -224
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 537
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Min Product of Three with Power Query
Power Query solution 1 for Find Min Product of Three, proposed by Kris Jaganah:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S,
"Answer Expected",
each [
a = Record.ToList(_),
b = List.Sort(a),
c = List.Product(List.FirstN(b, 3)),
d = List.Product({b{0}, b{4}, b{5}}),
e = List.Min({c, d})
][e]
)
in
APower Query solution 2 for Find Min Product of Three, proposed by Kris Jaganah:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S,
"Answer Expected",
each [
a = Record.ToList(_),
b = List.Count(List.Select(a, each _ > 0)),
c = List.Min(a),
d = b + (if c >= 0 then 1 else 0),
e = List.Product(List.MinN(a, 3)),
f = List.MaxN(a, 2),
g = List.Product({f{0}, f{1}, c}),
h = if d < 2 or d > 5 then e else g
][h]
)
in
APower Query solution 3 for Find Min Product of Three, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
L = Record.ToList(_),
C1 = List.MinN(L, 3),
C2 = List.MaxN(L, 2) & List.MinN(L, 1),
P = List.Transform({C1, C2}, List.Product),
R = List.Min(P)
][R]
)
in
ReturnPower Query solution 4 for Find Min Product of Three, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc =
let
Num = {1 .. Number.Power(2, Table.ColumnCount(Source))},
Base = 2,
Start = {},
G = (f, g, h) =>
let
a = Number.IntegerDivide(f, g),
b = Number.Mod(f, g),
c = h,
d = if f = 0 then (List.RemoveLastN(c & {a})) else @G(a, g, c & {b})
in
d,
Cal = List.Transform(Num, each G(_, Base, Start))
in
Cal,
Comb = List.Transform(List.Select(Calc, each List.Sum(_) = 3), each List.PositionOf(_, 1, 2)),
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Record.ToList(_),
b = List.Transform(Comb, (x) => List.Product(List.Transform(x, (y) => a{y}))),
c = List.Min(b)
in
c
)[[Answer]]
in
SolSolving the challenge of Find Min Product of Three with Excel
Excel solution 1 for Find Min Product of Three, proposed by Bo Rydobon 🇹🇭:
=LET(
c,
LEN(
REDUCE(
"",
SEQUENCE(
6
),
LAMBDA(
a,
v,
VSTACK(
a,
TOCOL(
a&v
)
)
)
)
)=3,
BYROW(
A2:F10,
LAMBDA(
r,
MIN(
FILTER(
REDUCE(
1,
r,
LAMBDA(
a,
v,
VSTACK(
a,
TOCOL(
a*v
)
)
)
),
c
)
)
)
)
)Excel solution 2 for Find Min Product of Three, proposed by Bo Rydobon 🇹🇭:
=BYROW(A2:F10,LAMBDA(n,MIN(PRODUCT(SMALL(n,{1,2,3})),PRODUCT(SMALL(n,{1,5,6})))))Excel solution 3 for Find Min Product of Three, proposed by John V.:
=BYROW(
A2:F10,
LAMBDA(
x,
MIN(
BYROW(
SMALL(
x,
{1,
2,
3;1,
5,
6}
),
PRODUCT
)
)
)
)Excel solution 4 for Find Min Product of Three, proposed by محمد حلمي:
=BYROW(
A2:F10,
LAMBDA(
a,
IF(
OR(
AND(
a<0
),
AND(
a>0
),
SUM(
N(
a<0
)=1
)
),
PRODUCT(
SMALL(
a,
{1,
2,
3}
)
),
PRODUCT(
MIN(
a
),
LARGE(
a,
{1,
2}
)
)
)
)
)Excel solution 5 for Find Min Product of Three, proposed by Kris Jaganah:
=BYROW(A2:F10,LAMBDA(x,LET(a,(MIN(x)>=0)+SUM(N(x>0)),IF((a<2)+(a>5),PRODUCT(SMALL(x,{1,2,3})),PRODUCT(LARGE(x,{1,2}),MIN(x))))))Excel solution 6 for Find Min Product of Three, proposed by Kris Jaganah:
=BYROW(
A2:F10,
LAMBDA(
x,
MIN(
PRODUCT(
SMALL(
x,
{1,
2,
3}
)
),
PRODUCT(
LARGE(
x,
{1,
2,
6}
)
)
)
)
)Excel solution 7 for Find Min Product of Three, proposed by Timothée BLIOT:
=BYROW(
A2:F10,
LAMBDA(
z,
LET(
A,
CHAR(
SEQUENCE(
,
6
)+64
)&z,
MIN(
MAP(
A,
LAMBDA(
x,
MIN(
MAP(
x&A&TOCOL(
A
),
LAMBDA(
y,
LET(
B,
TOCOL(
REGEXEXTRACT(
y,
"[A-Z]-?d+",
1
)
),
IF(
CONCAT(
B
)=CONCAT(
UNIQUE(
B
)
),
PRODUCT(
--REGEXEXTRACT(
CONCAT(
B
),
"-?d+",
1
)
),
10^6
)
)
)
)
)
)
)
)
)
)
)Excel solution 8 for Find Min Product of Three, proposed by Hussein SATOUR:
=LET(
a,
SEQUENCE(
6
),
b,
TOROW(
a
),
c,
TOCOL(
a&IF(
b>a,
b,
""
)
),
d,
TOCOL(
c&IF(
b>--RIGHT(
c
),
b,
""
)
),
e,
FILTER(
d,
LEN(
d
)=3
),
BYROW(
A2:F10,
LAMBDA(
y,
MIN(
MAP(
e,
LAMBDA(
x,
PRODUCT(
CHOOSECOLS(
y,
--MID(
x,
{1;2;3},
1
)
)
)
)
)
)
)
)
)Excel solution 9 for Find Min Product of Three, proposed by Sunny Baggu:
=LET(
s,
SEQUENCE(
ROWS(
A2:F10
)
),
MAP(
s,
LAMBDA(
x,
LET(
k,
INDEX(
A2:F10,
x,
),
_a,
REDUCE(
"",
TOCOL(
k
),
LAMBDA(
a,
v,
VSTACK(
a,
a & "," & v
)
)
),
_b,
MAP(
_a,
LAMBDA(
a,
IFERROR(
N(
ROWS(
UNIQUE(
TOCOL(
SEARCH(
",",
a,
SEQUENCE(
15
)
),
3
)
)
) = 3
),
0
)
)
),
_c,
FILTER(
_a,
_b
),
_d,
MIN(
MAP(
_c,
LAMBDA(
a,
PRODUCT(
TEXTSPLIT(
a,
,
",",
1
) + 0
)
)
)
),
_d
)
)
)
)Excel solution 10 for Find Min Product of Three, proposed by Sunny Baggu:
=BYROW(
A2:F10,
LAMBDA(
x,
LET(
_a,
SORT(
x,
,
1,
1
),
MIN(
PRODUCT(
TAKE(
_a,
,
3
)
),
PRODUCT(
INDEX(
_a,
,
{1,
5,
6}
)
)
)
)
)
)Excel solution 11 for Find Min Product of Three, proposed by LEONARD OCHEA 🇷🇴:
=LET(R,BYROW,S,SEQUENCE,m,--MID(BASE(S(2^6),2,6),S(,6),1), R(A2:F10,LAMBDA(x,MIN(R(IF(FILTER(m,R(m,SUM)=3),x,""),PRODUCT)))))Excel solution 12 for Find Min Product of Three, proposed by Bilal Mahmoud kh.:
BYROW(A2:F10,LAMBDA(m,MIN(MAP(TEXTSPLIT(TEXTJOIN(",",,(LET(a,SEQUENCE(6),MAP(a,LAMBDA(x,TEXTJOIN(",",,MAP(a,LAMBDA(y,TEXTJOIN(",",,MAP(a,LAMBDA(z,IF(AND(x<>y,y<>z,x<>z),TEXTJOIN(":",,x,y,z),"")))))))))))),","),LAMBDA(n,PRODUCT(INDEX(m,1,--TEXTSPLIT(n,":"))))))))Excel solution 13 for Find Min Product of Three, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=BYROW(
A2:F10,
LAMBDA(
x,
IF(
OR(
SUM(
IF(
SUM(
IF(
x<0,
1,
0
)
)={3,
4,
5,
6},
1,
0
)
)>0,
SUM(
IF(
x>0,
1,
0
)
)=6
),
PRODUCT(
SMALL(
x,
{1,
2,
3}
)
),
PRODUCT(
LARGE(
x,
{1,
2}
)
)*MIN(
x
)
)
)
)Excel solution 14 for Find Min Prod&uct of Three, proposed by Eddy Wijaya:
=BYROW(A2:F10,LAMBDA(r,MIN(PRODUCT(LARGE(r,{1,2}),MIN(r)),PRODUCT(SMALL(r,{1,2,3})))))Excel solution 15 for Find Min Product of Three, proposed by El Badlis Mohd Marzudin:
=LET(
q,
BYROW,
q(
A2:F10,
LAMBDA(
x,
LET(
a,
REDUCE(
"",
SEQUENCE(
3
),
LAMBDA(
a,
b,
TOCOL(
a&SEQUENCE(
,
6
)
)
)
),
b,
MID(
a,
SEQUENCE(
,
3
),
1
),
c,
INDEX(
x,
b
),
d,
q(
c,
PRODUCT
),
MIN(
FILTER(
d,
q(
b,
LAMBDA(
x,
COUNTA(
UNIQUE(
x,
1
)
)=3
)
)
)
)
)
)
)
)Excel solution 16 for Find Min Product of Three, proposed by Ogunronbi Taiwo Fisayo:
=BYROW(
A2:F10,
LAMBDA(
n,
LET(
a,
PRODUCT(
SMALL(
n,
{1,
2,
3}
)
),
b,
PRODUCT(
SMALL(
n,
{1,
5,
6}
)
),
MIN(
a,
b
)
)
)
)Solving the challenge of Find Min Product of Three with Python
Python solution 1 for Find Min Product of Three, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
from itertools import combinations
path = "537 Minimum Product for Triplet.xlsx"
input = pd.read_excel(path, usecols="A:F")
test = pd.read_excel(path, usecols="G")
output = input.copy()
output['min_product'] = input.apply(lambda row: min(np.prod(c) for c in combinations(row, 3)), axis=1).astype(np.int64)
print(output["min_product"].equals(test["Answer Expected"])) # True
Solving the challenge of Find Min Product of Three with Python in Excel
Python in Excel solution 1 for Find Min Product of Three, proposed by Alejandro Campos:
import itertools
df = xl("A1:F10", headers=True)
def min_product_of_3(row):
combinations = list(itertools.combinations(row, 3))
products = [np.prod(combo) for combo in combinations]
return min(products)
df['Min_Product'] = df.apply(min_product_of_3, axis=1)
df
Python in Excel solution 2 for Find Min Product of Three, proposed by Abdallah Ally:
from itertools import combinations
from functools import reduce
def get_min_product(row):
products = [
reduce(lambda x, y: x * y, comb)
for comb in combinations(row[:-1], 3)
]
return min(products)
df = xl("A1:G10", headers=True)
# Perform data munging
df['My Answer'] = df.apply(get_min_product, axis=1)
df['Check'] = df['Answer Expected'] == df['My Answer']
df
Python in Excel solution 3 for Find Min Product of Three, proposed by Anshu Bantra:
import itertools as itt
df = xl("A1:F10", headers=True)
def find_minimum_product(row):
return min([np.prod(_) for _ in itt.combinations(row, 3)])
df['Min. Prod.'] = df.apply(lambda x: find_minimum_product(x), axis=1)
df['Min. Prod.'].values
&&