Prime numbers

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

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

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

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