Udf (or actual function) to tell if a number is prime.

[Gene Klein]

I am sure I asked this years ago, but I am looking for a function that determines if a number is prime. It can be a UDF or an actual built in function. (I tried =isprime of course)

It would work like this =isprime(5) would return 1
=isprime(6) would return 0

Gene Klein

 

[Momentman]

Got this from Daily Dose of Excel » Blog Archive » Is this number prime? and modified slightly

Function IsPrime(Number As Single) As Integer
    If Number < 2 Or (Number > 2 And Number Mod 2 = 0) Or Number <> Int(Number) Then Exit Function
    For I = 3 To Sqr(Number)
        If Number Mod I = 0 Then Exit Function
    Next
    IsPrime = 1
End Function

And a small example here

Excel 2010
	G	H
5	Number	Prime or Not
6	13	1
7	11	1
8	15	0
9	16	0
10	17	1
11	19	1
12	21	0
13	23	1
14	34	0
15	35	0
16	31	1
Sheet6

Cell Formulas
Range	Formula
H6	=isprime(G6)

 

[Gene Klein]

MomentMan - this worked great. Thank you

Gene Klein

 

[Gene Klein]

I tried to use that function in the following macro, but I got an error. Can anyone see my problem?

Function IsPrime(Number As Single) As Integer
If Number < 2 Or (Number > 2 And Number Mod 2 = 0) Or Number <> Int(Number) Then Exit Function
For i = 3 To Sqr(Number)
If Number Mod i = 0 Then Exit Function
Next
IsPrime = 1
End Function

Sub CheckForTriplets()

Dim i, n, check As Long

For i = 5 To 1000 Step 2
check = IsPrime(i) + IsPrime(i + 2) + IsPrime(i + 6)
If check = 3 Then report = report + 1
Next
MsgBox report
End Sub

 

[JoeMo]

Try dimensioning i as Single:

Sub CheckForTriplets()

 Dim i as Single, check As Long

 For i = 5 To 1000 Step 2
 check = IsPrime(i) + IsPrime(i + 2) + IsPrime(i + 6)
 If check = 3 Then report = report + 1
 Next
 MsgBox report
 End Sub

 

[Rick Rothstein]

Try dimensioning i as Single:

Since we are talking about prime numbers, I wonder why the function isn't receiving values as type Long instead of type Single?

 

[shg]

A little longer ...

Function IsPrime(d As Double, Optional bFirstFactor As Boolean = False) As Variant
  '   Returns                    if d is
  ' -----------  ----------------------------------------------
  '  #VALUE!      < 2
  '  "Too big!"   > 1E+15
  '  #VALUE!      <> Int(d)
  '  FALSE        composite and bFirstFactor = False or omitted
  '  1st factor   composite and bFirstFactor = True
  '  TRUE         prime

  Dim dDiv  As Double
  Dim dRt   As Long

  If d < 2# Then
    IsPrime = CVErr(xlErrValue)
    Exit Function  
  ElseIf d = 2# Or d = 5# Then
    IsPrime = True
    Exit Function  
  ElseIf d > 1000000000000000# Then
    IsPrime = "Too big!"
    Exit Function  
  ElseIf Int(d) <> d Then
    IsPrime = CVErr(xlErrValue)
    Exit Function  
  End If

  ' can't use Mod with numbers bigger than Longs, so ...
  Select Case Right(Format(d, "0"), 1)
    Case "0", "2", "4", "6", "8"
      IsPrime = IIf(bFirstFactor, 2, False)
    Case "5"
      IsPrime = IIf(bFirstFactor, 5, False)
    Case Else
      For dRt = 3 To Int(Sqr(d)) Step 2
        dDiv = d / dRt
        If Int(dDiv) = dDiv Then
          IsPrime = IIf(bFirstFactor, dRt, False)
          Exit Function  
        End If
      Next dRt
      IsPrime = True
  End Select
End Function

 

[GTO]

@shg:

If bored and/or it's not too painful, would you respond to Rick's question? I recall a thread where this was discussed, but that is where my blond memory stops. I do not recall whether it was the n in For n = [some value] To [some other value] that was the issue, or whether it was where the called Function's parameter n was affected, or ?????

Thank you so much regardless of whether you get to reply or not.

Mark

 

[Kyle123]

At a guess because Prime numbers have to be whole numbers, if declared as long decimals will be coerced into their whole number value - 5.3 would become 5 for example which would return as Prime when it is clearly not.

The input as Single prevents this

shg's function addresses with:

  ElseIf Int(d) <> d Then
    IsPrime = CVErr(xlErrValue)
    Exit Function  
  End If

 

[Gene Klein]

I don't think that was the cause of my original issue. I never had decimals as input.

Gene