Finding primes

[Dave]

Hi all,

I need a formula or function that when I input a number then it will return
that prime. For example if I input 100 then I will get the 100th prime number.

Your assistance would be much appreciated.

D

 

[Gary''s Student]

http://support.microsoft.com/?kbid=202782

 

[Dave]

Hi,

Thanks for that. I was a bit suspicious when the site gave an incorrect
definition of a prime number and while I don't fully understand the code when
I ran it it gave an incorrect answer.

d

 

[Mike H]

Dave,

Why am I not surprised that the Microsoft website incorrectly defines a
prime number and then goes on to provide code that confirms the lack of
understanding. Perhaps you should submit as a suggestion to MS that it
corrects its site.

In the meantime try this. Alt+F11 to open VB editor. Right click 'This
Workbook' and insert module and paste the code below in. With a number in A1
call with

=nthprime(A1)
With 100 in a1 it will return the 100th prime.

It starts getting a bit slow with large numbers but it's the fastest way I
know.

Function NthPrime(PrimeRequired As Long) As Long
Dim i As Long
If PrimeRequired > 0 Then
For x = 2 To 100000000
If (x <> 2 And x Mod 2 = 0) Or x <> Int(x) Then GoTo 100
For i = 3 To Sqr(x) Step 2
If x Mod i = 0 Then GoTo 100
Next
foundprime = foundprime + 1
If foundprime = PrimeRequired Then
NthPrime = x
Exit Function
100
End If
Next
On Error GoTo 0
End If
End Function


Mike