Automating a RAND function with storing different results in different cells

[fantoosh420]

I have a Excel sheet which does a lot of calculations based on RAND function of Excel. The table represents a probability function where RAND is used to define a probable value between two given numbers. So everytime, I press calculate button in Excel I get a different value in the cell. Now I want the Excel to automatically do calculation 100 times (basically equivalent to pressing calculate button 100 times manually) and then populate a different sheet in same file with all the 100 results (one in each cell) so that I can get minimun, maximum, average and median of those 100 results. Getting minimum, maximum, average and median is not complicated but I have no idea how to automate the calculation and populate 100 different cells with 100 different values generated from same sheet. Any help would be appreciated. I am willing to pay a modest sum if someone does that for me. Please help.

 

[Special-K99]

Ok, so no specific cell references indicated.
No mention of the minimum or maximum random number range.
So I feel pretty at my liberty to say base something on this.

in A1
=INT(RAND()*6)+1

calculates a random number from 1 to 6, adjust as necessary.
copy the formula down as far as A100.

This calculates a random number between 1 and 6 100 times.

Base something on this or be more specific about what you want.

When you say "100 different values" is that what you mean? No repeats? If you do you havent specified that clearly and you will need a different method.

 

[fantoosh420]

Thank you for the effort you took.

To continue to explain further - No - that is not I want.

I have a sheet which basically predicts revenue generation for let's say 52 weeks. Now each cell from A1 to A52 holds a value of the respective week. All these cells have a RAND function included into it to accomodate a variation.
In the cell, A53, a formula SUM(A1:A52) applied to calculate the sum of all the values from A1 to A52 cell. Now since all the cells from A1 to A52 has RAND function included, it means everytime, I press calculate button, I would get a different value in all the cells from A1 to A52. Consequently, the cell A53 would also have a different value everytime I press calculate button in Excel.

Now I want Excel to automatically calculate the whole thing - say - 100 times and populate cell B1 to B100 each with the result that it would generate in cell A53 (which is basically sum of values present in A1 to A52 as explained earlier.) So basically Excel would automatically do calculate, gets a value in cell A53,and it stores that value in cell B1. Then Excel would do calculate second time and it would get a different value in cell A53 - that value would be stored in cell B2. Again Excel would do calculate third time and the new value in cell A53 would be stored in cell B3 and so on.

 

[Eric W]

You're basically asking to do a Monte Carlo simulation.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu click Insert > Module. Paste the following code:

Sub MonteCarlo()
Dim Source As Range, Destination As Range, i As Long

    Set Source = Range("A53")
    Set Destination = Sheets("Sheet2").Range("B1")
    
    For i = 1 To 100
        ActiveSheet.Calculate
        Destination.Value = Source.Value
        Set Destination = Destination.Offset(1)
    Next i
    
End Sub

Change the ranges in red to match your sheet, and the value in blue to the number of attempts you want. Go back to your Excel sheet, press Alt-F8, select MonteCarlo and click Run.

You can also do this without VBA, but it's a bit more involved. See here:

https://support.office.com/en-us/ar...mulation-64c0ba99-752a-4fa8-bbd3-4450d8db16f1

 

[fantoosh420]

Thank you Mr. Eric.

Yes this is exactly what I wanted. I put the code and it works perfectly.

One last query I have now. How do I do it for multiple cells in the excels? As in what if I want 4 such columns of 100 results which are taken from 4 different cells in the same sheet? And how would I rename the module so that each is readable when I apply 4 such modules?

 

[fantoosh420]

Hi Mr. Eric,

Never mind. I experimented with the code you provided and managed to do it for multiple values in the workbook.

Thank you your your support. As promised, I am willing to pay a modest sum for this help. Please PM me your PayPal details.

 

[Eric W]

I'm glad you got it working to your satisfaction.

I appreciate that you are willing to pay something for the help, but since this wasn't a formal consultant arrangement, I really couldn't accept. If you still would like to pay, then make a donation to your favorite charity. My favorite charities usually involve childhood development (education, nutrition, sports).

Have a great day!