RANDBETWEEN() function

[fastrunr]

Hi,

I'm using the random number generator in excel and want it to calculate only once. The goal is to generate a list of 8 numbers one time using 8 instances of that function, and then to disable the function from re-calculating.

I want to be able to preserve both the function in the cell and the initial list of numbers that is generated. (Currently nothing prevents the formula from re-calculating.)

Anyone know if this is possible??

Thanks!
Liz

 

[AnotherHiggins]

Liz,
->I want to be able to preserve both the function in the cell and the initial list of numbers that is generated.

I'm pretty sure you can't do both. (I won't say it's impossible because you just never know what some of these guys will come up with.)

I'd suggest copying the range then using Paste Special > Values to make the data static. The rand function is dynamic by nature*.

*dynamic by nature <- wasn't that an 80s rap group?

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[fastrunr]

Yeah, I think the only way to do it is to create a macro to run the function.

Actually, it was "naughty by nature", but dynamic by nature could be their cover band....

Thanks, John.

 

[xlhelp]

A macro will do it. The following one genrates 8 numbers between 1 and 100. There is a possibility of a repeat. BTW Ken Wright has done a lot of work on this (Have you won the lottery yet, Ken?)

Sub rdom()
Dim MyValue(10) As Long

Randomize (Now()) ' Initialize random-number generator.
Range("B1").Select

For i = 1 To 8
MyValue(i) = Int((100 * rnd()) + 1) ' Generate random value between 1 and 6.
Selection.Offset(1, 0).Select
Selection.Value = MyValue(i)
Next i


End Sub

http://www.geniusmedia.ca

 

[byundt]

Liz,
I think you are best off with a macro, or using the Edit...Paste Special...Values menu item to freeze the values. But here is a method using formulas that will work:
1) Put a value of TRUE in cell B1
2) Put this formula in cell A1, then copy it down for eight rows:
=IF($B$1,RAND(),A1)
3) Change the value of B1 to FALSE. Ignore the resulting error message about circular references. This will freeze the values, yet retain the formula that generated them.

If you want to generate the eight random values all at once, then select the eight cells (A1:A8 in the formula as shown), then enter an array formula at step 2 like:
=IF($B$1,RAND(),A1:A8)
Remember to hold the Control and Shift keys down while pressing Enter. Excel should respond by adding curly braces surrounding the formula. If it doesn't, then click in the formula bar and Control + Shift + Enter.

Note that you will get a message about being unable to calculate a formula when the workbook opens, but the values won't update after you click "OK" to dismiss the warning message.

Brad

 

[byundt]

Liz,
The same trick works with the RANDBETWEEN function using the formula (copied down):
=IF($B$1,RANDBETWEEN(1,100),A1)

or the array formula entered into preselected cells B1:B8
=IF($B$1,RANDBETWEEN(1,100),A1:A8)

Brad

 

[KenWright]

How about this - i often want to generate random dummy numbers to test various models I have built, so i use this attached to a keyboard shortcut:-

Sub RandomNumber()
    ubnd = InputBox("Enter Upper Bound")
    lbnd = InputBox("Enter Lower Bound")
    nudp = InputBox("Just hit OK for Integers or type D for decimals")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    On Error GoTo Oops:
    c = Selection.Cells.Count
    x = 1

    If UCase(nudp) = "D" Then
        With Selection
            .ClearContents
            .NumberFormat = "#,##0.00"
        End With
        For Each cell In Selection
            cell.Value = Rnd() * (ubnd - lbnd) + lbnd
            Application.StatusBar = Round(x / c, 2) * 100 & "% Done"
            x = x + 1
        Next cell
    Else
        With Selection
            .ClearContents
            .NumberFormat = "#,##0"
        End With
        For Each cell In Selection
            cell.Value = Int(Rnd() * (ubnd - lbnd + 1) + lbnd)
            Application.StatusBar = Round(x / c, 2) * 100 & "% Done"
            x = x + 1
        Next cell
    End If

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.StatusBar = False
Oops:     Exit Sub

End Sub

Regards
Ken............

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

 

[KenWright]

And unfortunately, no, despite having tried a number of times, the lottery has escaped me :-(

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