randbetween

[kjetil syvertsen]

i have used randbetween funtion to generate a series of numbers. I now want
to work with these numbers trying out different ideas I have. But every time
i make a change to ANY cell in the whole workbook the randbetween fct runs
and changes the whole series . How can I get by this problem.

 

[Mike H]

Hi,

I think you have a couple of options:-

1. Set workbook calculation to manual but this will be of limited use if you
are doung calculations on those numbers.

2. When you generate your numbers copy them and paste them somewhere else
using PasteSpecial and paste values only.

3. Generate you random numbers with a macro that writes only the values to
the worksheet. If you need help in doing this post back with details of your
numbers and where you are putting them.

Mike

 

[Bob Phillips]

Here is a way to generate 20 random numbers between 100 and 500 and tie them
down


First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B1>0,COUNTIF($B$1:$B$20,B1)=1)),B1,RANDBETWEEN(100,500))
it should show a 0

Copy B1 down to B20.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B20, and re-input A1.

 

[ShaneDevenshire]

Hi,

RANDBETWEEN is a volatile function, like TODAY and NOW among others, that
means they recalculate whenever the spreadsheet recalculates. You have a
couple of solutions.

In addition, since you must have the Analysis ToolPak installed (2003 or
earlier) to be using this function, you could also use the command Tools,
Data Analysis, Random Number Generation. There are a number of different
distributions you can use and for your purposes the output are hard numbers
not formulas.

If this helps, please click the Yes button.