excel problem, prime numbers

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: excel problem, prime numbers

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    hey,

    does anyone know how to make an excel spreadsheet that inputs any number and in another cell outputs if the number is prime or not. thanks.

    -matt w.

  2. #2
    New Member
    Join Date
    Dec 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    U can use
    countif
    =IF(COUNTIF(Myrange,A1)=1,"Prime",COUNTIF(Myrange,A1))
    Myrange=Sheet2!$A$2:INDIRECT("A"&COUNTA(Sheet2!$A:$A))
    Input you NO.columnA
    Input the above formula in cell A1
    then paste down



    [ This Message was edited by: Ken.Zeng on 2002-12-09 04:36 ]

  3. #3
    New Member
    Join Date
    Dec 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    when i input that formula into A1, it comes up as #NAME? do you know what is wrong?

    thanks

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt,

    Paste this into a general module in the workbook you are dealing with.
    Function Prime(TestRng As Range)
    'code by Tom Ogilvy (modified from original by Myrna Larson)
    'slight modification by RPS to accept range
    Dim PrimeCnt As Long
    Dim y As Long
    Dim x As Long
    Dim i As Integer
    Dim Flag As Boolean
    Dim Primes() As Long
    Dim NumStop As Double
    Dim TestNum As Long
    ReDim Primes(1 To 2)

    TestNum = TestRng.Value

    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

    Then to see if the value in A1 is prime, type "=prime(A1)" in your chosen cell.

    HTH
    Richie

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here are two more custom functions:

    Code:
    Option Explicit
    
    '' ***************************************************************************
    '' Purpose  : Test whether the argument is a PRIME number
    '' Written  : January 98 by Andy Wiggins - Byg Software Ltd
    '' Notes    : Returns TRUE or a zero length string
    ''
    Function IsPrime(vlTestNumber As Long)
    ''Application.Volatile                                ''Not essential for this demo
    Dim vlCount As Long                                 ''Declare some variables
    Dim vlHalf As Long
    
        vlHalf = vlTestNumber / 2 + 1                   ''By using a LONG we get an integer result, which is what we want
        For vlCount = 2 To vlHalf                       ''Loop until we get to vlHalf
            If (vlTestNumber Mod vlCount) = 0 Then      ''If we get a ZERO result in this test, the number is not a prime ..
                IsPrime = ""                            ''.. so return an empty string ..
                Exit Function                           ''.. and leave the function
            End If
        Next
        IsPrime = True                                  ''If we get here, the number is a prime, so return TRUE
    End Function
    
    '' ***************************************************************************
    '' Purpose  : Test whether the argument is a PRIME number
    '' Written  : January 98 by Andy Wiggins - Byg Software Ltd
    '' Notes    : Returns TRUE or FALSE
    ''
    Function IsPrimeBool(vlTestNumber As Long) As Boolean
    ''Application.Volatile                                ''Not essential for this demo
    Dim vlCount As Long
    Dim vlHalf As Long
    
        vlHalf = vlTestNumber / 2 + 1                   ''By using a LONG we get an integer result, which is what we want
        For vlCount = 2 To vlHalf                       ''Loop until we get to vlHalf
            If (vlTestNumber Mod vlCount) = 0 Then      ''If we get a ZERO result in this test, the number is not a prime ..
                IsPrimeBool = False                     ''.. so return FALSE
                Exit Function                           ''.. and leave the function
            End If
        Next
        IsPrimeBool = True                              ''If we get here, the number is a prime, so return TRUE
    End Function
    And here is an array formula:

    =OR(A1=1,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

    After typing it press Ctrl+Shift+Enter not just Enter.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,478
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

  7. #7
    New Member
    Join Date
    Mar 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel problem, prime numbers

    Hi
    I am stish kumar singh working in ms excell i have efficient way to find out prime number in excell sheet.

  8. #8
    New Member
    Join Date
    Mar 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel problem, prime numbers

    I can find out prime number in excell up numerical range of excell sheet.if some one intrested i can send them the way.

  9. #9
    New Member
    Join Date
    Mar 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel problem, prime numbers

    Quote Originally Posted by mwilliams232 View Post
    hey,

    does anyone know how to make an excel spreadsheet that inputs any number and in another cell outputs if the number is prime or not. thanks.

    -matt w.
    yes i can do this.i hv formula to do this.

  10. #10
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel problem, prime numbers

      
    Quote Originally Posted by Andrew Poulsom View Post
    And here is an array formula:

    =OR(A1=1,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

    After typing it press Ctrl+Shift+Enter not just Enter.
    Andrew: Isn't 2 a prime number

    Martin
    Excel 2010, Win7,

    We are not what we think we are, but what we think, we are.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com