Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Factors of a number (Excel)

    Is there a function or other method, where I can work out the Factors of a specific number?:

    i.e. FactorNumber(8)= 1,2,4,8
    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Factors of a number (Excel)

    How about this? Add it to a module and then call it in a cell:
    <pre>=factornumber(8)</pre>


    Steve

    <pre>Option Explicit
    Function FactorNumber(lNumber As Long)
    Dim x As Long
    Dim y As Long
    Dim sList As String
    y = 0
    For x = 1 To lNumber
    If Int(lNumber / x) = lNumber / x Then
    y = y + 1
    If x = 1 Then
    sList = x
    Else
    sList = sList & "," & x
    End If
    End If
    Next
    FactorNumber = sList
    End Function</pre>


  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Factors of a number (Excel)

    Brilliant, Brilliant.

    We have just been mucking about with a Puzzle at work and I was challenged to prove the mathmatical reasons behind it and I was trying to explain that it was about common factors in two numbers, this answer proves it perfectly

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Jerry
    Jerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Factors of a number (Excel)

    You can "improve it" to create an array function with an optional parameter.

    <pre>Option Explicit
    Function FactorNumber(lNumber As Long, Optional iType As Integer = 3)
    Dim x As Long
    Dim y As Long
    Dim sList As String
    Dim lArray() As Long
    y = 0
    For x = 1 To lNumber
    If Int(lNumber / x) = lNumber / x Then
    y = y + 1
    ReDim Preserve lArray(1 To y)
    If x = 1 Then
    sList = x
    Else
    sList = sList & ", " & x
    End If
    lArray(y) = x
    End If
    Next
    Select Case iType
    Case 0
    FactorNumber = y
    Case 1
    FactorNumber = lArray
    Case 2
    FactorNumber = Application.WorksheetFunction. _
    Transpose(lArray)
    Case Else
    FactorNumber = sList
    End Select
    End Function</pre>


    If you select the cells A1:d1 and enter the formula
    <pre>=factornumber(8,1)</pre>

    then confirm with ctrl-shift-enter
    you will fill in the values 1,2,4,8 in the cells A11(horizontal)

    Alternately:
    If you select the cells A1:A4 and enter the formula
    <pre>=factornumber(8,2)</pre>

    then confirm with ctrl-shift-enter
    you will fill in the values 1,2,4,8 in the cells A1:A4 (vertical)

    Or you can use the "0" parameter to give you the number of factors
    <pre>=factornumber(8,0)</pre>

    will = 4

    But using:
    <pre>=factornumber(8)</pre>

    with no parameter, or an integer that is not 0, 1 or 2, you will get the string.
    "1,2,4,8"

    Of course you can also get an individual one with the index function:
    This will get you the third factor (for example) and either array:
    <pre>=INDEX(factornumber(8,1),3)</pre>

    <pre>=INDEX(factornumber(8,2),3)</pre>



    Steve

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Factors of a number (Excel)

    Another variation might be to use:
    If lNumber Mod x = 0 Then
    instead of
    If Int(lNumber / x) = lNumber / x Then
    In theory this should run a bit quicker, since you're not evaluating lNumber / x twice on each pass.

    Cheers
    PS: Another approach might be to calculate prime factors only, which would limit the numbers to be tested to primes =<int(n^(1/2)), though that wasn't quite what was asked for.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Factors of a number (Excel)

    An excellent suggestion.
    Another minor improvement (extending your logic) is to not test "1", since we know it will be there:
    1)Add 1 to the list
    2) loop/test 2 to lnumber/2
    3) add lnumber

    Not nearly as dramatic an improvement, but still will be a litte faster.

    Then we can add macropod's suggestion to make the loop faster.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Factors of a number (Excel)

    one more improvement...

    test the original number passed to the function to see if it's odd or even (eg, mod(number,2)=0). Set up the bounds of the for loop based on the result:
    - if odd: lower bound=3, upper bound=number/2, step=2 (assumes adding 1 to list of factors "optimization")
    - if even: bounds as given (lower bound=2, upper bound=number/2, step=1)

    Odd numbers can't have even numbers as a factor.

    You could also do the odd/even test by number/2 and test the remainder. If 0, the number is even. This not only gives you the upper bound for the loop but gives you the last factor. So another minor optimization would be to adjust the upper bound to NOT do the last iteration since you've already derived the last factor by the odd/even test.

    Fred

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Factors of a number (Excel)

    <P ID="edit" class=small>(Edited by kieran on 06-Jul-04 08:01. Edited because I cannot type without autocorrect/spell checker.)</P>There are no factors of a number in the range n/2 + to n other than n itself.
    Therefore the function can be changed to that below to speed up the process slightly.

    <pre>Option Explicit
    Function FactorNumber(lNumber As Long, Optional iType As Integer = 3)


    Dim x As Long
    Dim y As Long
    Dim sList As String
    Dim lArray() As Long
    y = 0
    For x = 1 To lNumber / 2
    If Int(lNumber / x) = lNumber / x Then
    y = y + 1
    ReDim Preserve lArray(1 To y+1)
    If x = 1 Then
    sList = x
    Else
    sList = sList & ", " & x
    End If
    lArray(y) = x
    End If
    Next

    sList = sList & ", " & lNumber
    lArray(y + 1) = lNumber

    Select Case iType
    Case 0
    FactorNumber = y
    Case 1
    FactorNumber = lArray
    Case 2
    FactorNumber = Application.WorksheetFunction. _
    Transpose(lArray)
    Case Else
    FactorNumber = sList
    End Select
    End Function
    </pre>


  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Factors of a number (Excel)

    Yes there are:
    Factors for 8 are 1,2,4,8
    Sqrt of 8 = 2.83
    4 is > 2.83
    [8 is also, but I assume you meant between sqrt(n) and n]

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Factors of a number (Excel)

    Editted after the reply below - my mistake - I'd got myself confused as was probably appallingly evident. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    In defense it was early in the morning for me and I hadn't had my <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48> (I'd been thinking of prime factors)

    You said "There are no factors of a number in the range n/2 + to n other than n itself."
    In fact there are none beyond the squareroot of n.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Factors of a number (Excel)

    We can still bring the square root of the number into this if we take into account that factors of a number always occur in pairs, one of which is always less than or equal to the square root of the number. The only exception is when the number is a square (the square root pairs with itself).

    Function FactorNumber(lngNum As Long) As String
    Dim strListLo As String
    Dim strListHi As String
    Dim lngSqr As Long
    Dim i As Long
    Dim fSquare As Boolean

    lngSqr = Int(Sqr(lngNum))
    fSquare = (lngNum = lngSqr * lngSqr)
    If fSquare Then
    lngSqr = lngSqr - 1
    End If

    For i = 1 To lngSqr
    If lngNum Mod i = 0 Then
    strListLo = strListLo & ", " & i
    strListHi = (lngNum i) & ", " & strListHi
    End If
    Next i

    If fSquare Then
    strListLo = strListLo & ", " & (lngSqr + 1)
    End If
    strListLo = strListLo & ", " & strListHi
    FactorNumber = Mid(strListLo, 3, Len(strListLo) - 4)
    End Function

    The loop index only goes up to the square root of the number instead of number/2. For large numbers, this will be more efficient; for example, for n=10000, the loop index will go up to 100 instead of 5000.

  12. #12
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Factors of a number (Excel)

    Hans - good thought, but they also occur as triplets and higher orders as well.
    Certainly having prime factors minimises the search - but there is then a synthesis of all combinations of those prime factors e.g. 32 is 2x2x2x2x2
    That synthesis may be more complex than the original search within n/2

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Factors of a number (Excel)

    I didn't try to find an optimal algorithm for finding the prime factors of a number - if you really need one, do a Google search. I merely tried to provide a slightly more efficient program for Jezza's original question.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Factors of a number (Excel)

    The original question was for factors, not the prime factors.

    Steve

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Factors of a number (Excel)

    Hans,

    I suppose this could be taken further still.
    If n is even, then all numbers will need to be checked, but can be 'paired' as you have explained.
    If n is odd, then the loop need only process the odd numbers
    ie change
    For i = 1 To lngSqr to
    For i = 1 To lngSqr step 2

    This will speed it up further half of the time.

    so you code would become

    <pre>Function FactorNumber(lngNum As Long) As String
    Dim strListLo As String
    Dim strListHi As String
    Dim lngSqr As Long
    Dim i As Long
    Dim fSquare As Boolean
    Dim iStep As Integer

    If lngNum Mod 2 = 1 Then
    iStep = 2
    Else
    iStep = 1
    End If

    lngSqr = Int(Sqr(lngNum))
    fSquare = (lngNum = lngSqr * lngSqr)
    If fSquare Then
    lngSqr = lngSqr - 1
    End If

    For i = 1 To lngSqr Step iStep
    If lngNum Mod i = 0 Then
    strListLo = strListLo & ", " & i
    strListHi = (lngNum i) & ", " & strListHi
    End If
    Next i

    If fSquare Then
    strListLo = strListLo & ", " & (lngSqr + 1)
    End If
    strListLo = strListLo & ", " & strListHi
    FactorNumber = Mid(strListLo, 3, Len(strListLo) - 4)
    End Function
    </pre>


Page 1 of 2 12 LastLast

Posting Permissions

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