Sandwich numbers – A number is said to be a Sandwich number if the number prior to it and the number after it, both are primes. Ex. 18 – 17 and 19 both are primes. List the first 100 Sandwich numbers and prime numbers before and after it.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 405
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Prime-Surrounded Sandwich Numbers with Power Query
Power Query solution 1 for List Prime-Surrounded Sandwich Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
A = List.Select(
List.Select({3 .. 4000}, Number.IsOdd),
each List.AllTrue(
List.Transform({2 .. Number.RoundDown(Number.Sqrt(_))}, (x) => Number.Mod(_, x) <> 0)
)
),
B = List.Transform(A, each _ + 2),
C = List.Zip({A, B}),
D = List.FirstN(List.Select(C, each List.Contains(A, _{1})), 100),
E = List.Transform(D, each {_{0}, _{0} + 1, _{1}}),
F = Table.FromRows(E, {"Before Number", "Sandwich Number", "After Number"})
in
FPower Query solution 2 for List Prime-Surrounded Sandwich Numbers, proposed by Ramiro Ayala Chávez:
let
T = Table.AddColumn,
a = Table.FromColumns({{2 .. 4000}}, {"Before Number"}),
b = T(a, "Sandwich Number", each [Before Number] + 1),
c = T(b, "After Number", each [Sandwich Number] + 1),
Fx = (x) =>
let
A = Number.IntegerDivide(Number.Sqrt(x), 1),
B = List.Transform(List.Buffer({2 .. A}), each Number.Mod(x, _)),
C = List.Min(B) <> 0
in
C,
d = T(c, "P1", each Fx([Before Number])),
e = T(d, "P2", each Fx([After Number])),
Sol = Table.FirstN(Table.SelectRows(e, each [P1] = true and [P2] = true), 100)[
[Before Number],
[Sandwich Number],
[After Number]
]
in
SolSolving the challenge of List Prime-Surrounded Sandwich Numbers with Excel
Excel solution 1 for List Prime-Surrounded Sandwich Numbers, proposed by Bo Rydobon 🇹🇭:
=VSTACK(4,
TOCOL(MAP(SEQUENCE(
640
)*6,
LAMBDA(n,
n/AND(MOD(n+{-1,
1},
SEQUENCE((n+1)^0.5,
,
2))))),
3))Excel solution 2 for List Prime-Surrounded Sandwich Numbers, proposed by Rick Rothstein:
=LET(
p,
REDUCE(
2,
SEQUENCE(
3850
)+2,
LAMBDA(
a,
v,
IF(
AND(
MOD(
v,
a
)
),
VSTACK(
a,
v
),
a
)
)
),
TOCOL(
MAP(
p,
DROP(
p,
1
),
LAMBDA(
m,
n,
IF(
n-m=2,
m,
1/0
)
)
),
2
)+{0,
1,
2}
)Excel solution 3 for List Prime-Surrounded Sandwich Numbers, proposed by Rick Rothstein:
=LET(
p,
REDUCE(
2,
SEQUENCE(
3850
)+2,
LAMBDA(
a,
v,
IF(
AND(
MOD(
v,
a
)
),
VSTACK(
a,
v
),
a
)
)
),
t,
TOCOL(
MAP(
p,
DROP(
p,
1
),
LAMBDA(
m,
n,
IF(
n-m=2,
m,
1/0
)
)
),
2
),
HSTACK(
t,
t+1,
t+2
)
)Excel solution 4 for List Prime-Surrounded Sandwich Numbers, proposed by John V.:
=TOCOL(
MAP(
ROW(
3:3821
),
LAMBDA(
x,
x/AND(
MOD(
x+{0,
2},
1+SEQUENCE(
x^0.5
)
)
)
)
),
2
)+{0,
1,
2}
And with recursive function:
✅=LET(
r,
LAMBDA(
r,
n,
i,
IF(
i<100,
IF(
AND(
MOD(
n+{0,
2},
1+SEQUENCE(
n^0.5
)
)
),
VSTACK(
n,
r(
r,
n+2,
i+1
)
),
r(
r,
n+2,
i
)
),
n
)
),
DROP(
r(
r,
3,
)+{0,
1,
2},
-1
)
)Excel solution 5 for List Prime-Surrounded Sandwich Numbers, proposed by محمد حلمي:
=REDUCE(
A1:C1,
SEQUENCE(
3820
)+1,
LAMBDA(
a,
v,
IF(
AND(
MOD(
v+{0,
2},
SEQUENCE(
v^0.5
)+1
)
),
VSTACK(
a,
v+{0,
1,
2}
),
a
)
)
)Excel solution 6 for List Prime-Surrounded Sandwich Numbers, proposed by محمد حلمي:
=TOCOL(MAP(SEQUENCE(3820)+1,LAMBDA(a,a/
AND(MOD(a+{0,2},SEQUENCE(a^0.5)+1)))),2)Excel solution 7 for List Prime-Surrounded Sandwich Numbers, proposed by Julian Poeltl:
=TAKE(LET(FK,
SEQUENCE(
4000,
,
3
),
TT,
MAP(
FK,
LAMBDA(
A,
SUM(
IF(
MOD(
A,
SEQUENCE(
1,
A
)
)=0,
1,
0
)
)
)
)&MAP((FK+2),
LAMBDA(
A,
SUM(
IF(
MOD(
A,
SEQUENZ(
1,
A
)
)=0,
1,
0
)
)
)),
FF,
FILTER(
FK,
TT*1=22
),
HSTACK(
FF+SEQUENCE(
,
3,
0
)
)),
100)Excel solution 8 for List Prime-Surrounded Sandwich Numbers, proposed by Timothée BLIOT:
=LET(
S,
ROW(
1:5000
),
F,
LAMBDA(
n,
NOT(
REGEXTEST(
REPT(
"1",
n
),
"^1?$|^(11+?)1+$"
)
)
),
TAKE(
FILTER(
S,
MAP(
S,
LAMBDA(
x,
F(
x-1
)*F(
x+1
)
)
)
),
100
)
)Excel solution 9 for List Prime-Surrounded Sandwich Numbers, proposed by Hussein SATOUR:
=LET(a,
SEQUENCE(
4000
),
b,
FILTER(a,
MAP(a,
LAMBDA(x,
SUM((x/a=INT(
x/a
))*1)))=2),
d,
DROP(
b,
-1
),
e,
DROP(
b,
1
),
FILTER(HSTACK(d,
(e+d)/2,
e),
e-d=2))Excel solution 10 for List Prime-Surrounded Sandwich Numbers, proposed by Sunny Baggu:
=LET(
_p,
REDUCE(
2,
SEQUENCE(
5000
) + 2,
LAMBDA(
a,
v,
IF(
AND(
MOD(
v,
a
)
),
VSTACK(
a,
v
),
a
)
)
),
_s,
SEQUENCE(
4000
),
_s1,
_s + {-1,
1},
{-1,
0,
1} +
TAKE(
FILTER(
_s,
BYROW(
ISNUMBER(
XMATCH(
_s1,
_p
)
),
LAMBDA(
a,
AND(
a
)
)
)
),
100
)
)Excel solution 11 for List Prime-Surrounded Sandwich Numbers, proposed by Sunny Baggu:
=LET(
_p,
REDUCE(
2,
SEQUENCE(
5000
) + 2,
LAMBDA(
a,
v,
IF(
AND(
MOD(
v,
a
)
),
VSTACK(
a,
v
),
a
)
)
),
_s,
SEQUENCE(
4000
),
_s1,
_s + {-1,
1},
TAKE(
FILTER(
_s,
BYROW(
ISNUMBER(
XMATCH(
_s1,
_p
)
),
LAMBDA(
a,
AND(
a
)
)
)
),
100
)
)Excel solution 12 for List Prime-Surrounded Sandwich Numbers, proposed by 🇵🇪 Ned Navarrete C.:
=LET(s,
SEQUENCE(
8^4
)+1,
i,
MAP(s,
LAMBDA(r,
SUM(--(MOD(
r,
SEQUENCE(
,
r^0.5
)
)=0))=1)),
x,
FILTER(
s,
i
),
TAKE(
FILTER(
x,
ISNUMBER(
XMATCH(
x+2,
x
)
)
),
100
)+{0,
1,
2})Excel solution 13 for List Prime-Surrounded Sandwich Numbers, proposed by Charles Roldan:
=LET(
Apply,
LAMBDA(
f,
LAMBDA(
x,
[y],
f(
x
)
)
),
Succ,
LAMBDA(f,
LAMBDA(Ω,
Ω(Ω))(LAMBDA(Ω,
LAMBDA(n,
IF(f(
n + 1
),
n + 1,
Ω(Ω)(
n + 1
)))))),
First,
LAMBDA(
n,
LAMBDA(
f,
SCAN(
0,
SEQUENCE(
n
),
Apply(
Succ(
f
)
)
)
)
),
Prime,
LAMBDA(
n,
IFERROR(
AND(
MOD(
n,
DROP(
SEQUENCE(
SQRT(
n
)
),
1
)
)
),
n > 1
)
),
{-1,
0,
1} + First(
100
)(LAMBDA(
n,
AND(
Prime(
n - 1
),
Prime(
n + 1
)
)
))
)Excel solution 14 for List Prime-Surrounded Sandwich Numbers, proposed by Mihai Radu O:
LET(
np, LAMBDA(nr,
AND(MOD(nr, SEQUENCE(nr ^ 0.5, , 2)) <> 0)
),
s, SEQUENCE(10000, , 2),
a, s + {0, 1, 2},
b, FILTER(
a,
MAP(TAKE(a, , 1), LAMBDA(x, np(x))) *
MAP(TAKE(a, , -1), LAMBDA(x, np(x)))
),
TAKE(b, 100)
)
Excel solution 15 for List Prime-Surrounded Sandwich Numbers, proposed by Giorgi Goderdzishvili:
==0:
return False
return True
emp_lst = []
cntr = 3
while len(
emp_lst
)<100:
if is_prime(
cntr-1
) and is_prime(
cntr+1
):
emp_lst.append(
[cntr-1,
cntr,
cntr+1]
)Solving the challenge of List Prime-Surrounded Sandwich Numbers with R
R solution 1 for List Prime-Surrounded Sandwich Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
is_prime <- function(x) {
if (x <= 1) return (FALSE)
if (x == 2 || x == 3) return (TRUE)
if (x %% 2 == 0) return (FALSE)
for (i in 3:sqrt(x)) {
if (x %% i == 0) return (FALSE)
}
TRUE
}
is_sandwich <- function(x) {
is_prime(x-1) && is_prime(x+1)
}
find_first_n_sandwich_numbers <- function(no) {
keep(1:10000, is_sandwich) %>%
unlist() %>%
head(no)
}
a = find_first_n_sandwich_numbers(100)
check = tibble(sandwich_number = a) %>%
mutate(before_number = sandwich_number - 1,
after_number = sandwich_number + 1) %>%
select(2,1,3)
Solving the challenge of List Prime-Surrounded Sandwich Numbers with Excel VBA
Excel VBA solution 1 for List Prime-Surrounded Sandwich Numbers, proposed by Nicolas Micot:
One to know if an Integer number is prime or not:
Private Function isPrime(number As Integer) As Boolean
Dim endNumber As Integer
Dim resultat As Boolean
endNumber = Int(Sqr(number))
resultat = True
For i = 2 To endNumber
If number / i = Int(number / i) Then
resultat = False
Exit For
End If
Next i
isPrime = resultat
End Function
And one to generate a sandwich list of n rows with n as an integer:
Function f_listSandwich(ByVal nbLig As Integer&) As Variant
Dim tableau As Variant
Dim lig As Integer, number As Integer
ReDim tableau(1 To nbLig, 1 To 3)
lig = 1
number = 3
While lig <= nbLig
If isPrime(number) Then
If isPrime(number + 2) Then
tableau(lig, 1) = number
tableau(lig, 2) = number + 1
tableau(lig, 3) = number + 2
lig = lig + 1
End If
End If
number = number + 2
Wend
f_listSandwich = tableau
End Function
With current functions definition, it can go to 505 rows of sandwich numbers.
&&