# Thread: Factors of a number (Excel)

1. ## 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

2. ## 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. ## 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

4. ## 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. ## 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.

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Factors of a number (Excel)

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

Steve

15. ## 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 Last

#### Posting Permissions

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