# 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