Count Prime Numbers in Range - Excel, Power Query, Python Challenges

Count prime numbers between given Lower and Upper Limits. Prime numbers are those numbers which can be divided only by 1 and that number itself and by no other number. Hence, 6 is not a prime number as it is divisible by 2 and 3. But 7 is a prime number. 1 is regarded as a non-prime number.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 58
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Count Prime Numbers in Range with Power Query

Power Query solution 1 for Count Prime Numbers in Range, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  MyFun = (Number as number) =>
    let
      Sqrt = Number.Sqrt(Number), 
      Int  = Number.IntegerDivide(Sqrt, 1), 
      Seq  = {2 .. Int}, 
      Mod  = List.Transform(Seq, each Number.Mod(Number, _)), 
      TF   = List.Min(Mod) <> 0
    in
      TF, 
  Min = List.Min(Source[Lower Limit]), 
  Max = List.Max(Source[Upper Limit]), 
  Calc = List.Generate(
    () => [x = 2, y = 2], 
    each [x] <= Max, 
    each [x = [x] + 1, y = if MyFun(x) then x else null], 
    each [y]
  ), 
  Prime = List.Buffer(List.RemoveNulls(Calc)), 
  Return = Table.AddColumn(
    Source, 
    "Count", 
    each List.Count(List.Select(Prime, (f) => f >= [Lower Limit] and f <= [Upper Limit]))
  )
in
  Return

Power Query solution 2 for Count Prime Numbers in Range, proposed by Brian Julius:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PcqxEcAwCAPAXVRTCGxlGY7910iwuVToJTLhhGELZQl1dvKgryFG4myXwd/zrXi+Qh5ro+oF", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Lower Limit" = _t, #"Upper Limit" = _t]
  ), 
  Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Type = Table.TransformColumnTypes(
    Index, 
    {{"Lower Limit", Int64.Type}, {"Upper Limit", Int64.Type}}
  ), 
  NumList = Table.AddColumn(
    Type, 
    "Number", 
    each List.Numbers([Lower Limit], [Upper Limit] - [Lower Limit] + 1)
  ), 
  Expand = Table.ExpandListColumn(NumList, "Number"), 
  RScriptPrimes = R.Execute(
    "# 'dataset' holds the input data for this script#(lf)library(primes)#(lf)dataset$isprime <- is_prime( dataset$Number)#(lf)answer <- as.data.frame(dataset)", 
    [dataset = Expand]
  ), 
  #"""answer""" = RScriptPrimes{[Name = "answer"]}[Value], 
  TrueOnly = Table.SelectRows(#"""answer""", each ([isprime] = true)), 
  CountPrimes = Table.Group(
    TrueOnly, 
    {"Lower Limit", "Upper Limit"}, 
    {{"Count", each Table.RowCount(_), Int64.Type}}
  )
in
  CountPrimes

Power Query solution 3 for Count Prime Numbers in Range, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "PrimeRange"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Count", 
    each List.Count(
      List.Select(
        {[Lower Limit] .. [Upper Limit]}, 
        each 
          let
            num = _
          in
            num
              > 1
                and List.IsEmpty(
                  List.Select(
                    {2 .. Number.RoundDown(Number.Sqrt(num))}, 
                    each Number.Mod(num, Number.From(_)) = 0
                  )
                )
      )
    )
  )[[Count]]
in
  #"Added Custom"

Solving the challenge of Count Prime Numbers in Range with Excel

Excel solution 1 for Count Prime Numbers in Range, proposed by John V.:

=b (a: lower limit, b: upper limit)

=MAP(A2:A7,B2:B7,LAMBDA(a,b,SUM(--(MAP(SEQUENCE(1+b-a,,a),LAMBDA(x,SUM(--(MOD(x,SEQUENCE(SQRT(x)))=0))))=1),-(a=1))))

Excel solution 2 for Count Prime Numbers in Range, proposed by محمد حلمي:

=MAP(A2:A7,
    B2:B7,
    
LAMBDA(a,
    b,
    
SUM(--MAP(SEQUENCE(
    b-a+1,
    ,
    a
),
    
LAMBDA(x,
    SUM(--(MOD(
        x/SEQUENCE(
            x
        ),
        1
    )=0))=2)))))

Excel solution 3 for Count Prime Numbers in Range, proposed by Julian Poeltl:

=MAP(A2:A7,B2:B7,LAMBDA(L,U,LET(S,SEQUENCE(U-L,,L),SUM(--MAP(S,LAMBDA(N,LET(c,{2357},D,N/TOCOL(VSTACK(c,SEQUENCE(N^0,5/6,,2)*6+{-11}),3),OR(N=c,AND(D-INT(D),N>1)))))))))

Excel solution 4 for Count Prime Numbers in Range, proposed by Timothée BLIOT:

