Determine if a number is prime

[Nia]

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?

[Vasant Nanavati]

You need to put this function in a standard module in your workbook:

Function IsPrime(Num As Long) As Boolean
Dim i As Long
If Num = 4 Then Exit Function
For i = 3 To Abs(Num) / 2 Step 2
If i = 4 Or Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message
news:167CCE2C-DFC2-4CC4...@microsoft.com...

[Vasant Nanavati]

Sorry; missed the line testing for even numbers. Use:

Function IsPrime(Num As Long) As Boolean
Dim i As Long

If Num > 2 And Num Mod 2 = 0 Then Exit Function

For i = 3 To Abs(Num) / 2 Step 2

If Num Mod i = 0 Then Exit Function

Next
IsPrime = True
End Function

--

Vasant

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OKWa3jz...@tk2msftngp13.phx.gbl...

[Vasant Nanavati]

In the interest of accuracy:

If Num > 2

should be:

If Abs(Num) > 2

--

Vasant

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message

news:ORDpsrzJ...@tk2msftngp13.phx.gbl...

[Vasant Nanavati]

When you are in the Visual Basic Editor, use the menu to insert a new module
(Insert | Module). The paste the function into it. Let me give you a faster
and better variation of the function:

Function IsPrime(Num As Long) As Boolean
Dim i As Long

If Abs(Num) > 2 And Num Mod 2 = 0 Then Exit Function
For i = 3 To Sqr(Abs(Num)) Step 2
If Num Mod i = 0 Then Exit Function

Next
IsPrime = True
End Function

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:E2A4F434-09B2-4B46...@microsoft.com...
> I understand the function. Thank you.
> I don't know where to put it though.
> I researched "standard module".
> I don't think I record a macro but I did find out that I can make a module
in Visual Basic Editor.
> Now I don't know how to access it from my spredsheet.
>
> I don't want you to teach me but please direct me to more info.
> I am pretty geeky for a mom so a good tech site would be fine.

[Vasant Nanavati]

Then you can use the function like any other function:

=IF(isprime(A1),"prime","not prime")

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:C066647A-D232-489F...@microsoft.com...
> Thanks for letting me know I am on the right track. I created the module
in the VB Editor already but I don't know how to access it in my
spreadsheet. Next step please.

[Vasant Nanavati]

Sadly, my math class days are far behind me and I had forgotten that
negative integers cannot be prime. And I just assumed that no one would test
0 or 1. However, for the sake of completeness and correctness:

Function IsPrime2(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

IsPrime2 = True
End Function

I think this is about as short and sweet as I can make it.

In regard to your point about Excel, while it has many shortcomings, keep in
mind that it is primarily a business tool and not a math program!

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:1922A4ED-0FFC-4A25...@microsoft.com...
> Negatives, zero and one are not prime by definition. I will adjust the
formula but I thought you might like to know.
> I am surprised that there is no add-in or standard formula for primes. It
makes me think less of Excel as a program and Microsoft as a company when
they can't accommodate 4th grade math.

[Jerry W. Lewis]

In an interpreted language like VBA, it is probably faster to set up the
loop as you have, but there are additional patterns that could be exploited.

Your loop searches potential divisors by starting from 3 and
incrementing by 2 to skip even numbers.

If you first eliminate 2 and 3 as potential divisors, the loop could
start at 5 and increment by 2, 4, 2, 4, ... to skip numbers divisible by
3, as well as even numbers.

If you first eliminate 2, 3, and 5 as potential divisors, the loop could
start at 7 and increment by 4, 2, 4, 2, 4, 6, 2, 6, ... to skip numbers
divisible by 5, 3, or 2.

...

Jerry

[Nia]

It seems a little more difficult or I am doing something wrong.
When I create the module in VB it puts it in Book1.xls.
Then I have to put it in my formula as follows:

=IF(Book1.xls!IsPrime.IsPrime(C106),"Prime","Composite")

Is there a way to make it only in my sheet so I don't have to reference Book1.xls?

[Vasant Nanavati]

Hi Jerry:

I thought about doing that, but even on my creaky system a test routine took
just 4 seconds to test *every*positive integer through 1,000,000. At that
point I decided it was good enough for government work, as they say! <g>

Regards,

Vasant.

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:4085F72C.5060406@no_e-mail.com...

[Vasant Nanavati]

You have to create a module in the workbook that you want to use the
function in. Don't create it in a new workbook. In the Visual Basic Editor,
make sure you select the correct workbook in the Project Explorer Window
(top left) and then insert a module to paste the code into.

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:17E43EB7-1A16-4384...@microsoft.com...

[Nia]

Thank you for being so patient with this. I really appreciate it.
I did create the module in the same book that the spreadsheet is in.

I tried removing the reference to the book and that worked but when I try to remove the first "isprime." the function no longer works.

works =IF(Book1.xls!IsPrime.IsPrime(C106),"Prime","Composite")
works =IF(IsPrime.IsPrime(C106),"Prime","Composite")
error =IF(IsPrime(C106),"Prime","Composite")

[Vasant Nanavati]

You may have set the name of the module to IsPrime.

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:D6E4F5D3-5172-4739...@microsoft.com...

[Harlan Grove]

"Vasant Nanavati" wrote...

>In the interest of accuracy:
>
>If Num > 2
>
>should be:
>
>If Abs(Num) > 2

..

Why? Prime numbers must be positive numbers. Otherwise, you get into the
absurdity that 3 has factorizations (1,3), {-1,-3), (1,-1,-3), so can't be prime
because there's more than one order-insensitive integer factorization.

Negatives are never prime, and a precise definition of a prime number is that it
has no positive integer factors other than itself and 1. So your first statement
should be

If Num < 2 Or (Num - 2) Mod 2 = 0 Then Exit Function

and you should eliminate all subsequent Abs calls.

--
To top-post is human, to bottom-post and snip is sublime.

[Harlan Grove]

"Jerry W. Lewis" wrote...

>In an interpreted language like VBA, it is probably faster to set up the
>loop as you have, but there are additional patterns that could be exploited.

..

It's *not* interpretted. It's just not compiled into machine code, but it *IS*
compiled.

[Harlan Grove]

"Harlan Grove" wrote...
..
> . . . So your first statement should be

>
>If Num < 2 Or (Num - 2) Mod 2 = 0 Then Exit Function

..

Figures I'd screw it up. Make that

If Num < 2 Or (Num - 3) Mod 2 = 1 Then Exit Function

[Vasant Nanavati]

The default name would be Module1 (or 2 or 3). You can give it any name you
like or leave the default. The confusion probably arose from the fact that
you gave the module the same name as the function name.

--

Vasant

"Nia" <anon...@discussions.microsoft.com> wrote in message

news:8841200E-9623-47B6...@microsoft.com...
> Am I supposed to not-name the module?
> Do I give a default name or no name?
>
> Could you please tell me where I could learn more about this without
taking all your time?

[Vasant Nanavati]

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.

"Harlan Grove" <hrl...@aol.com> wrote in message
news:OuChc.1836$H4....@www.newsranger.com...

[Harlan Grove]

"=?Utf-8?B?Tmlh?=" wrote...

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.

[Dana DeLouis]

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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:4085F72C.5060406@no_e-mail.com...

[Vasant Nanavati]

I was wondering when we'd hear from you in this thread, Dana! <g>

Regards,

Vasant.

"Dana DeLouis" <del...@bellsouth.net> wrote in message
news:u5WPQKAK...@TK2MSFTNGP12.phx.gbl...