Determine Factors of Number & Counting

[muhammad susanto]

hi guys..

i'm looking for in excel formula to easy determine a number has factors (2 problems) :
a. how many list of factors a number?
b. counting list of factors?

number	list of factors (expected result a)	count factors (expected result b)
10	{1;2;5}	3
90	{1;2;3;5;6;9;10;15;18;30;45;90}	12
etc..

<tbody>
</tbody>

any help, greatly appreciated...

m.susanto

 

[sheetspread]

Excel 2010
	A	B
1	10	4
2	90	12
Sheet7

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(MOD(A1,ROW(INDIRECT("1:"&A1)))=0))

gets you the count, and the array can be returned with a little VBA, hang on....

 

[sheetspread]

Excel 2010
	A
1	10
2	90
3	11
4	13
5	14
6	22
7	56
Sheet8

Sub getfactors()
Dim i%, j%, lr%, a%, b%, x As Variant
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
a = Cells(i, 1).Value
ReDim x(a - 1)
For j = 0 To a - 1
b = a Mod (j + 1)
If b = 0 Then
x(j) = j + 1
Else
x(j) = "z"
End If
Next j
Cells(i, 3).Value = Replace(Join(x, ","), ",z", "")
Next i
End Sub

Excel 2010
	A	B	C
1	10		1,2,5,10
2	90		1,2,3,5,6,9,10,15,18,30,45,90
3	11		1,11
4	13		1,13
5	14		1,2,7,14
6	22		1,2,11,22
7	56		1,2,4,7,8,14,28,56
Sheet8

 

[Gary's Student]

Place a value in cell A1. In B1 enter the array formula:

=LARGE((ROUND($A$1/ROW(INDIRECT("$1:$"&$A$1)),0)=$A$1/ROW(INDIRECT("$1:$"&$A$1)))*ROW(INDIRECT("$1:$"&$A$1)),ROW(1:1))

and copy down.

Array formulas must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

 

[sheetspread]

Place a value in cell A1. In B1 enter the array formula:

=LARGE((ROUND($A$1/ROW(INDIRECT("$1:$"&$A$1)),0)=$A$1/ROW(INDIRECT("$1:$"&$A$1)))*ROW(INDIRECT("$1:$"&$A$1)),ROW(1:1))

and copy down.

Array formulas must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

Excel 2010
	P
1	90
Sheet7

Cell Formulas
Range	Formula
P1	{=LARGE(((MOD($A$1,ROW(INDIRECT("1:"&$A$1)))=0)*ROW(INDIRECT("1:"&$A$1))),ROW(A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

works too, and for ascending order:

Excel 2010
	R
1	1
Sheet7

Cell Formulas
Range	Formula
R1	{=SMALL(((MOD($A$1,ROW(INDIRECT("1:"&$A$1)))=0)*ROW(INDIRECT("1:"&$A$1))),($A$1-SUMPRODUCT(--(MOD($A$1,ROW(INDIRECT("1:"&$A$1)))=0)))+ROW(A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[muhammad susanto]

Excel 2010

	A
1	10
2	90
3	11
4	13
5	14
6	22
7	56

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Sub getfactors()
Dim i%, j%, lr%, a%, b%, x As Variant
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
a = Cells(i, 1).Value
ReDim x(a - 1)
For j = 0 To a - 1
b = a Mod (j + 1)
If b = 0 Then
x(j) = j + 1
Else
x(j) = "z"
End If
Next j
Cells(i, 3).Value = Replace(Join(x, ","), ",z", "")
Next i
End Sub

Excel 2010

	A	B	C
1	10		1,2,5,10
2	90		1,2,3,5,6,9,10,15,18,30,45,90
3	11		1,11
4	13		1,13
5	14		1,2,7,14
6	22		1,2,11,22
7	56		1,2,4,7,8,14,28,56

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

hi sheetspread, work great!! thank you so much...