Making a random number 'stick'

Rob Fenix

New Member
Joined
Jan 21, 2004
Messages
2
I am using =rand()*99 to produce a random percentage, however, I do not want the generated number to change with every keystroke. I'd like it to be stored, in either that cell or, if that's not possible, another cell.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I am using =rand()*99 to produce a random percentage, however, I do not want the generated number to change with every keystroke. I'd like it to be stored, in either that cell or, if that's not possible, another cell.
Welcome the the Board!

You're not being very clear as to what you're trying to do. That formula should only change if you activate/exit that cell. And shouldn't it be =RAND()*0.99?

Do you have a Worksheet_Change event as well?

Try to post some of what you're trying to accomplish, using Colo's HTML Maker - you'll find the link at the bottom of the page.

Smitty
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
you can just copy the cells with the formula, then click edit, pastespecial, values . If you want another distination for your values, then before clicking edit, click the destination cells first.


However, if you want a VBA code like the cell is in a1:a8 then
Range("a1:a8").Value = Range("a1:a8").Value.

You if want another destination like b1:b8 then just use:
Range("b1:b8").Value = Range("a1:a8").Value
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

Smitty -- Rand() is a volatile function, so it will recalculate upon any change to the workbook, not including formatting changes like adjusting column width, etc..

Workarounds...

1. Turn calculation to Manual (not recommended in most cases)
2. After entering the formula, choose Edit>PasteSpecial>Values. You will lose the formula but retain the value.
3. Write a UDF such as the following

Code:
Function StaticRand(Optional Multiplier as Double) As Double
    StaticRand = Rnd * IIf(IsMissing(Multiplier), 1, Multiplier)
    End Function

Note that VBA's Rnd has a different period than does Excel's RAND() function, but the difference would only be noticed when doing seriously intensive stats work.

4. Search this site for Eric Dysart and visit his webpage. He has a downloadable file that has exactly what you want, but requires the add-in to be loaded.

5. I am sure I am missing some option. :confused:
 

Rob Fenix

New Member
Joined
Jan 21, 2004
Messages
2

ADVERTISEMENT

I'm thinking the 'Paste Special' may give me what I'm wanting, since I don't really need the function after it has resolved once. Thanks for the help guys!
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Another approach in VBA, select the cells and run this code:Sub rndinput()
For Each sel In Selection
sel.Value = Rnd * 99
Next sel
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,883
Messages
5,543,019
Members
410,583
Latest member
gazz57
Top