Determine if a number is prime

  • Thread starter Thread starter Guest
  • Start date Start date
Hi Harlan:

Yep ... realized that later and fixed the function in another branch of the
thread. Now it reads:

Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num < 2 Or (Num <> 2 And Num Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

Regards,

Vasant.
 
I saw a message where someone suggested using "=ISPRIME(num)".
I want to make this statement =IF(IsPrime(num),"Prime","Composite").
When I enter it in I get an error. I don't know where to get the function
IsPrime. Is there an add-on that I need to download? How else can I check a
number to see if it is prime?
Click to expand...

Gnumeric comes with an ISPRIME function, but I believe it's the only spreadsheet
that does. In any event, if num would be less than 8.58 billion, you could use
the array formula

=IF(AND(num<>INT(num/2)*2,num<>INT(num/(1+2*ROW(INDIRECT("1:"
&INT(SQRT(num)/2)))))*(1+2*ROW(INDIRECT("1:"&INT(SQRT(num)/2))))<>0),
"Prime","Composite")

[Yes, you have to use x<>INT(x/k)*k rather than MOD(x,k)<>0 because Microsoft,
bless their little innumerate skulls, fubarred Excel's MOD function for large
quotients. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;119083&Product=xlw

for details. You can use the Windows Calculator applet to see that some
Microsoft software was written without this bug, so either the Excel programmers
or the bean counters just don't care about fixing it.]

In terms of arithmetic operations, it's grossly wasteful, but as a practical
matter it may recalc faster than udfs with more efficient algorithms that are
nevertheless burdened by the Excel/VBA udf interface.
 
One might be able to eliminate some searches with something like this early
in the routine.

Sub GeneralIdea()
Dim n
n = 100000011
Select Case n Mod 6
Case 1, 5
' Could still be a Prime...
' Further research needed
MsgBox n & " Could be a Prime"

Case Else
' Not possible
MsgBox n & " is not a Prime"
End Select
End Sub
 
Back
Top