random numbers

[travis]

I am trying to devise a way to generate random numbers
within defined parameters (i.e. between 1 and 100)that
will give me a list that doesn't change everytime a
calculation is done within the workbook. The "rand"
and "randbetween" functions give me exactly what I need
for my list of numbers but there doesn't seem to be a way
to stop it from recalculating the list everytime a
calculation is done on the page. I tried the Random
Number Generation analysis tool but cant seem to find a
way to create a macro that allows for seperate parameters
when it is run. This workbook is going to be used by
children so I need it to be more user friendly than
pulling up that tool everytime the workbook is used. Any
help you can provide would be most useful. I am using
Excel 2000.
(e-mail address removed)

 

[Bob Phillips]

Travis,

Here is one way

Here is one way that may or may not be good for you.

First, ensure cell F1 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 A1
=IF(($F$1="")+A1>0,A1,INT(1+100*RAND()))
it should show a 0

Copy A1 down to A12.

Finally, put some value in F1, say an 'x', and all the random numbers will
be generated.

They won't change.

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

I believe it is possible to simplify Bob's suggestion on how to change
the cell value. See my post in 'Stop automatically changing value in
=RAND or =RANDBETWEEN' in the .worksheet.functions newsgroup.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ken Wright]

You can use the following, and assign it to a button on the toolbar or a
shortcut key combo. then just have the users select the range that they want to
fill, hit the button/shortcut and answer the questions as they pop up:-

Sub RandomNumber()

ubnd = InputBox("Enter Upper Bound")
lbnd = InputBox("Enter Lower Bound")
nudp = InputBox("Just hit OK for Integers or type D for decimals")
If UCase(nudp) = "D" Then
With Selection
.ClearContents
.NumberFormat = "#,##0.00"
End With
For Each cell In Selection
cell.Value = Rnd() * (ubnd - lbnd) + lbnd
Next cell
Else
With Selection
.ClearContents
.NumberFormat = "#,##0"
End With
For Each cell In Selection
cell.Value = Int(Rnd() * (ubnd - lbnd + 1) + lbnd)
Next cell
End If
End Sub