PDA

View Full Version : [SOLVED] Random Numbers Looping Thru and Displaying Values



Sully1440
03-09-2018, 08:15 PM
Hi All,
I'm really hoping someone can help me. I'm trying to randomize numbers between two numbers (Column E and G). I need it to loop through several times and display the numbers in column Q. Once it finishes the row, it copies and pastes the values and then moves on to the next row to the end of the table. My problem is that the random numbers don't seem to fall between the 2 values (Column E and G).

Please help.
I attached the file I'm using....(please note that I'm piecing various code together)
I believe my problem is in the sub called Sub RandomNumberGen_PERT_Budget

Any help would be greatly appreciated. I'm really stuck.
Thanks,
Jim

werafa
03-10-2018, 01:19 AM
are you using rand or randbetween?

alternatively, try myNum = Int((Up - Lw + 1) * Rnd + Lw), where up = upper limit, and lw = lower limit

Sully1440
03-10-2018, 04:34 AM
I think my random equation is right. I'm using: Cell.Value = ((Range("G7") - Range("E7") + 1) * Rnd() + Range("E7"))

I think it's a problem with how I'm calling it from the Userform code. I want to use different upper and lower values for each row as it loops through the table.
For example:
Cell.Value = ((Range("G7") - Range("E7") + 1) * Rnd() + Range("E7"))
Cell.Value = ((Range("G8") - Range("E8") + 1) * Rnd() + Range("E8"))
Cell.Value = ((Range("G9") - Range("E9") + 1) * Rnd() + Range("E9"))
Cell.Value = ((Range("G10") - Range("E10") + 1) * Rnd() + Range("E10"))

....until the end of the table.

No idea how to loop it through. When I select only one row to do (row 7), it works fine. When I want it to loop through all rows, it doesn't work.

Jim

Paul_Hossler
03-10-2018, 06:40 AM
1. You can simplify your sub to this



Sub Clear_Data_Prob_Budget()
Application.Calculation = xlManual
Range("I7:N100").ClearContents
Range("A1").Select
Application.Calculation = xlAutomatic
End Sub




2. Really not sure of your process:

Why 30 cells in Q?
Why loop 5 times for random number in same cell?
Doesn't seem to be a task loop
This is so fast, why do you even need a progress bar?

You can look at this. When it completes, col Q contains the 30 random numbers between the min/max of the 10th task




Sub RandomNumberGen_PERT_Budget()
Dim q As Long, t As Long
For t = 7 To 16 ' loop tasks
For q = 7 To 36 ' loop q 30 times
Cells(q, 17).NumberFormat = "0.00"
Randomize
Cells(q, 17).Value = Int(Rnd * (Cells(t, 7).Value - Cells(t, 5).Value + 1) + Cells(t, 5).Value)
Next q
Next t
End Sub

Sully1440
03-10-2018, 07:28 AM
Hi Paul,
Thanks for your help looking into this.....

What I'm attempting to do is show a normal distribution graph update in real time and get values when it finishes looping. I'm looping it for each row (for effect really). But I am interested in the final results when its complete for each row. So, I added a progress bar (which was a task for me...lol). I stripped out too much from my spreadsheet (graph) in the original post. I'm attaching a new spreadsheet.

I want it to loop thru for each row (looking at optimistic and pessimistic values)...auto update the graph.....when it finishes, paste values related to some stats, then move on to the next row....and do the same thing. This is based on a selection by the user in the userform. The user can also select only one task to run as well (and not the whole table).

Not sure how else to do it.

See attached sheet....

SamT
03-10-2018, 07:56 AM
No idea how to loop it through.
Dim Rw as Long

For Rw = X to Y 'Where X & Y Come from the UserForm
Cells(Rw, "Q") = (Cells(Rw, "G") - Cells(Rw, "E")+ 1) * (Rnd() + Cells(Rw, "E"))
Next Rw

Paul_Hossler
03-10-2018, 09:32 AM
I don't think it's reasonable (could be 200% wrong) to try and plot all tasks together as normal data

