random numbers
 

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.

random numbers
 

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 ToolsOptions 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="")+A10,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)

"travis" wrote in message
...
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.

random numbers
 

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

In article ,
says...
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.

random numbers
 

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"travis" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004