If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
RAND()
I have posted this question in the wrong place, so here I am again.
I want to generate a random number using RAND() in a cell. But the number generated by RAND() will change every time there is a recalculation. Once the number is generated, is there a way to make it NOT to change under any situation? Thanks in advance Charles. |
#2
|
|||
|
|||
RAND()
In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1
to overcome the circular reference problem. -- David Biddulph "Charles W" wrote in message ... I have posted this question in the wrong place, so here I am again. I want to generate a random number using RAND() in a cell. But the number generated by RAND() will change every time there is a recalculation. Once the number is generated, is there a way to make it NOT to change under any situation? Thanks in advance Charles. |
#3
|
|||
|
|||
RAND()
Thanks for the help. How do you "set the calculation iteration count to 1"?
Actually, I tried just using the formula you suggested without "setting the calculation iteration count to 1" (because I don't know how), and all I got was "0" Charles. "David Biddulph" wrote: In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1 to overcome the circular reference problem. -- David Biddulph "Charles W" wrote in message ... I have posted this question in the wrong place, so here I am again. I want to generate a random number using RAND() in a cell. But the number generated by RAND() will change every time there is a recalculation. Once the number is generated, is there a way to make it NOT to change under any situation? Thanks in advance Charles. |
#4
|
|||
|
|||
RAND()
I found out how to set the count now.
The formula works like a charm. One more problem, when I copy the formula in A1 to A2 by dragging, the same Random number is generated. How can I make it to generate a new number in another cell when I copy? I am lazy and don't want to type the formula in a new cell every time. Thanks again Charles. "David Biddulph" wrote: In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1 to overcome the circular reference problem. -- David Biddulph "Charles W" wrote in message ... I have posted this question in the wrong place, so here I am again. I want to generate a random number using RAND() in a cell. But the number generated by RAND() will change every time there is a recalculation. Once the number is generated, is there a way to make it NOT to change under any situation? Thanks in advance Charles. |
#5
|
|||
|
|||
RAND()
Hi,
Press F9. However, if you want to generate a lot of random numbers with RAND and you want them static. Enter the formula, copy it as needed and then choose Copy, and Edit, Paste Special, Values. This will convert all the formulas to values. If this approach works for you I would return the iteration to its default, it could cause problems elsewhere. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Charles W" wrote: I found out how to set the count now. The formula works like a charm. One more problem, when I copy the formula in A1 to A2 by dragging, the same Random number is generated. How can I make it to generate a new number in another cell when I copy? I am lazy and don't want to type the formula in a new cell every time. Thanks again Charles. "David Biddulph" wrote: In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1 to overcome the circular reference problem. -- David Biddulph "Charles W" wrote in message ... I have posted this question in the wrong place, so here I am again. I want to generate a random number using RAND() in a cell. But the number generated by RAND() will change every time there is a recalculation. Once the number is generated, is there a way to make it NOT to change under any situation? Thanks in advance Charles. |
#6
|
|||
|
|||
RAND()
On Feb 23, 11:40*am, Charles W
wrote: But the number generated by RAND() will change every time there is a recalculation. Yeah, real useful, huh? :-( Once the number is generated, is there a way to make it NOT to change under any situation? Copy-and-paste-special-value. You can overwrite the RAND() formulas, or paste-special the values somewhere else and simply ignore the cells with RAND(). An advantage of the latter is that you can "regenerate" random values again later simply by using copy-and-paste-special-value again. Alteratively, create the following macro. function myrand(optional rng as range) myrand = Rnd end function The argument provides the option of causing the MYRAND formulas to be recalculated automagically simply by modifying a cell in the range argument. For example, =MYRAND(A1) is recalculated whenever A1 is modified. (There are other ways to accomplish the same thing.) If you are not familiar with macros, press alt-F11, click on Insert - Module, and copy-and-paste the above function into the VBE window. Be sure to set macro security to Medium or higher in order to make life a little easier when you reopen the workbook. A couple downsides to the macro approach. First, it makes it more difficult to share your workbook with other people, since it depends on their macro security. Second, the VBA Rnd function might not be as robust as the Excel RAND function. I don't know that for a fact. But the VBA Rnd function returns a 32-bit floating-point value, whereas the Excel VBA RAND function presumably returns a 64-bit floating-point value. On the other hand, what really determines robustness of an RNG is the internal algorithm, which might not be reflected in the function data type. A 64-bit result does not necessarily have a longer period or better random characteristics. Perhaps some other people can comment, if they have knowledge of the internal algorithms of Rnd and RAND. |
#7
|
|||
|
|||
RAND()
PS....
On Feb 23, 3:39*pm, I wrote: A couple downsides to the macro approach. Another downside: the MYRAND expressions are normally recalculated when you save the workbook, unless you select Manual and unselect "Recalculate before save" under Tools Options Calculation. Kinda defeats the benefit of using a macro to generate the random value. Still, I use the macro approach sometimes in quick-and-dirty worksheets, which I don't save or don't care if the values are not preserved across save and re-open. |
#8
|
|||
|
|||
RAND()
Charles!
Try out my excel addin function randStatic at : www.pimpmyexcel.com Look under statistical functions Custom Excel Function: randStatic() Category Statistical Functions Arguments None Returns A mersenne twister generated random number in [0,1] The function is non volatile, ie it only will not change on recalculation Examples randStatic() returns a [0,1] random number only once |
#9
|
|||
|
|||
RAND()
Hello,
... Try out my excel addin function randStatic at : www.pimpmyexcel.com ... No open source? No license? No rights at all? Sorry, no use! Regards, Bernd |
#10
|
|||
|
|||
RAND()
pimpmyexcel
And you want to be taken seriously? -- Biff Microsoft Excel MVP wrote in message ... Charles! Try out my excel addin function randStatic at : www.pimpmyexcel.com Look under statistical functions Custom Excel Function: randStatic() Category Statistical Functions Arguments None Returns A mersenne twister generated random number in [0,1] The function is non volatile, ie it only will not change on recalculation Examples randStatic() returns a [0,1] random number only once |
Thread Tools | |
Display Modes | |
|
|