divisor in cell

[steve0075]

Suppose the number 50 is in A1. I need a formula in B1 that will generate a random number that is a divisor of 50 (i.e. 1,2,5,10,25, or 50). I need this to work with any number (not just 50). Any help would be greatly appreciated.

 

[hedrijw]

Instead of a formula, you can do this in VBA on the applicable worksheets code, i.e:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'-----------------------------------------------
If Target.Address<> "$A$1" Then Exit Sub ' Only handle changes to A1
temp = Target ' Get value entered in A1
Randomize ' Get random seed
Do
divisor = Int((temp * Rnd) + 1) ' Generate random divisor
Loop Until temp Mod divisor = 0 ' Loop until temp is evenly divisible
[b1] = divisor ' Show valid divisor
'-----------------------------------------------
End Sub

Right click on the worksheet's tab and click on View Code

Find the Private Sub Worksheet_Change(ByVal Target As Excel.Range) statement and insert everything between the dashed lines after it.

Every time a number is entered in A1, a valid divisor will appear in B1
This message was edited by hedrijw on 2002-08-23 09:26

 

[Juan Pablo González]

Use the formula

=GETDIVISORS(A1)

where GETDIVISORS is this UDF, and A1 houses 50. This formula returns an Array, so, if you want to see all the results, you must array enter it over a range (For example, in B1:B6 to get the results of 50)

<pre>Function GETDIVISORS(Num As Long) As Variant
Dim Ar As Variant
Dim Ar2
Dim i As Long
Dim Ctr As Long
Ar = Application.Evaluate("IF(MOD(" & Num & ",ROW(1:" & Num & "))=0,ROW(1:" & Num & "),"""")")
ReDim Ar2(1 To Application.Count(Ar), 1 To 1)
For i = 1 To Num
If IsNumeric(Ar(i, 1)) Then
Ctr = Ctr + 1
Ar2(Ctr, 1) = Ar(i, 1)
End If
Next i
GETDIVISORS = Ar2
End Function</pre>

 

[Juan Pablo González]

I was able to use that formula with the number 5461, from then on, the ReDim statement crashed and made the formula return #VALUE

 

[Aladin Akyurek]

On 2002-08-23 07:33, steve0075 wrote:
Suppose the number 50 is in A1. I need a formula in B1 that will generate a random number that is a divisor of 50 (i.e. 1,2,5,10,25, or 50). I need this to work with any number (not just 50). Any help would be greatly appreciated.

( 1.) Download the morefunc add-in from:

http://longre.free.fr/english/index.html

( 2.) Install/Activate morefunc via Tools|Add-Ins.

( 3.) Activate the target workbook.

( 4.) Activate Insert|Name|Define.

( 5.) Enter DivSet as name in the Names in Workbook box.

( 6.) Enter the following in the Refers to box:

=UNIQUEVALUES(IF(MOD(!A1,ROW(INDIRECT("1:"&ABS(!A1))))=0,ROW(INDIRECT("1:"&ABS(!A1))),""),1)

Note. This defines a so-called named formula.

( 7.) In A1 enter: 50 [ the sample number you provided ]

( 8.) In B1 enter:

=IF(A1,INDEX(DivSet,RANDBETWEEN(2,COUNT(DivSet))),"NoValue")

RANDBETWEEN requires that the Analysis Toolpak add-in is activated (via Tools|Add-Ins).

See the figure...

DivisorInCell steve0075.xls
	A	B	C	D
1	50	1
2	-45	3
3	0	NoValue
4
Sheet1

Caveat. I did not test how big the input value can be.

Aladin
This message was edited by Aladin Akyurek on 2002-08-24 05:06