Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
Does Excel has a formula to check if an integer is a prime number or not?
Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
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 "danpt" wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
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 "Sheeloo" wrote: 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 "danpt" wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
may be this ?
=IF(MOD(A1,2),"prime","not prime") On Oct 9, 8:56*am, danpt wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
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 "muddan madhu" wrote: may be this ? =IF(MOD(A1,2),"prime","not prime") On Oct 9, 8:56 am, danpt wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
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. Mike "muddan madhu" wrote: may be this ? =IF(MOD(A1,2),"prime","not prime") On Oct 9, 8:56 am, danpt wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
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? "Mike H" wrote: 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 "muddan madhu" wrote: may be this ? =IF(MOD(A1,2),"prime","not prime") On Oct 9, 8:56 am, danpt wrote: Does Excel has a formula to check if an integer is a prime number or not? Thank you |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
On Wed, 8 Oct 2008 21:31:00 -0700, Sheeloo
wrote: 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 { }. 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
prime number
On Oct 8, 9:56*pm, danpt wrote:
Does Excel has a formula to check if an integer is a prime number or not? Thank you Try this: =IF(SUMPRODUCT((MOD(A1,ROW(INDIRECT("1:"&A1)))=0)+ 0)2,"composite","prime") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to generate a list of prime number? | Excel Discussion (Misc queries) | |||
An add-in that allows you to find the highest prime in a number | Excel Discussion (Misc queries) | |||
how do I find prime factors of a number | Excel Worksheet Functions | |||
How do I test for a prime number? | Excel Worksheet Functions | |||
Prime number puzzle | Excel Worksheet Functions |