divisor in cell

steve0075

New Member
Joined
Aug 22, 2002
Messages
4
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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

Which adblocker are you using?

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
Back
Top