Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |