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