prime number

D

danpt

Does Excel has a formula to check if an integer is a prime number or not?
Thank you
 
S

Sheeloo

No.

Thanks to Chip Pearson for providing this formula
________________
The formula below will test the number in cell C8 and return the word prime
if it is prime or the string not prime if the number is not prime.

=IF(OR(C8=1,C8=2,C8=3),"prime",
IF(AND((MOD(C8,ROW(INDIRECT("2:"&C8-1)))<>0)),"prime","not prime"))

This is an array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.
________________
For details go to http://www.cpearson.com/excel/PrimeNumbers.aspx
 
M

Mike H

The number 1 isn't a prime number. I can't see how to adjust the formula
other than with an additional IF statement

=IF(C8=1,"not prime",IF(OR(C8=2,C8=3),"prime",
IF(AND((MOD(C8,ROW(INDIRECT("2:"&C8-1)))<>0)),"prime","not prime")))

or perhaps a UDF

Function IsPrime(Number As Single) As Boolean
Dim x As Long
If Number < 2 Or (Number <> 2 And Number Mod 2 = 0) _
Or Number <> Int(Number) Then Exit Function
For x = 3 To Sqr(Number) Step 2
If Number Mod x = 0 Then Exit Function
Next
IsPrime = True
End Function

Mike
 
M

Mike H

hi,

Every odd number isn't prime, the correct definition is that a prime number
has 2 (and only 2) distinct natural number divisors.

Mike
 
J

James Silverton

Mike wrote on Thu, 9 Oct 2008 01:33:01 -0700:
Every odd number isn't prime, the correct definition is that a
prime number has 2 (and only 2) distinct natural number
divisors.

"muddan madhu" wrote:
Click to expand...

I came across a subroutine in VB that might be of interest:
http://www.devx.com/vb2themax/Tip/19051
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not
 
D

danpt

First of all, thank you all.
1 is called Identity number of multiplication (I think).
21 is not a prime, but 11 should be a prime per definition "A number that
has itself and unity as its only factors." Isn't it?
 
R

Ron Rosenfeld

No.

Thanks to Chip Pearson for providing this formula
________________
The formula below will test the number in cell C8 and return the word prime
if it is prime or the string not prime if the number is not prime.

=IF(OR(C8=1,C8=2,C8=3),"prime",
IF(AND((MOD(C8,ROW(INDIRECT("2:"&C8-1)))<>0)),"prime","not prime"))

This is an array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.
Click to expand...

Of course, this formula seems to be limited to the range of numbers up to the
number of rows in the spreadsheet + 1.

In Excel 2007, it returns a REF error for values > 1,048,577
--ron
 
A

amontes

Does Excel has a formula to check if an integer is a prime number or not?
Thank you
Click to expand...

Try this:
=IF(SUMPRODUCT((MOD(A1,ROW(INDIRECT("1:"&A1)))=0)+0)>2,"composite","prime")
 
Top