Prime numbers

**Gearcutter** · 03-07-2007, 11:40 AM

Is there a function that will indicate a prime number or if not a prime numberwhat the lowest common denomenator is?

**Carim** · 03-07-2007, 12:06 PM

Hi,

For prime numbers, you can use the UDF code proposed by Myrna Larson ...

Function Prime(TestNum As Long) 
  Dim PrimeCnt As Long 
  Dim y As Long 
  Dim x As Long 
  Dim Flag As Boolean 
  Dim Primes() As Long 
  Dim NumStop As Double 
  ReDim Primes(1 To 2) 
  NumStop = Sqr(TestNum) 
  If TestNum = 1 Or _ 
     TestNum = 2 Or _ 
     TestNum = 3 Or _ 
     TestNum = 5 Then 
       Prime = True 
       Exit Function 
  End If 
  Primes(1) = 2 
  Primes(2) = 3 
  PrimeCnt = 2 
  x = 3 
  Do 
    x = x + 2 
    For y = 3 To Sqr(x) Step 2 
      If x Mod y = 0 Then GoTo NoPrime1 
    Next y 
    PrimeCnt = PrimeCnt + 1 
    ReDim Preserve Primes(1 To PrimeCnt) 
    Primes(PrimeCnt) = x 
NoPrime1: 
  Loop Until Primes(PrimeCnt) > NumStop 
  For i = LBound(Primes) To UBound(Primes) 
    If TestNum Mod Primes(i) = 0 Then 
      Debug.Print i, Primes(i), TestNum / Primes(i) 
      Prime = False 
      Exit Function 
    End If 
  Next 
  Prime = True 
End Function

**Gearcutter** · 03-07-2007, 12:47 PM

Thanks Carim that works fine
Regards Howard

**daddylonglegs** · 03-07-2007, 06:47 PM

Originally Posted by Gearcutter

Is there a function that will indicate a prime number or if not a prime numberwhat the lowest common denomenator is?

Do you mean the lowest factor (not counting 1)? This formula will generate the lowest factor (not counting 1) of a number in A1

=MIN(IF(MOD(A1,ROW(INDIRECT("1:"&FLOOR(A1^(1/2),1)))+1)=0,ROW(INDIRECT("1:"&FLOOR(A1^(1/2),1)))+1))

confirmed with CTRL+SHIFT+ENTER

If the result is 0 then A1 contains a prime number

note: it won't work if A1 is greater than 268435455

....or here's a UDF, courtesy of Bernard Liengme, which will return either "prime" or give the lowest factor

Function prime(num)
    Dim j, k, remain As Integer
    j = Int(num)
    If j <> num Then
      prime = "NonInt"
      Exit Function
    End If
    j = Sqr(num)
    For k = 2 To j
     remain = num Mod k
     If remain = 0 Then
        prime = k
        Exit Function
     End If
    Next k
    prime = "Prime"
End Function

**Gearcutter** · 03-08-2007, 09:03 AM

Hi Daddylonglegs, thank you for the formula and the function, this one is even better as it will return the lcd if the number is not a prime the array formula I will certainly try, I won't bore you with what I am trying to do with these numbers but now my task looks a lot easier, thanks once again
Howard

Prime numbers

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Prime numbers

Prime numbers

Prime numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions