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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top