Results 1 to 15 of 19
Thread: Factors of a number (Excel)

20040702, 12:59 #1
 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,8Jerry

20040702, 13:54 #2
 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>

20040702, 14:01 #3
 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>
JerryJerry

20040702, 14:15 #4
 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 ctrlshiftenter
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 ctrlshiftenter
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

20040705, 03:24 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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]

20040705, 10:09 #6
 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

20040705, 14:21 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 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

20040706, 00:01 #8
 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 06Jul04 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>

20040706, 23:51 #9
 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

20040707, 09:53 #10
 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.

20040707, 15:01 #11
 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.

20040707, 18:28 #12
 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

20040707, 18:37 #13
 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.

20040707, 19:14 #14
 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

20040707, 23:49 #15
 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>