Prime numbers

[Dave]

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

 

[Mike H]

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

 

[Bob Phillips]

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)

 

[Dana DeLouis]

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)