Using a monti carlo on each task, you could do the Mean, etc for each task individually and get something like this

Not sure it that's what you're looking for

21791


The Per Task statistics are calculated N times using Rnd() and returned using an array entered formula

(Do you have A06 inputs in the correct order)?




Option Explicit

Function RandomResults(N As Long, O As Double, P As Double) As Variant
' 1 2 3 4 5 6
'Mean Std Dev Median 95% Confidence 3 std dev below 3 std dev above
Dim A(1 To 6) As Double
Dim R() As Double
Dim i As Long

ReDim R(1 To N)

Application.Volatile

Randomize
For i = LBound(R) To UBound(R)
R(i) = Int(Rnd * (O - P + 1) + P)
Next i
With Application.WorksheetFunction
A(1) = .Average(R)
A(2) = .StDev(R)
A(3) = .Median(R)
A(4) = .Confidence(0.05, A(2), N)
A(5) = A(1) - 3 * A(2)
A(6) = A(1) + 3 * A(2)
End With
RandomResults = A

End Function

Sully1440
03-10-2018, 11:11 AM
Hi Paul,
Yes this works :)
Can you add or simulate a monti carlo graph?

See attached.....
Jim

Sully1440
03-10-2018, 01:48 PM
Btw, thank you Paul & SamT. This code is working for me and I'm learning more about worksheet functions. Very much appreciated.

I'm trying to produce a graph as well. Would it be possible to loop a worksheet function for a normal distribution?

For example:

For i = 1 to #runs
Application.WorksheetFunction.NormInv(Rnd(), 0.55, 0.2)
Next i

.....place the data either somewhere on the sheet or to memory and then graph it?

Paul_Hossler
03-10-2018, 02:22 PM
It would seem to me that a graph should be limited to the spread of a single task, so if you have 10 tasks, you'd have 10 graphs

Would you try to use the internal array values?

Sully1440
03-10-2018, 02:44 PM
My preference would be to run the normal graph for one of the 10 tasks.....by user selection...so the user selects a task, hits a macro button to run the graph.

Sully1440
03-11-2018, 10:38 AM
...Still having trouble....
I'm trying to run what I have for task A01(as an example), and write in the following code:
Cells(i, 21) = Range("J7")
Cells(i, 22) = Range("O7")


I manually did it for Columns Q, R, S which shows my nice normal graph...

My problem is that I can't repeat the graph when using the numbers in the table.

See attached...

:(

Paul_Hossler
03-11-2018, 12:11 PM
Try something like this to see if it helps

Change Q3 to the task ID you want to graph

Sully1440
03-11-2018, 12:48 PM
Yes, this is perfect. It's doing everything I want it to do. Thank you Paul....again.

Last question. What are the { and } for? I added one column called Normal Distr, but it didn't add those {'s It seems to work, just curious.

Paul_Hossler
03-11-2018, 12:56 PM
The RandomResults() function returns a multi-cell array so the formula is 'Array-Entered' = Ctrl-Shift-Enter (instead just Enter) with the same formula in all cells

The braces are added by Excel when you CSE, you don't type them

21797

Sully1440
03-11-2018, 01:06 PM
Thank you again Paul. I'm very happy this is fixed. Couldn't have done this without you.

Thx,
Jim
:)

I'll mark it as solved.

Paul_Hossler
03-12-2018, 06:35 AM
There were a few issues with v7


a. The function was in 7 cells instead of only the 6 (I missed a compatibility when I was playing around)


b. It might be better to use a normally distributed random number instead of a linearly distributed one


R(i) = Application.WorksheetFunction.NormInv(Rnd, Abs(O + P) / 2#, Abs(O - P) / 6#)

assumes that the Optimistic to Pessimistic spread = 6 sigmas, and the mean is the average.

Might be interesting to use the Expected as the mean


c. You can simplify the graph a little bit

I used 6 sigmas/100 as X and Normal(x) as Y


Been a fun little project

Sully1440
03-12-2018, 12:25 PM
Thanks Paul. I made the changes :)