Find the Min, Max and Count of numbers having number of digits in column A whose product of digits is equal to column B. Hence, if number of digits is 4, then we need to consider all numbers from 1000 to 9999.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 337
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count of numbers where digits’ product equals value with Power Query
Power Query solution 1 for Count of numbers where digits’ product equals value, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.ExpandRecordColumn(
Table.AddColumn(
Source,
"R",
each
let
n = [Number of Digits],
p = List.Transform(
List.Select({1 .. 9}, (d) => Number.Mod([Product of Digits], d) = 0),
Text.From
),
q = List.Accumulate(
{1 .. n - 1},
p,
(s, l) =>
List.Combine(
List.Transform(
s,
(s) =>
List.RemoveNulls(
List.Transform(p, (p) => if Text.End(s, 1) <= p then s & p else null)
)
)
)
),
r = List.Select(
q,
(q) => List.Product(List.Transform(Text.ToList(q), Number.From)) = [Product of Digits]
),
s = List.Sum(
List.Transform(
r,
(r) =>
Number.Permutations(n, n)
/ List.Product(
Table.Group(
Table.FromValue(Text.ToList(r)),
"Value",
{"C", each Number.Permutations(List.Count(_), List.Count(_))}
)[C]
)
)
)
in
[Min = Number.From(r{0}), Max = Number.From(Text.Reverse(r{0})), Count = s]
),
"R",
{"Min", "Max", "Count"}
)
in
AnsPower Query solution 2 for Count of numbers where digits’ product equals value, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine(Table.AddColumn(Source, "A", (x)=>
let
a = Number.Power(10,x[Number of Digits]-1),
b = Number.Power(10,x[Number of Digits])-1,
c = List.Transform(List.Select({a..b}, each not Text.Contains(Text.From(_), "0")), Number.From),,
d = List.Select(c, each List.Product(List.Transform(Text.ToList(Text.From(_)), Number.From))=x[Product of Digits]),
e = {List.First(d)}&{List.Last(d)}&{List.Count(d)}
in Table.FromRows({e}, {"Min", "Max","Count"}))[A])
in
Sol
Power Query solution 3 for Count of numbers where digits’ product equals value, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
(k) =>
let
a = Number.Power(10, k[Number of Digits] - 1),
b = Number.Power(10, k[Number of Digits]) - 1,
c = List.Last(
List.Generate(
() => [x = a, y = 0, z = k[Product of Digits]],
each [y] < 1,
each [
x = [x] + 1,
y =
if List.Product(List.Transform(Text.ToList(Text.From([x])), Number.From)) = [z] then
[y] + 1
else
[y],
z = [z]
],
each [x]
)
),
d = Number.From(Text.Combine(List.Reverse(Text.ToList(Text.From(c))))),
e = List.Transform(
List.Select({c .. d}, each not Text.Contains(Text.From(_), "0")),
Number.From
),
f = List.Count(
List.Select(
e,
each List.Product(List.Transform(Text.ToList(Text.From(_)), Number.From))
= k[Product of Digits]
)
),
g = Table.FromRows({{c} & {d} & {f}}, {"Min", "Max", "Count"})
in
g
)[A]
)
in
SolPower Query solution 4 for Count of numbers where digits' product equals value, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
(x) =>
let
z = List.Select({1 .. 9}, each Number.Mod(x[Product of Digits], _) = 0),
y = List.Repeat({z}, x[Number of Digits]),
a = Table.FromRows({y}),
b = List.Accumulate(Table.ColumnNames(a), a, (s, c) => Table.ExpandListColumn(s, c)),
c = List.Select(Table.ToRows(b), each List.Product(_) = x[Product of Digits]),
d = List.Count(c),
e = Text.Combine(List.Transform(c{0}, Text.From)),
f = Number.From(Text.Reverse(e))
in
Table.FromColumns({{Number.From(e)}, {f}, {d}}, {"Min", "Max", "Count"})
)[A]
)
in
SolPower Query solution 5 for Count of numbers where digits' product equals value, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ReType = Table.TransformColumnTypes(
Source,
{{"Number of Digits", Int64.Type}, {"Product of Digits", Int64.Type}}
),
AddProdList = Table.AddColumn(
ReType,
"NumList",
each [
POD = Number.From([Product of Digits]),
a = [Number of Digits],
b = Number.Power(10, a - 1),
c = Number.Power(10, a) - 1,
d = {b .. c},
e = List.Transform(d, each Text.From(_)),
f = List.Transform(e, each List.Product(List.Transform(Text.ToList(_), each Number.From(_)))),
g = Table.FromColumns({d, f}),
h = Table.SelectRows(g, each [Column2] = POD),
i = List.Min(h[Column1]),
j = List.Max(h[Column1]),
k = Table.RowCount(h)
]
),
AddSelRecs = Table.RemoveColumns(
Table.AddColumn(AddProdList, "SelRecs", each Record.SelectFields([NumList], {"i", "j", "k"})),
"NumList"
),
Expand = Table.ExpandRecordColumn(AddSelRecs, "SelRecs", {"i", "j", "k"}, {"Min", "Max", "Count"})
in
ExpandPower Query solution 6 for Count of numbers where digits' product equals value, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x, y) =>
let
d = x,
p = y,
a = Number.Power(10, d - 1),
b = Number.Power(10, d) - 1,
c = {a .. b},
e = List.Transform(c, each Text.ToList(Text.From(_))),
f = List.Transform(e, each List.Transform(_, Number.From)),
g = List.Transform(f, each List.Product(_)),
h = List.Zip({g, c}),
i = List.Buffer(List.Select(h, each _{0} = p)),
j = List.First(i){1},
k = List.Last(i){1},
l = List.Count(i),
m = [Min = j, Max = k, Count = l]
in
m,
n = Table.AddColumn(Origen, "R", each Fx([Number of Digits], [Product of Digits])),
Sol = Table.ExpandRecordColumn(n, "R", {"Min", "Max", "Count"}, {"Min", "Max", "Count"})[
[Min],
[Max],
[Count]
]
in
SolSolving the challenge of Count of numbers where digits' product equals value with Excel
Excel solution 1 for Count of numbers where digits' product equals value, proposed by Bo Rydobon 🇹🇭:
=LET(x,
A3:B7,
REDUCE(C2:E2,
SEQUENCE(
ROWS(
x
)
),
LAMBDA(a,
b,
LET(n,
INDEX(
x,
b,
1
),
p,
INDEX(
x,
b,
2
),
i,
SEQUENCE(
9
),
f,
FILTER(
i,
MOD(
p,
i
)=0
),
j,
ROWS(
f
),
k,
TOCOL(MAP(BASE(
SEQUENCE(
j^n-1
),
j,
n
),
LAMBDA(b,
LET(m,
INDEX(
f,
MID(
b,
SEQUENCE(
n
),
1
)+1
),
CONCAT(
m
)/(p=PRODUCT(
m
))))),
3),
VSTACK(
a,
HSTACK(
MIN(
k
),
MAX(
k
),
ROWS(
k
)
)
)))))Excel solution 2 for Count of numbers where digits' product equals value, proposed by Rick Rothstein:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A3:A6,
LAMBDA(
n,
LET(
r,
REPT(
9,
n-1
),
s,
SEQUENCE(
REPT(
9,
n
)-r,
,
r+1
),
f,
FILTER(
s,
BYROW(
MID(
s,
SEQUENCE(
,
n
),
1
),
LAMBDA(
r,
PRODUCT(
0+r
)=OFFSET(
n,
,
1
)
)
)
),
MIN(
f
)&" "&MAX(
f
)&" "&COUNT(
f
)
)
)
)
),
" ",
"|"
)Excel solution 3 for Count of numbers where digits' product equals value, proposed by محمد حلمي:
=REDUCE(
{"Min",
"Max",
"Count"},
A3:A7,
LAMBDA(
a,
d,
LET(
s,
SEQUENCE(
d
),
x,
SEQUENCE(
MIN(
10^d-1,
10^5*6
),
2
),
n,
TOCOL(
MAP(
x,
LAMBDA(
a,
IFS(
LEN(
a
)=d,
a
)
)
),
2
),
i,
MAP(
n,
LAMBDA(
a,
PRODUCT(
--MID(
a,
s,
1
)
)
)
),
r,
FILTER(
n,
i=TAKE(
d:B3,
-1,
-1
)
),
m,
MIN(
r
),
w,
ROWS(
r
),
VSTACK(
a,
HSTACK(
m,
--CONCAT(
MID(
m,
d+1-s,
1
)
),
IF(
w=50,
16912,
w
)
)
)
)
)
)Excel solution 4 for Count of numbers where digits' product equals value, proposed by Kris Jaganah:
=--TEXTSPLIT(ARRAYTOTEXT(MAP(A3:A7,
B3:B7,
LAMBDA(x,
y,
LET(a,
10^(x-1),
b,
a*10-a,
c,
SEQUENCE(
b/10,
10,
a
),
d,
MAP(
c,
LAMBDA(
y,
PRODUCT(
--MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
)
)
),
e,
TOCOL(
IFS(
d=y,
c
),
3
),
TEXTJOIN(
"-",
,
MIN(
e
),
MAX(
e
),
ROWS(
e
)
))))),
"-",
", ")Excel solution 5 for Count of numbers where digits' product equals value, proposed by Julian Poeltl:
=REDUCE(HSTACK(
"Min",
"Max",
"Count"
),
A3:A7,
LAMBDA(A,
N,
VSTACK(A,
LET(P,
XLOOKUP(
N,
A3:A7,
B3:B7
),
S,
SEQUENCE(((1&REPT(
0,
N
))-(1&REPT(
0,
N-1
)))/10,
10,
(1&REPT(
0,
N-1
))),
T,
TOCOL(
IF(
MAP(
S,
LAMBDA(
A,
PRODUCT(
--MID(
A,
SEQUENCE(
N
),
1
)
)
)
)=P,
S,
X
),
3
),
HSTACK(
TAKE(
T,
1
),
TAKE(
T,
-1
),
ROWS(
T
)
)))))Excel solution 6 for Count of numbers where digits' product equals value, proposed by Alejandro Campos:
= 1
while n > 0:
product *= n % 10
n //= 10
return product
def find_numbers_with_digit_product(
num_digits,
target_product
):
min_num = 10**(num_digits - 1)
max_num = 10**num_digits - 1
valid_numbers = []
for num in range(
min_num,
max_num + 1
):
if product_of_digits(
num
) == target_product:
valid_numbers.append(
num
)
if valid_numbers:
return min(
valid_numbers
),
max(
valid_numbers
),
len(
valid_numbers
)
else:
return None,
None,
0
data = [
(3,
8),
(4,
15),
(5,
896),
(6,
5184),
(7,
23328)
]
results = []
for num_digits,
target_product in data:
min_val,
max_val,
count = find_numbers_with_digit_product(
num_digits,
target_product
)
results.append((num_digits,
target_product,
min_val,
max_val,
count))Excel solution 7 for Count of numbers where digits' product equals value, proposed by Timothée BLIOT:
=REDUCE({"Min","Max","Count"},SEQUENCE(5),LAMBDA(r,s, LET(N,INDEX(A3:A7,s),M,INDEX(B3:B7,s),A,SEQUENCE(MIN(10^N-(10^(N-1)),10^6-10^5),ROUNDUP(10^(N-6),0),(10^(N-1))),B,MAP(A,LAMBDA(x, PRODUCT(--MID(x,SEQUENCE(LEN(x)),1))=M)),D,TOCOL(IF(B,A,1/0),3), VSTACK(r,HSTACK(MIN(D),MAX(D),COUNT(D))))))Excel solution 8 for Count of numbers where digits' product equals value, proposed by LEONARD OCHEA 🇷🇴:
=REDUCE(
{"Min",
"Max",
"Count"},
B3:B7,
LAMBDA(
a,
b,
LET(
n,
OFFSET(
b,
,
-1
),
s,
SEQUENCE(
9
),
d,
& TOROW(
IF(
MOD(
b,
s
),
x,
s
),
3
),
m,
COUNT(
d
),
e,
INDEX(
d,
MID(
BASE(
SEQUENCE(
m^n,
,
0
),
m,
n
),
SEQUENCE(
,
n
),
1
)+1
),
p,
BYROW(
e,
LAMBDA(
x,
PRODUCT(
x
)
)
),
f,
BYROW(
FILTER(
e,
p=b
),
LAMBDA(
y,
--CONCAT(
y
)
)
),
VSTACK(
a,
HSTACK(
MIN(
f
),
MAX(
f
),
COUNT(
f
)
)
)
)
)
)Excel solution 9 for Count of numbers where digits' product equals value, proposed by JvdV -:
=--TEXTSPLIT(TEXTAFTER("|"&LET(x,
SEQUENCE(
10^6
)+SEQUENCE(
,
10,
0,
10^6
),
p,
REDUCE(
1,
ROW(
1:8
),
LAMBDA(
y,
z,
IF(
MID(
x,
z,
1
)="",
y,
y*MID(
x,
z,
1
)
)
)
),
MAP(A2:A6,
B2:B6,
LAMBDA(a,
b,
LET(n,
TOCOL(IFS((p=b)*(LEN(
x
)=a),
x),
3),
MIN(
n
)&"|"&MAX(
n
)&"|"&ROWS(
n
))))),
"|",
{1,
2,
3}),
"|")Excel solution 10 for Count of numbers where digits' product equals value, proposed by Pieter de Bruijn:
=LET(
x,
A3:B7,
REDUCE(
{"Min",
"Max",
"Count"},
SEQUENCE(
ROWS(
x
)
),
LAMBDA(
a,
b,
LET(
n,
INDEX(
x,
b,
1
),
p,
INDEX(
x,
b,
2
),
s,
SEQUENCE(
,
9
),
o,
SEQUENCE(
n
),
r,
REDUCE(
TOCOL(
s
),
SEQUENCE(
n-1
),
LAMBDA(
a,
z,
TOCOL(
IFS(
--RIGHT(
a
)<=s,
a&s
),
2
)
)
),
t,
--FILTER(
r,
MAP(
r,
LAMBDA(
m,
p=PRODUCT(
--MID(
m,
o,
1
)
)
)
)
),
VSTACK(
a,
HSTACK(
@t,
--CONCAT(
MID(
@t,
n+1-o,
1
)
),
SUM(
MAP(
t,
LAMBDA(
x,
LET(
p,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
UNIQUE(
MID(
x,
SEQUENCE(
n
),
1
)
),
)
),
PERMUT(
n,
n
)/PRODUCT(
PERMUT(
p,
p
)
)
)
)
)
)
)
)
)
)
)
)Excel solution 11 for Count of numbers where digits' product equals value, proposed by Pieter de Bruijn:
=--TEXTSPLIT(ARRAYTOTEXT(MAP(A3:A7,
B3:B7,
LAMBDA(x,
y,
LET(a,
10^(x-1),
b,
SEQUENCE((a*10-a)/10,
10,
a),
d,
TOCOL(
MAP(
b,
LAMBDA(
c,
IFS(
PRODUCT(
--MID(
c,
SEQUENCE(
LEN(
c
)
),
1
)
)=y,
c
)
)
),
2
),
TEXTJOIN(
"-",
,
MIN(
d
),
MAX(
d
),
ROWS(
d
)
))))),
"-",
", ")Excel solution 12 for Count of numbers where digits' product equals value, proposed by Giorgi Goderdzishvili:
=
VSTACK( {"Min",
"Max",
"Count"},
TEXTSPLIT(
TEXTJOIN("!",
,
MAP(A3:A7,
B3:B7,
LAMBDA(a,
v,
LET(
_dig,
a,
_prd,
v,
_st,
(1&REPT(
"0",
_dig-1
))*1,
_nd,
1* REPT(
9,
_dig
),
_ttl,
_nd-_st+1,
_rw,
INT(
_ttl/100
),
_cl,
100,
_sq,
SEQUENCE(
_rw,
_cl,
_st
),
_sm,
MAP(
_sq,
LAMBDA(
z,
PRODUCT(
--
MID(
z,
SEQUENCE(
,
_dig
),
1
)
)
)
),
_fl,
IF(
_sm=_prd,
_sq,
""
),
_mn,
MIN(
_fl
),
_mx,
MAX(
_fl
),
_cn,
COUNT(
_fl
),
_fin,
ARRAYTOTEXT(
HSTACK(
_mn,
_mx,
_cn
)
),
_fin)))),
", ",
"!")*1)Excel solution 13 for Count of numbers where digits' product equals value, proposed by Viswanathan M B:
=Let(vals,
sequence(10^(a3+1) - 10^a3,
1,
10^(a3-1)),
P,
Map(
vals,
lambda(
a,
product(
mid(
a,
sequence(
a3
),
1
)
)
)
),
Condition,
(P=b3)*1,
L,
min(
vals*condition
),
U,
max(
vals*condition
),
Hstack(
L,
u,
sum(
condition
)
))Solving the challenge of Count of numbers where digits' product equals value with R
R solution 1 for Count of numbers where digits' product equals value, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(numbers)
input = read_excel("Product Equal To.xlsx", range = "A2:B7") %>% janitor::clean_names()
test = read_excel("Product Equal To.xlsx", range = "C2:E7")
analyze_numbers = function(number_of_digits, products_of_digits) {
# get divisors of product (only single digit ones)
divs = divisors(products_of_digits)
sing_dig_divs = divs[divs < 10]
# get all possible combinations of digits
all_combos = expand.grid(rep(list(sing_dig_divs), number_of_digits)) %>%
mutate(prod_of_combo = reduce(., `*`)) %>%
filter(prod_of_combo == products_of_digits) %>%
select(-prod_of_combo) %>%
unite("number", everything(), sep = "")
summary = all_combos %>%
summarise(Min = min(as.numeric(number)),
Max = max(as.numeric(number)),
Count = n() %>% as.numeric())
return(summary)
}
result = input %>%
mutate(summary = map2(number_of_digits, product_of_digits, analyze_numbers)) %>%
unnest(summary) %>%
select(-number_of_digits, -product_of_digits)
Solving the challenge of Count of numbers where digits' product equals value with Excel VBA
Excel VBA solution 1 for Count of numbers where digits' product equals value, proposed by Nicolas Micot:
VBA solution:
Function f_challenge337(PlageProbleme As Range) As Long()
Dim resultat() As Long, nombre As Long, produitCible As Long, produit As Long, _
nombreMin As Long, nombreMax As Long
Dim tabProbleme As Variant
Dim lig As Integer, nbChiffres As Integer, numChiffre As Integer, nbNombre As Integer
tabProbleme = PlageProbleme.Value
ReDim resultat(1 To UBound(tabProbleme, 1), 1 To 3)
For lig = 1 To UBound(tabProbleme, 1)
nbChiffres = tabProbleme(lig, 1)
produitCible = tabProbleme(lig, 2)
nbNombre = 0
For nombre = 10 ^ (nbChiffres - 1) To 10 ^ (nbChiffres) - 1
For numChiffre = 1 To nbChiffres
If numChiffre > 1 Then
produit = produit * Mid(nombre, numChiffre, 1)
Else
produit = Mid(nombre, numChiffre, 1)
End If
Next numChiffre
If produit = produitCible Then
If nbNombre = 0 Then nombreMin = nombre
nombreMax = nombre
nbNombre = nbNombre + 1
End If
Next nombre
resultat(lig, 1) = nombreMin
resultat(lig, 2) = nombreMax
resultat(lig, 3) = nbNombre
Next lig
f_challenge337 = resultat
End Function
&&