Solved

Microsoft Excel: generating random numbers without recalculating each time.

Posted on 2016-09-06
31
73 Views
Last Modified: 2016-09-10
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
Comment
Question by:Anton Tajanlangit
  • 14
  • 13
  • 4
31 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41786617
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
 

Author Comment

by:Anton Tajanlangit
ID: 41786633
I'm using a Mac, will these instructions apply too? (It doesn't seem to with me.)
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41786643
PasteSpecial as Values should work for you not sure about the shortcut keystrokes. Though you may try to find out.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41786645
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
 

Author Comment

by:Anton Tajanlangit
ID: 41786707
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41786729
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
 

Author Closing Comment

by:Anton Tajanlangit
ID: 41786847
Accurate and quick response time!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41787158
Thanks for the feedback Anton! Glad to help.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41787758
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
 

Author Comment

by:Anton Tajanlangit
ID: 41787807
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41787814
If you are copying from another cell anyway, just paste as value rather than pasting formula and then converting to value.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41787825
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
 

Author Comment

by:Anton Tajanlangit
ID: 41787994
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
 

Author Comment

by:Anton Tajanlangit
ID: 41788476
Is there a function that will display only the value of a =RAND() operation performed in a different cell?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41788543
If RAND function in A1 just simple

=A1
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Anton Tajanlangit
ID: 41788577
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41788852
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
 

Author Comment

by:Anton Tajanlangit
ID: 41789003
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789145
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
 

Author Comment

by:Anton Tajanlangit
ID: 41789512
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789560
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
 

Author Comment

by:Anton Tajanlangit
ID: 41789801
Thank you! I'm working on the spreadsheet right now and seeing if it will work within the entire framework.

You're awesome.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789846
You're welcome Anton!
0
 

Author Comment

by:Anton Tajanlangit
ID: 41791597
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41791656
There are two instances of Application.Undo in the code which are responsible for this effect.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41792589
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41792597
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
 

Author Comment

by:Anton Tajanlangit
ID: 41792604
You're genius! These VB codes are like Greek to me, but for you they're just like street English! Awesome!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41792609
Thanks for the compliment Anton! :)
I am sure you yourself would be able to write similar codes very soon. :)
0
 

Author Comment

by:Anton Tajanlangit
ID: 41792660
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41792741
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now