Tags: excel, function, microsoft, msdn, number, numbers, obtain, prime, return, software

Prime Numbers?

On Microsoft » Microsoft Excel

7,598 words with 9 Comments; publish: Fri, 23 May 2008 08:10:00 GMT; (306109.38, « »)

Hello,

I am looking for a function that will return a prime

number in Excel. Is there such a thing? Is it possible

to obtain prime numbers in a simple and easy way?

Keith

All Comments

Leave a comment...

  • 9 Comments
    • Hi Keith

      =3

      <vbg>

      but I don't thinks this is what you want. what are you trying to

      achieve (generating prime numbers within a range, getting the largest

      prime within a range, sieving primes, etc?). You may have a look at

      http://www.utm.edu/research/primes/

      Frank

      Keith wrote:

      > Hello,

      > I am looking for a function that will return a prime

      > number in Excel. Is there such a thing? Is it possible

      > to obtain prime numbers in a simple and easy way?

      > Keith

      #1; Fri, 23 May 2008 08:12:00 GMT
    • Keith,

      Here is a routine originally presented by Myrna Larson that I use to test

      for primes.

      =IsPrime(num)

      returns True or False.

      Function IsPrime(TestNum As Long)

      Dim PrimeCnt As Long

      Dim y As Long

      Dim x As Long

      Dim i As Long

      Dim Flag As Boolean

      Dim Primes() As Long

      Dim NumStop As Double

      ReDim Primes(1 To 2)

      NumStop = Sqr(TestNum)

      If TestNum = 1 Or TestNum = 2 Or TestNum = 3 Or TestNum = 5 Then

      IsPrime = True

      Exit Function

      End If

      Primes(1) = 2

      Primes(2) = 3

      PrimeCnt = 2

      x = 3

      Do

      x = x + 2

      For y = 3 To Sqr(x) Step 2

      If x Mod y = 0 Then GoTo NoPrime1

      Next y

      PrimeCnt = PrimeCnt + 1

      ReDim Preserve Primes(1 To PrimeCnt)

      Primes(PrimeCnt) = x

      NoPrime1:

      Loop Until Primes(PrimeCnt) > NumStop

      For i = LBound(Primes) To UBound(Primes)

      If TestNum Mod Primes(i) = 0 Then

      IsPrime = False

      Exit Function

      End If

      Next

      IsPrime = True

      End Function

      --

      HTH

      Bob Phillips

      ... looking out across Poole Harbour to the Purbecks

      (remove nothere from the email address if mailing direct)

      "Keith" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:e21101c3eff7$906dee70$a601280a.excel.questionfor.info.phx.gbl...

      > Hello,

      > I am looking for a function that will return a prime

      > number in Excel. Is there such a thing? Is it possible

      > to obtain prime numbers in a simple and easy way?

      > Keith

      #2; Fri, 23 May 2008 08:13:00 GMT
    • Hi Keith,

      If you do a search on Internet you will find lists of prime numbers and

      agorithms to create such a list.

      What exactly do you require? Just one prome numer or a function that takes a

      prime number randomly from a list?

      --

      Kind Regards,

      Niek Otten

      Microsoft MVP - Excel

      "Keith" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:e21101c3eff7$906dee70$a601280a.excel.questionfor.info.phx.gbl...

      > Hello,

      > I am looking for a function that will return a prime

      > number in Excel. Is there such a thing? Is it possible

      > to obtain prime numbers in a simple and easy way?

      > Keith

      #3; Fri, 23 May 2008 08:14:00 GMT
    • "Keith" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:e21101c3eff7$906dee70$a601280a.excel.questionfor.info.phx.gbl...

      > Hello,

      > I am looking for a function that will return a prime

      > number in Excel. Is there such a thing? Is it possible

      > to obtain prime numbers in a simple and easy way?

      > Keith

      see the code at the bottom of this page

      http://members.chello.nl/n.sterk/ExcelPages/Files/excel_files.htm

      Keith Willshaw

      #4; Fri, 23 May 2008 08:15:00 GMT
    • Thank you to all who replied.

      I actually am attempting to generate a list of primes, so

      the routine that tests for primes will work, and I'll also

      look at the other suggestions.

      thanks very much,

      keith

      #5; Fri, 23 May 2008 08:16:00 GMT
    • Keith wrote:

      > Thank you to all who replied.

      > I actually am attempting to generate a list of primes, so

      > the routine that tests for primes will work, and I'll also

      > look at the other suggestions.

      > thanks very much,

      > keith

      Hi Keith

      I would suggest to import an existing list of primes. e.g.

      http://www.utm.edu/research/primes/lists/small/1000.txt

      Frank

      #6; Fri, 23 May 2008 08:17:00 GMT
    • Here's another new suggestion:

      I do not quite know if this software can contribute,

      but i just wanted to let you know, that we developed a collection

      of functions (library, dll) for working with real big numbers.

      (...numbers bigger than the normal data types a programmer can handle.)

      It was made for Visual Basic, but can be used in

      any language that can invoke a .DLL (such as C++, VBA in Excel, Access or

      whatever)

      It's the only DLL available for Windows for UNLIMITED BIG NUMBERS

      with functions such as: +/-* Power2, Power10, MOD DIVIDE, ISPRIME, COMPARE,

      Xor etc...

      Calculations are sometimes even faster than you are used to, cause

      everything was made in assembly. It's shareware and it is online on

      http://www.big-numbers.com

      David

      "Frank Kabel" <frank.kabel.excel.questionfor.info.freenet.de> schreef in bericht

      news:%23RaUP%23$7DHA.2572.excel.questionfor.info.TK2MSFTNGP09.phx.gbl...

      > Keith wrote:

      > > Thank you to all who replied.

      > > I actually am attempting to generate a list of primes, so

      > > the routine that tests for primes will work, and I'll also

      > > look at the other suggestions.

      > >

      > > thanks very much,

      > >

      > > keith

      > Hi Keith

      > I would suggest to import an existing list of primes. e.g.

      > http://www.utm.edu/research/primes/lists/small/1000.txt

      > Frank

      >

      #7; Fri, 23 May 2008 08:18:00 GMT
    • Where can I get the function

      =IsPrime(num

      I typed it in a cell and got an error message

      Is there an add-on for more functions in Excel

      I want an IF test for a cel

      =IF(IsPrime(B1),"Prime","Composite")

      #8; Fri, 23 May 2008 08:19:00 GMT
    • Hi Nia,

      Here is a routine originally presented by Myrna Larson that I use to test

      for primes.

      =IsPrime(num)

      returns True or False.

      Function IsPrime(TestNum As Long)

      Dim PrimeCnt As Long

      Dim y As Long

      Dim x As Long

      Dim i As Long

      Dim Flag As Boolean

      Dim Primes() As Long

      Dim NumStop As Double

      ReDim Primes(1 To 2)

      NumStop = Sqr(TestNum)

      If TestNum = 1 Or TestNum = 2 Or TestNum = 3 Or TestNum = 5 Then

      IsPrime = True

      Exit Function

      End If

      Primes(1) = 2

      Primes(2) = 3

      PrimeCnt = 2

      x = 3

      Do

      x = x + 2

      For y = 3 To Sqr(x) Step 2

      If x Mod y = 0 Then GoTo NoPrime1

      Next y

      PrimeCnt = PrimeCnt + 1

      ReDim Preserve Primes(1 To PrimeCnt)

      Primes(PrimeCnt) = x

      NoPrime1:

      Loop Until Primes(PrimeCnt) > NumStop

      For i = LBound(Primes) To UBound(Primes)

      If TestNum Mod Primes(i) = 0 Then

      IsPrime = False

      Exit Function

      End If

      Next

      IsPrime = True

      End Function

      HTH

      Bob Phillips

      ... looking out across Poole Harbour to the Purbecks

      (remove nothere from the email address if mailing direct)

      "Nia" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:E3DA10BF-7E1A-4DBF-827E-5F3A9FC0275A.excel.questionfor.info.microsoft.com...

      > Where can I get the function?

      > =IsPrime(num)

      > I typed it in a cell and got an error message.

      > Is there an add-on for more functions in Excel?

      > I want an IF test for a cell

      > =IF(IsPrime(B1),"Prime","Composite")

      #9; Fri, 23 May 2008 08:20:00 GMT