Hi,

This an Excel forum and not one for number theory so I'll keep my
explanation brief.

Your definition of a prime is incorrect but because it is nearly correct is
the one most people are comfortable with. The correct definition of a prime
number is:-

Any 'natural number' that has 2 (and only 2) distinct natural number divisors.

As you can see 1 fails this test because it has only 1 distinct natural
number divisor and if you want to understand why this is important Google for
the 'Fundamental Theory of Aritmmetic'

Mike

"Dave" wrote:

Thanks, that's excatly what I want but you have confused me by omitting 1
which because it is divisible by 1 and itself must be prime. why do you say
it isn't?

d

"Mike H" wrote:

Hi,

Try this function which is good for the first 500 thousand (ish) primes but
it does get a bit slow as the numbers get bigger.

Call with
=nthprime(n) where n is the prime you want

Function NthPrime(ReqPrime As Long) As Long
Dim i As Long, foundprime As Long, x As Long
For x = 2 To 10000000
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 = ReqPrime Then
NthPrime = x
Exit Function
100
End If
Next
On Error GoTo 0
End Function

For testing for primes try this taken from Chip Pearson's webiste

=IF(C8=1,"not
prime",IF(OR(C8=2,C8=3),"prime",IF(AND((MOD(C8,ROW (INDIRECT("2:"&C8-1)))<0)),"prime","not prime")))

Note I've modified Chip's formula to exclude 1 which the original formula
incorrectly evaluates as a prime

Mike

"Dave" wrote:

Hello,

I would like a function which I could aks for the nth prime number. e.g.

=myfunction(10)

which would give the tenth prime.
Also I would like a function/formula that would test a number for being a
prime.

Thank you