List all Semi Prime Numbers. A Semi Prime is a natural number that is the product of exactly two prime numbers Ex. 314 which can be expressed as product of two prime numbers – 2 and 157 Note – I have given column B as justification to the answers. You need to use column A only for the problem.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 306
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List all Semi Prime Numbers with Power Query
Power Query solution 1 for List all Semi Prime Numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
IsPrime = each not List.Accumulate({2 .. Number.RoundDown(Number.Sqrt(_))}, _ = 1, (s, c) => s or (Number.Mod(_, c) = 0)),
S = Table.SelectRows(Source, each List.Accumulate(List.Numbers(1, Int64.From([Numbers] / 2)), 0, (s, c) => s + (let q = [Numbers] / c, m = Number.Mod([Numbers], c) in Number.From(IsPrime(c) and m = 0 and IsPrime(q)))) = 2)
in
S
Working well with small numbers, struggles with large ones. Looking forward to see other solutions that work quicker with large numbers. 😇
Power Query solution 2 for List all Semi Prime Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = {2} & List.Select({3 .. Number.RoundDown(Number.Sqrt([Numbers]))}, Number.IsOdd),
b = List.Select(a, (x) => Number.Mod([Numbers], x) = 0),
c = List.Transform(b, (y) => {y} & {[Numbers] / y}),
d = List.Transform(
c,
(z) =>
List.AllTrue(
List.Combine(
List.Transform(
z,
(t) =>
List.Transform(
{2} & List.Select({3 .. Number.RoundDown(Number.Sqrt(t))}, Number.IsOdd),
(w) =>
if t = 2 then
true
else if Number.Mod(t, 2) = 0 then
false
else
Number.Mod(t, w) > 0
)
)
)
)
),
e = List.AllTrue(List.Transform(a, (x) => Number.Mod([Numbers], x) > 0))
in
List.AllTrue(d) and not e
)[[Numbers]]
in
SolPower Query solution 3 for List all Semi Prime Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
fx = (n) =>
List.Select({2 .. Number.RoundDown(Number.Sqrt(n))}, (x) => Number.Mod(n, x) = 0){0}? = null,
res = Table.SelectRows(
Fonte,
each (Expression.Evaluate(Text.Replace([Prime Factors], ", ", "*")) = [Numbers])
and List.Count(Text.Split([Prime Factors], ", "))
= 2
and List.AllTrue(
List.Transform(Text.Split([Prime Factors], ", "), (x) => fx(Number.From(x)))
)
)[[Numbers]]
in
resSolving the challenge of List all Semi Prime Numbers with Excel
Excel solution 1 for List all Semi Prime Numbers, proposed by Bo Rydobon 🇹🇭:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
n,
LET(
P,
LAMBDA(
n,
LET(
c,
{2,
3,
5,
7},
d,
n/TOCOL(
VSTACK(
c,
SEQUENCE(
n^0.5/6,
,
2
)*6+{-1,
1}
),
3
),
IF(
OR(
n=c
),
0,
XLOOKUP(
0,
d-INT(
d
),
d,
0
)
)
)
),
a,
P(
n
),
n/IF(
a,
P(
a
)=0
)
)
)
),
3
)Excel solution 2 for List all Semi Prime Numbers, proposed by John V.:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
LAMBDA(
n,
1+SEQUENCE(
n^0.5/2,
2
)
),
p,
LAMBDA(
n,
SUM(
N(
n-a(
n
)*INT(
n/a(
n
)
)=0
)
)=0
),
s,
a(
x
),
f,
@TOCOL(s/(x-s*INT(
x/s
)=0),
2),
x/(1-p(
x
))/p(
x/f
)))),
2)Excel solution 3 for List all Semi Prime Numbers, proposed by محمد حلمي:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
c,
LET(
i,
{2,
3,
5,
11,
13,
17,
19,
21},
x,
LAMBDA(
w,
AND(
0<>MOD(
w,
SEQUENCE(
w^
IF(
LEN(
c
)>5,
0.25,
0.5
),
,
2
)
)
)
),
c/AND(
MAP(
c/i,
LAMBDA(
a,
IF(
OR(
a=i
),
1,
IF(
x(
c
),
0,
x(
a
)
)
)
)
)
)
)
)
),
2
)Excel solution 4 for List all Semi Prime Numbers, proposed by Timothée BLIOT:
=TOCOL(A2:A10/MAP(A2:A10,
LAMBDA(z,
LET(D,
LAMBDA(m,
VSTACK({2;3},
SEQUENCE(MAX(FLOOR((FLOOR(
m^0.5,
1
)-3)/2,
1),
1),
,
5,
2))),
P,
LAMBDA(n,
OR(SUM(--(n={2;3;5;7;9}))=1,
PRODUCT(--(MOD(
n,
D(
n
)
)<>0)))),
A,
ROW(
1:999
),
B,
FILTER(
A,
MAP(
A,
LAMBDA(
x,
P(
x
)
)
)
),
C,
z/B,
E,
FILTER(C,
INT(
C
)=C*(C<>z),
1/0),
ROWS(
UNIQUE(
TOCOL(
HSTACK(
E,
z/E
),
3
)
)
)=2))),
3)Excel solution 5 for List all Semi Prime Numbers, proposed by Charles Roldan:
=LET(
_,
LAMBDA(
f,
LAMBDA(
g,
LAMBDA(
x,
f(
g(
x
)
)
)
)
),
S,
LAMBDA(
f,
LAMBDA(
g,
LAMBDA(
x,
f(
x
)(
g(
x
)
)
)
)
),
Fl,
LAMBDA(
f,
LAMBDA(
x,
FILTER(
x,
f(
x
)
)
)
),
Mp,
LAMBDA(
f,
LAMBDA(
x,
MAP(
x,
f
)
)
),
λNot,
LAMBDA(
x,
NOT(
x
)
),
λMod,
LAMBDA(
a,
LAMBDA(
b,
MOD(
a,
b
)
)
),
JustOne,
LAMBDA(
x,
COUNT(
x
) = 1
),
Candidates,
LAMBDA(
x,
DROP(
SEQUENCE(
SQRT(
x
)
),
1
)
),
Fl(Mp(_(JustOne)(S(_(Fl)(_(_(λNot))(λMod)))(Candidates))))
)(A2:A9)Excel solution 6 for List all Semi Prime Numbers, proposed by Pieter de Bruijn:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(b,
a/TOCOL(a/(a/VSTACK(
{2,
3},
SEQUENCE(
a^0.5/6
)*6-{1,
-1}
)),
2),
a/(SUM(
N(
b=INT(
b
)
)
)=1)))),
2)
(Big thanks to Bo Rydobon's formula solution)Excel solution 7 for List all Semi Prime Numbers, proposed by Abdelrahman Omer, MBA, PMP:
=TOCOL(MAP(A2:A10,
LAMBDA(b,
LET(z,
ROUNDUP(
SQRT(
b
),
),
y,
IF(
ISEVEN(
z
),
z-1,
z
),
c,
VSTACK(
SEQUENCE(
ROUNDDOWN(
SQRT(
b
),
)/2,
,
y,
-2
),
2
),
e,
BYROW(c,
LAMBDA(f,
(b/f-INT(
b/f
)=0))),
g,
FILTER(
c*e,
c*e>0
),
d,
BYROW(g,
LAMBDA(f,
--IF(f=2,
TRUE,
PRODUCT(--(MOD(
f,
SEQUENCE(
ROUNDDOWN(
SQRT(
f
),
),
,
ROUNDUP(
SQRT(
f
),
),
-1
)
)<>0))))),
FILTER(
b,
SUM(
d
)=1
)))),
3)Solving the challenge of List all Semi Prime Numbers with Python in Excel
Python in Excel solution 1 for List all Semi Prime Numbers, proposed by Bo Rydobon 🇹🇭:
from sympy import isprime
def iSP(n):
if isprime(n):return 0
else:
for d in range(2,int(n**0.5)+1):
if n%d==0: return isprime(n//d)
[n for n in xl("A2:A10")[0] if iSP(n)]
Python in Excel solution 2 for List all Semi Prime Numbers, proposed by John V.:
Hi everyone!
def sp(n):
f = factorint(n)
return len(f) == 2 and all(isprime(i) for i in f)
[n for n in xl("A2:A10")[0] if sp(n)]
Blessings!
Solving the challenge of List all Semi Prime Numbers with R
R solution 1 for List all Semi Prime Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(gmp)
library(data.table)
input = read_excel("Semi Prime Numbers.xlsx", range = "A1:A10")
test = read_excel("Semi Prime Numbers.xlsx", range = "C1:C5")
is_semiprime = function(n){
factors <- n %>%
as.character() %>%
as.bigz() %>%
factorize() %>%
as.vector()
check = ifelse(length(factors) == 2, TRUE, FALSE)
return(check)
}
result = input$Numbers %>%
keep(~ is_semiprime(.))
identical(result, test$`Expected Answers`)
&&&