MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Prime numbers


Posted by LeAnne on December 10, 2001 6:03 PM

Is there a formula or function that will check a number to see if it is a prime number? Say if a number is in A1 have a formula in B1 that would have true or false. Thanks for the help. LeAnne


Posted by Bariloche on December 10, 2001 6:32 PM

LeAnne,

I lifted this from John Walkenbach's site (http://j-walk.com/ss/excel/eee/eee015.txt):

**********************************POWER FORMULA TECHNIQUEby Bob Umlas
This array formula returns TRUE if the number in cell A1 is a prime number.
=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=
INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))
Use it as a conditional formatting formula, with A1 as the active cell
in the selection to be formatted.
Here's how Bob's amazing formula works. In a nutshell, the number is
divided by all potential prime factors, and the resulting array is tested
to see whether it contains a whole number. If is does, you have a prime
number. A limitation of this formula is that it cannot test numbers that
are greater than 65535^2. This is due to the array size constraint in
Excel 97/2000. **********************************


I haven't tested it, I'll just trust that Bob is right. :-)

enjoy

Posted by LeAnne on December 10, 2001 6:44 PM

Thanks

: Is there a formula or function that will check a number to see if it is a prime number? Say if a number is in A1 have a formula in B1 that would have true or false. Thanks for the help. LeAnne

Posted by Mark O'Brien on December 10, 2001 6:51 PM

You can do this using VBA.

Insert this code into a new module:

Public Function PrimeNumber(ByVal iNumber As Integer) As Boolean
Dim i As Integer
PrimeNumber = True

Select Case iNumber
Case 0
PrimeNumber = False
Exit Function

Case 1 Or 2
Exit Function

Case Else
For i = 2 To iNumber - 1
If iNumber Mod i = 0 Then
PrimeNumber = False
End If
Next

End Select
End Function


to test if A1 contains a prime number type this into B1

=PrimeNumber(A1)

Hope this helps

Is there a formula or function that will check a number to see if it is a prime number? Say if a number is in A1 have a formula in B1 that would have true or false. Thanks for the help. LeAnne

Posted by Mark O'Brien on December 10, 2001 6:56 PM

This only works for numbers up to 32,767. You could change the iNumber data type to Long (i.e. "ByVal iNumber as Long"), this would give numbers up to 2,147,483,647. I wouldn't recommend this though since it would take a long time to compute this.

You can do this using VBA. Insert this code into a new module: Public Function PrimeNumber(ByVal iNumber As Integer) As Boolean Dim i As Integer PrimeNumber = True Select Case iNumber Case 0 PrimeNumber = False Exit Function Case 1 Or 2 Exit Function Case Else For i = 2 To iNumber - 1 If iNumber Mod i = 0 Then PrimeNumber = False End If Next End Select End Function to test if A1 contains a prime number type this into B1 =PrimeNumber(A1) Hope this helps