Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

Microsoft Excel: generating random numbers without recalculating each time.

Hello,

How can the results of the RAND and RANDBETWEEN functions become non-volatile (keeping the generated numbers fixed and not automatically recalculating) each time a new RAND or RANDBETWEEN command is entered into another cell?

Thank you,

Anton.
0
Anton Tajanlangit
Asked:
Anton Tajanlangit
  • 14
  • 13
  • 4
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Copy the cells with Rand or RandBetween Formula and overwrite them by pasting values i.e. pastespecial --> Values.
The shortcut is select those cells, press Ctrl+C to copy and then press Alt + E + S + V and then press Enter.
0
 
Anton TajanlangitAuthor Commented:
I'm using a Mac, will these instructions apply too? (It doesn't seem to with me.)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
PasteSpecial as Values should work for you not sure about the shortcut keystrokes. Though you may try to find out.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
See if this works for you...

To copy --> ⌘ + C
To paste values --> ⌘ + Ctrl + V

For more shortcuts, you may download the pdf file from the following downloadable link..
https://samples-breakingintowallstreet-com.s3.amazonaws.com/BIWS-Excel-Shortcuts-Mac.pdf
0
 
Anton TajanlangitAuthor Commented:
I am preparing a spreadsheet that has to be used in a live setting. One column needs to generate a random number for each cell, going downwards, one by one.

However, whenever I enter the =RAND() function as I go down the column, the cell above it changes values because of automatic recalculation. Which I don't want to happen. I also don't want copying and pasting as this is cumbersome.

What I want to happen is that I type the =RAND() function in a cell, and then all the other cells take results from their formulas from this random number. When I type another =RAND() command in the cell below that, I don't want the cell above it to change its value just because of automatic recalculation. And I don't want manual recalculation as well!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In this case I would suggest you to take help of a VBA code i.e. Sheet Change Event Code.
The following code will convert the Rand() formula entered in column A starting from Row2 to Values automatically.
Not sure if this would work in Mac but you may try it to find out.
To apply this code to your workbook, please follow these steps...

1) Open your workbook
2) Right Click on Sheet Tab --> View Code and paste the code given below into the opened code window.
3) Close VB Editor
4) Save your workbook as Macro-Enabled Workbook.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipError
'The following line assumes that you will place your Rand() formulas in column A
'Starting from Row2, assuming Row1 being the column header
'Target.Column = 1 means that the column index of column A is 1
'If it is not column A in your actual workbook, change the 1 with the column index of actual column
If Target.Column = 1 And Target.Row > 1 Then
   Application.EnableEvents = False
   Target.Value = Target.Value
   Application.EnableEvents = True
End If
SkipError:
Application.EnableEvents = True
End Sub

Open in new window

0
 
Anton TajanlangitAuthor Commented:
Accurate and quick response time!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the feedback Anton! Glad to help.
0
 
Rob HensonIT & Database AssistantCommented:
Alternative, for a non VBA solution.

Type the =RAND() formula but before hitting enter, press F9. This will convert the formula result to a value.
0
 
Anton TajanlangitAuthor Commented:
Nice solution, Rob! But in order to minimize keystrokes, I was just going to copy the formula from another cell and paste it into cells, one by one going down, in the column as mentioned. As soon as I paste it, it's entered as a formula straightaway...
0
 
Rob HensonIT & Database AssistantCommented:
If you are copying from another cell anyway, just paste as value rather than pasting formula and then converting to value.
0
 
Rob HensonIT & Database AssistantCommented:
Just trying this and have found something a little bizarre.

Normally if you copy a cell and then Paste Values to another cell you get the value of the copied cell, paste values again in another cell and you get the same again.

However, if you place =RAND() in a cell and then copy it, pasting values to a cell will paste the current RAND value, move to another cell and Paste Value again and you get a different value, the value of the RAND function has recalculated and has somehow linked through to the clipboard!!!
1
 
Anton TajanlangitAuthor Commented:
Very helpful of you Rob, looks like this is another hidden feature of Excel. I just found some keyboard shortcuts on the Mac as well, to Paste Values.

Thanks so much!
0
 
Anton TajanlangitAuthor Commented:
Is there a function that will display only the value of a =RAND() operation performed in a different cell?
0
 
Rob HensonIT & Database AssistantCommented:
If RAND function in A1 just simple

=A1
0
 
Anton TajanlangitAuthor Commented:
Subodh Tiwari (Neeraj), if I wanted this behaviour to occur in more than just one column, how do I edit the code you sent in the VB editor?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Suppose you want the code to be triggered whenever you change a cell in column A, B, C, E, G, then you may try something like this....
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipError
'First checking that the Row of the Cell being changed is greater than 1
If Target.Row > 1 Then
   Select Case Target.Column
      Case 1, 2, 3, 5, 7  '1=Column A, 2=Column B, 3=Column C, 5=Column E and 7=Column G
      Application.EnableEvents = False
      Target.Value = Target.Value
      Application.EnableEvents = True
   End Select
