# divisor in cell

#### steve0075

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### hedrijw

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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
ABCD
1501
2-453
30NoValue
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

Replies
20
Views
681
Replies
4
Views
275
Replies
7
Views
568
Replies
1
Views
188
Replies
4
Views
462

Threads
1,195,903
Messages
6,012,206
Members
441,681
Latest member
AkosiJessica

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back