### 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; (30678.13, « »)

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

*http://excel.todaysummary.com/q_microsoft-excel_358965.html*

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

- Hi Keith
- 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.todaysummary.com.discussions.microsoft.com> wrote in message

news:e21101c3eff7$906dee70$a601280a.excel.todaysummary.com.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

- Keith,
- 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.todaysummary.com.discussions.microsoft.com> wrote in message

news:e21101c3eff7$906dee70$a601280a.excel.todaysummary.com.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

- Hi Keith,
- "Keith" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message
news:e21101c3eff7$906dee70$a601280a.excel.todaysummary.com.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

- "Keith" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message
- 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

- Thank you to all who replied.
- 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

- Keith wrote:
- 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

David

"Frank Kabel" <frank.kabel.excel.todaysummary.com.freenet.de> schreef in bericht

news:%23RaUP%23$7DHA.2572.excel.todaysummary.com.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

- Here's another new suggestion:
- 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

- Where can I get the function
- 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.todaysummary.com.discussions.microsoft.com> wrote in message

news:E3DA10BF-7E1A-4DBF-827E-5F3A9FC0275A.excel.todaysummary.com.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

- Hi Nia,