# 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

Select Case iNumber
Case 0
Exit Function

Case 1 Or 2
Exit Function

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

End Select
End Function

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