+ Reply to Thread
Results 1 to 8 of 8

Smallest prime factor of a number

  1. #1
    Registered User
    Join Date
    02-15-2009
    Location
    Ploiesti,Romania
    MS-Off Ver
    Excel 1997
    Posts
    22

    Smallest prime factor of a number

    Hi,
    Is there any formula witch i can use to find the smallest prime factor of a number?

    Example: In A1 i write 30 (30=2*3*5 (the prime factors))
    in B1 i write a formula, and the result will be 2 (smallest prime factor)

    What is the formula?
    Last edited by RazBoss; 04-14-2009 at 07:10 AM.
    It's a plane,
    No, it's a bird,
    NO , it,s SuperRazBoss

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Smallest prime factor of a number

    dont think there is a formula
    this code when inserted in a standard module and run can be used to get all the prime factors of a given number
    code from http://support.microsoft.com/kb/202782 it works ok in 2003 and prob 2007
    Please Login or Register  to view this content.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Smallest prime factor of a number

    I agree with Martin... there are a few functions out there

    Determining the smallest factor is relatively straight forward:

    =SMALL(IF(MOD($A$1,ROW(INDIRECT("A2:A"&$A$1)))=0,ROW(INDIRECT("A2:A"&$A$1))),1)
    committed with CTRL + SHIFT + ENTER
    (k can be changed obviously)

    However, refining the above to include only primes as is your requirement is where it gets pretty intensive and the UDF's come into play... if MS has a UDF on their site it's a good indicator that that is the best way to go
    (that said the `big hitters` may have something up their respective sleeves)

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Smallest prime factor of a number

    The smallest factor is always a prime number.

    I'd use this version to allow larger numbers

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

    confirmed with CTRL+SHIFT+ENTER

    where your number is in A1

    if formula returns 0 then A1 itself is prime...

  5. #5
    Registered User
    Join Date
    02-15-2009
    Location
    Ploiesti,Romania
    MS-Off Ver
    Excel 1997
    Posts
    22

    Re: Smallest prime factor of a number

    Thanks both of you, i will use the both theories

    EDIT: i am sorry i thank all 3 of you
    Last edited by RazBoss; 04-14-2009 at 07:06 AM.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Smallest prime factor of a number

    dll that formula falls down in excel<2007 as i presume its using row count
    for say
    1847 *1861 = 3437267

    both primes
    formula gives ref

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Smallest prime factor of a number

    Martin, thanks

    I made a mistake with the formula which doesn't affect the authenticity of any results but restricts the size of the number in A1.

    I used A1^1/2 which is, in effect, just the same as A1/2. There should be parentheses around 1/2 or you can use 0.5 instead, i.e.

    =MIN(IF(MOD(A1,ROW(INDIRECT("2:"&INT(A1^0.5)))),"",ROW(INDIRECT("2:"&INT(A1^0.5)))))

    That should then work for any integer value of A1 up to 268435455 (in any version of Excel). After that an error of the MOD function kicks in (I'm not sure if the MOD error applies in Excel 2007).

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Smallest prime factor of a number

    now that works a treat! nice one

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1