On 20020823 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 addin from:
http://longre.free.fr/english/index.html
( 2.) Install/Activate morefunc via ToolsAddIns.
( 3.) Activate the target workbook.
( 4.) Activate InsertNameDefine.
( 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 socalled
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 addin is activated (via ToolsAddIns).
See the figure...
DivisorInCell steve0075.xls 



 A  B  C  D 

1  50  1   

2  45  3   

3  0  NoValue   

4     



Caveat. I did not test how big the input value can be.
Aladin
This message was edited by Aladin Akyurek on 20020824 05:06