=LET(LowerLimit,
    A2:A7,
     UpperLimit,
     B2:B7,
    

Numbers,
     SEQUENCE(
         ,
         INDEX(
             UpperLimit,
             1
         )-INDEX(
             LowerLimit,
             1
         )+1,
         INDEX(
             LowerLimit,
             1
         ),
         1
     ),
    
Divisors,
     BYCOL(Numbers,
     LAMBDA(b,
     ROUNDDOWN( b^(1/2),
    0) )),
    

Isprime,
     MAP( SEQUENCE(
         1,
         COLUMNS(
             Numbers
         )
     ),
     LAMBDA(b,
    

IF( SUM(
--(INDEX(
    Numbers,
    1,
    b
) /SEQUENCE(
    ,
    INDEX(
        Divisors,
        1,
        b
    )-1,
    2,
    1
) =
INT(
    INDEX(
    Numbers,
    1,
    b
)/SEQUENCE(
    ,
    INDEX(
        Divisors,
        1,
        b
    )-1,
    2,
    1
)
))
) >0,
     0,
     1)
 )),
    

SUM(
    Isprime
))

Excel solution 5 for Count Prime Numbers in Range, proposed by Charles Roldan:

=LET(Primes,
     LAMBDA(n,
     REDUCE(
DROP(
    SEQUENCE(
        n
    ),
     1
),
     DROP(
         SEQUENCE(
             ,
              SQRT(
        n
    )
         ),
          ,
          1
     ),
     
LAMBDA(a,
    b,
     FILTER(a,
     (a <= b) + MOD(
         a,
          b
     ))))),
     
MMULT(
    MATCH(
        A2:B7,
         Primes(
             MAX(
                 A2:B7
             )
         )
    ),
     {-1; 1}
))

Excel solution 6 for Count Prime Numbers in Range, proposed by Victor Momoh (MVP, MOS, R.Eng):

=MAP(A2:A7,B2:B7,LAMBDA(p,q,LET(a,SEQUENCE(q-p,,p),b,MAP(a,LAMBDA(x,SUM(--(MOD(x,SEQUENCE(INT(SQRT(x)),,2))=0)))),COUNT(FILTER(b,b=0)))))

Excel solution 7 for Count Prime Numbers in Range, proposed by Amardeep Singh:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,
COUNT(LET(n,SEQUENCE(b-a+1,1,a),pchk,MAP(n,LAMBDA(x,IFS(x=1,FALSE,x<4,TRUE,TRUE,SUM(--(MOD(x,SEQUENCE(1,INT(x/2)-1,2))=0))=0))),FILTER(n,pchk)))))

Excel solution 8 for Count Prime Numbers in Range, proposed by Viswanathan M B:

=LET(a, MIN(A2:A7),
 b, MAX(B2:B7),
 Seqn, SEQUENCE(b-a+1,1,a),
 Func, LAMBDA(x, LET(b, x/SEQUENCE(SQRT(x)-1,1,2), SUM(--(b=INT(b)))=0)),
 IsPrime, MAP(Seqn, Func),
 PrimeList, FILTER(Seqn, IsPrime),
 BYROW((TRANSPOSE(PrimeList)>=A2:A7)*(TRANSPOSE(PrimeList)<=B2:B7), LAMBDA(a, SUM(a))))

Excel solution 9 for Count Prime Numbers in Range, proposed by Sarun Chimamphant:

Solving the challenge of Count Prime Numbers in Range with Python in Excel

Python in Excel solution 1 for Count Prime Numbers in Range, proposed by Alejandro Campos:

def is_prime(n): return n > 1 and all(n % i for i in range(2, int(n**0.5) + 1))
def count_primes(l, u): return sum(is_prime(n) for n in range(l, u + 1))
data = xl("A1:B7", headers=True)
[count_primes(l, u) for l, u in zip(data["Lower Limit"], data["Upper Limit"])]

Solving the challenge of Count Prime Numbers in Range with Excel VBA

Excel VBA solution 1 for Count Prime Numbers in Range, proposed by Rick Rothstein:

Function PrimeCountBetween(FromNumber As Long, ToNumber As Long) As Long
 Dim X As Long, NthPrime As Long, Test As Long, Sum As Long
 Const MaxPrime As Long = 20000003
 Static Initialized As Boolean, PrimesFlag() As Byte
 If FromNumber > 0 And FromNumber < ToNumber And ToNumber < 20000001 Then
 If Not Initialized Then
 Initialized = True
 PrimesFlag = ChrW(1) & ChrW(257) & String(10000000, ChrW(256))
 Test = 3
 Do While Test <= MaxPrime
 For X = 2 * Test To MaxPrime Step Test
 PrimesFlag(X) = 0
 Next
 Test = Test + 1 - (Test > 2)
 Loop
 End If
 For X = FromNumber To ToNumber
 If PrimesFlag(X) = 1 Then PrimeCountBetween = PrimeCountBetween + 1
 Next
 Else
 PrimeCountBetween = -1
 End If
End Function

&&&

Solving the challenge of Count Prime Numbers in Range with Power Query

Solving the challenge of Count Prime Numbers in Range with Excel

Solving the challenge of Count Prime Numbers in Range with Python in Excel

Solving the challenge of Count Prime Numbers in Range with Excel VBA

Leave a Reply Cancel reply