‘Stable’ Random Numbers (XL2000)

Topic

New Reply

Hi,

When I use either the RAND() or the RANDBETWEEN() functions, Excel calculates a new random number on each worksheet update (say, by pressing F9; entering a value in another cell; or even adjusting a column width, to name some). My question is, is there a way to produce “stable” random numbers without resorting to VBA? By “stable” I mean, numbers that are calculated the very first time and don’t change on successive worksheet updates.

Thank you very much

Reply | Quote

Replies

You can copy the range with the RAND or RANDBETWEEN formulas, copy it to the clipboard, then Paste Special > Values (without changing the selection). This replaces the formulas with their results.

Reply | Quote

Hello Hans,

Yes, and I had read an article on how to automate the task with a macro, which was useful, but if possible I want to avoid VBA. However, just minutes from posting, I think I found an answer here. I gotta check on it a little more, but at least numbers stay still. I also found this third-party random number generator add-in, but haven’t had time to try it yet (besides the hassle of the installation makes it a less attractive alternative, given the solution at hand). Thank you for your (very!) prompt response .

Reply | Quote

In fact… the add-in did prove useful. It’s very easy to use. From the website:[indent]

---

• fill the Range with random integers from the selected range
• fill the Range with random real numbers from the selected range
• fill the Range with random dates from the selected range
• fill the Range with random strings formed by symbols from the selected range
• fill the Range with random Boolean[/list]
  

  ---

  [/indent]It’s and the supported versions are 2000, 2002, 2003.

Reply | Quote