End If
SkipError:
Application.EnableEvents = True
End Sub

Open in new window


Change the line#6 as per your requirement by changing the the column Index numbers.
0
 
Anton TajanlangitAuthor Commented:
Cell D5 has this formula: =IF(F4=1,RANDBETWEEN(1,100),0)

So if I enter "1" in cell F4, a random number is generated in cell D5.

I had incorporated D5 in the code you mentioned (by including Case 4 for the D column), but D5 does not display just the value of the RANDBETWEEN() function—it displays the result of the calculation and when I enter anything new in other cells with a formula, the worksheet recalculates and D5 changes its value.

Does your code work with the formula I first mentioned above? Or does it work only with the RANDBETWEEN() function by itself?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Sheet change event will not be triggered if the cell value is changed by a formula, it will be only triggered when you manually change the cell content.
You may use another event known as Sheet Calculate Event to convert a formula cell to value only if the cell content is changed by the formula itself.

In your case considering you already have formula in D5 before implementing the Change event code, once you input a 1 in F4, the code will be triggered if you have included the range properly in the code but the code will have no impact on the formula cell in D5.
Does inputting a 1 in F4 have impact on the formula in D5 only or on other formula cells also?

Making change event codes to work as per your requirement depend whether you are implementing the code on a blank sheet or on a sheet with prior data and formulas on it.

It would be helpful if you let me know the layout of your sheet i.e. how you have organized the data on the sheet, what are the formula cells and what are the cells which change the output returned by the formula cells after you manually input values. You will need to explain all the steps you are trying to automate

Alternatively instead of having formulas already in place, you may take help of change event code to place the value as per your existing formula in the destination cell.

e.g. insert a blank sheet in the workbook, right click on the new sheet tab --> View Code and place the code given below into the opened code window.
So the code will place a random number between 1 and 100 in D5 each time you input 1 in cell F4.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "F4" Then
   Application.EnableEvents = False
   If Target <> "" And Target = 1 Then
      Range("D5").Value = WorksheetFunction.RandBetween(1, 100)
   Else
      Range("D5").Value = 0
   End If
   Application.EnableEvents = True
End If
End Sub

Open in new window

0
 
Anton TajanlangitAuthor Commented:
Attached is a section of my worksheet which we are concerned about.

Each light blue cell has a formula which is impacted by a corresponding yellow cell. There should be no automatic recalculation of the sheet when new values are entered in other yellow cells.

Is it possible to enter a value in a yellow cell only once, after which the cell locks up?

Perhaps using VB code is again a solution here...
Layout.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with the code added on the Sheet Module. The code does what you are trying to achieve. The formulas on the sheet are not required so I have deleted them.
Layout.xlsm
0
 
Anton TajanlangitAuthor Commented:
Thank you! I'm working on the spreadsheet right now and seeing if it will work within the entire framework.

You're awesome.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Anton!
0
 
Anton TajanlangitAuthor Commented:
May I ask you what part of the code you sent would temporarily disable the locking up of cells when a value is entered once? I'm testing the spreadsheet and I have to close it (without saving) and load it up back again when I want to enter another number.

When everything is perfected, I can put the particular line back to the code to enable the locking up of cells again.

Thanks Neeraj!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There are two instances of Application.Undo in the code which are responsible for this effect.
0
 
Anton TajanlangitAuthor Commented:
Thank you so much!

I wish to remove an unused column from my spreadsheet (column D). Is there anything I will have to change in the VB code?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There are two Case statements within the Select Case statement.

Case 1  'Column A
and

Case 7, 12, 17, 22    'Column G, L, Q and V respectively

So if you delete/insert a column on the sheet, you will have to make sure that the column numbers are correctly mentioned in the Case statements.

e.g. if you delete a column say col. D, all the columns right to the deleted column D will be shifted towards left and their index numbers will be changed.
So the second Case statement will be changed to this....

Case 6, 11, 16, 21
0
 
Anton TajanlangitAuthor Commented:
You're genius! These VB codes are like Greek to me, but for you they're just like street English! Awesome!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the compliment Anton! :)
I am sure you yourself would be able to write similar codes very soon. :)
0
 
Anton TajanlangitAuthor Commented:
I never knew about VB codes until you showed me this possibility. Whenever I work on Excel, I only use standard formulas within the cells. Although I've not mastered them fully yet (I'm only basically self-taught), I hope to dive into VB coding also sometime in the near future.
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
VBA makes things lot easier when you are in a situation where inbuilt functions are not able to perform some specific tasks like one you had.
All the best Anton for the future learning process. We all are here to help whenever you need our assistance. :)
1

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 13
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now