Determine if a number is prime

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 said:
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
Click to expand...
to see if it is prime?
 
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
 
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.
 
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
 
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.
 
Then you can use the function like any other function:

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

--

Vasant

Nia said:
Thanks for letting me know I am on the right track. I created the module
Click to expand...
in the VB Editor already but I don't know how to access it in my
spreadsheet. Next step please.
 
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.
 
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 said:
Negatives, zero and one are not prime by definition. I will adjust the
Click to expand...
formula but I thought you might like to know.
I am surprised that there is no add-in or standard formula for primes. It
Click to expand...
makes me think less of Excel as a program and Microsoft as a company when
they can't accommodate 4th grade math.
 
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
 
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?
 
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.
 
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 said:
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
Click to expand...
Book1.xls?
 
You may have set the name of the module to IsPrime.

--

Vasant

Nia said:
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
Click to expand...
to remove the first "isprime." the function no longer works.
 
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?
 
In the interest of accuracy:

If Num > 2

should be:

If Abs(Num) > 2
Click to expand...
...

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.
 
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.
Click to expand...
...

It's *not* interpretted. It's just not compiled into machine code, but it *IS*
compiled.
 
...
...
. . . So your first statement should be

If Num < 2 Or (Num - 2) Mod 2 = 0 Then Exit Function
Click to expand...
...

Figures I'd screw it up. Make that

If Num < 2 Or (Num - 3) Mod 2 = 1 Then Exit Function
 
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 said:
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
Click to expand...
taking all your time?
 
Back
Top