How to determine the prime numbers?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
set up a list with primes and then use index match to find the one above and
below your number
 
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
 
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
 
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
 
I know but I couldn't help showing my solution!
That formula that even impressed Bob Umlas
Please forgive me showing off!!!
best wishes
 
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?
 
Back
Top