Hello,

I know I can look it up on any amount of websites but that's not what I
want. I want to be able to input a number into Excel (say 99) and for it to
output the 99th prime number. Can anyone help please?

If possible both worksheet and VB solutions

Hi,

This will give any prime in the first 5m numbers, after that on my PC it
gets a bit too slow but if you must then increase the size of the x loop to
get additional primes. I'd be interested to see a worksheet solution, i'm
sure its dooable.

Sub Prime_Lending_has_Wrecked_the_economy()
Dim i As Long
primerequired = Val(InputBox("Enter your number"))
If primerequired 0 Then
On Error Resume Next
For x = 2 To 5000000 'increase if you want
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
MsgBox "Prime number " & primerequired & " is " & x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
If foundprime < primerequired Then
MsgBox "There are only " & foundprime & " prime numbers less than " & x - 1
End If
End Sub


Mike

"Dave" wrote:

Hello,

I know I can look it up on any amount of websites but that's not what I
want. I want to be able to input a number into Excel (say 99) and for it to
output the 99th prime number. Can anyone help please?

If possible both worksheet and VB solutions

This took 56 secs on my machine to go upto 5m numbers

Public Function GetPrimes(inst As Long) As Long
Dim aryPrimes
Dim i As Long
Dim PrimeCount As Long
Dim LastPrime As Long
Dim LastPrimeInst As Long
If inst < 26 Then
aryPrimes = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23, _
29, 31, 37, 41, 43, 47, 53, 59, 61, 67, _
71, 73, 79, 89, 97)
GetPrimes = aryPrimes(inst + (LBound(aryPrimes) = 0))
Exit Function
Else
PrimeCount = 25 'ignore prior to 71
For i = 101 To 5000000 Step 2

If IsPrime(i) Then
LastPrime = i
LastPrimeInst = PrimeCount
PrimeCount = PrimeCount + 1
If PrimeCount = inst Then
GetPrimes = i
Exit For
End If
End If
Next i
End If
If GetPrimes = 0 Then Debug.Print LastPrime, LastPrimeInst
End Function

'-----------------------------------------------------------*------
Private Function IsPrime(num As Long) As Boolean
'-----------------------------------------------------------*------
Dim i As Long
IsPrime = True

If num = 2 Then
IsPrime = True
ElseIf num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If

End Function




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mike H" wrote in message
...
Hi,

This will give any prime in the first 5m numbers, after that on my PC it
gets a bit too slow but if you must then increase the size of the x loop
to
get additional primes. I'd be interested to see a worksheet solution, i'm
sure its dooable.

Sub Prime_Lending_has_Wrecked_the_economy()
Dim i As Long
primerequired = Val(InputBox("Enter your number"))
If primerequired 0 Then
On Error Resume Next
For x = 2 To 5000000 'increase if you want
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
MsgBox "Prime number " & primerequired & " is " & x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
If foundprime < primerequired Then
MsgBox "There are only " & foundprime & " prime numbers less than " & x -
1
End If
End Sub


Mike

"Dave" wrote:

Hello,

I know I can look it up on any amount of websites but that's not what I
want. I want to be able to input a number into Excel (say 99) and for it
to
output the 99th prime number. Can anyone help please?

If possible both worksheet and VB solutions

Hi. I seem to tweak a little speed improvement by adding an Exit Function
in the IsPrime Routine.

If num Mod i = 0 Then
IsPrime = False
Exit Function '<-- Added
End If

(Note: Max input value is 348,513 with a Max Output of 5,000,000)
--
Dana DeLouis



"Bob Phillips" wrote in message
...
This took 56 secs on my machine to go upto 5m numbers

Public Function GetPrimes(inst As Long) As Long
Dim aryPrimes
Dim i As Long
Dim PrimeCount As Long
Dim LastPrime As Long
Dim LastPrimeInst As Long
If inst < 26 Then
aryPrimes = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23, _
29, 31, 37, 41, 43, 47, 53, 59, 61, 67, _
71, 73, 79, 89, 97)
GetPrimes = aryPrimes(inst + (LBound(aryPrimes) = 0))
Exit Function
Else
PrimeCount = 25 'ignore prior to 71
For i = 101 To 5000000 Step 2

If IsPrime(i) Then
LastPrime = i
LastPrimeInst = PrimeCount
PrimeCount = PrimeCount + 1
If PrimeCount = inst Then
GetPrimes = i
Exit For
End If
End If
Next i
End If
If GetPrimes = 0 Then Debug.Print LastPrime, LastPrimeInst
End Function

'-----------------------------------------------------------*------
Private Function IsPrime(num As Long) As Boolean
'-----------------------------------------------------------*------
Dim i As Long
IsPrime = True

If num = 2 Then
IsPrime = True
ElseIf num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If

End Function




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mike H" wrote in message
...
Hi,

This will give any prime in the first 5m numbers, after that on my PC it
gets a bit too slow but if you must then increase the size of the x loop
to
get additional primes. I'd be interested to see a worksheet solution, i'm
sure its dooable.

Sub Prime_Lending_has_Wrecked_the_economy()
Dim i As Long
primerequired = Val(InputBox("Enter your number"))
If primerequired 0 Then
On Error Resume Next
For x = 2 To 5000000 'increase if you want
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
MsgBox "Prime number " & primerequired & " is " & x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
If foundprime < primerequired Then
MsgBox "There are only " & foundprime & " prime numbers less than " & x -
1
End If
End Sub


Mike

"Dave" wrote:

Hello,

I know I can look it up on any amount of websites but that's not what I
want. I want to be able to input a number into Excel (say 99) and for it
to
output the 99th prime number. Can anyone help please?

If possible both worksheet and VB solutions