How to determine the prime numbers?

[Guest]

Does anyone have any suggestions on how to determine the prime numbers?
There is a given number 10 in cell A2, I would like to determine the next
prime numbers below and over this given number 10. In this case, 11 will be
returned in cell A1, and 7 will be returned in A3.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Guest]

This URL has the VBA code for creating a UDF that will determine if a number
is prime or not.

http://www.bluemoosetech.com/micros...hp?jid=29&title=Microsoft Excel Prime Numbers

 

[Guest]

set up a list with primes and then use index match to find the one above and
below your number

 

[Bernard Liengme]

No need for VBA this time:
=IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0)),"Not
Prime","Prime")
must be array-entered (ie with SHIFT+CTRL+ENTER)
best wishes

 

[Roger Govier]

Hi Bernard

Nice solution, but I think it works without being array entered.

 

[Bernie Deitrick]

Eric,

To find the next higher prime, use the UDF below (Copy the code and paste it into a codemodule) like
this:
=NextPrime(A2,TRUE)

and to find the next lower prime, use it like this:
=NextPrime(A2,FALSE)

HTH,
Bernie
MS Excel MVP

Option Explicit
Function NextPrime(inRange As Long, Increase As Boolean) As Variant
Dim i As Long
Dim j As Long
Dim boolSolved As Boolean
Dim isPrime As Boolean

boolSolved = False
i = inRange

While Not boolSolved
i = i + IIf(Increase, 1, -1)

If i < 2 Then
NextPrime = "No prime is less than " & inRange
Exit Function
End If

If i Mod 2 = 0 Then
If i = 2 Then
NextPrime = 2
Exit Function
Else
i = i + IIf(Increase, 1, -1)
End If
End If

isPrime = True
For j = 3 To i ^ 0.5 Step 2
If i Mod j = 0 Then
isPrime = False
End If
Next j

If isPrime Then
NextPrime = i
Exit Function
End If

Wend

End Function

 

[Bernie Deitrick]

Bernard,

That formula works to determine if the entered number is prime, but the OP wants the first prime
that is higher, and the first prime that is lower.

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

I know but I couldn't help showing my solution!
That formula that even impressed Bob Umlas
Please forgive me showing off!!!
best wishes

 

[Bernard Liengme]

True, I was working from memory (core needs replacing)
best wishes

 

[Tyro]

I put this formula in cell B1. It returns Not Prime for every number entered
in A1. I tried both normal and array. I copied the formula from B1 and
pasted it here.

=IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0)),"Not
Prime","Prime")

Any ideas why it doesn't work?