Macro to do repeated calculation..

[traihan]

Hi, have very little knowledge writing macros.

What I needed to do was:

Create 5 random numbers between (let's say) 39 and 46. Calculate %RSD of those 5 random numbers. Repeat this say 1000 times. Then find the highest %RSD from the 1000 RSDs. Hope someone can help. TIA>

 

[bassplr19]

Here's a start:

Function rsdCalc(VarA, VarB, runs)
Dim x() As Integer
Dim stdDevX As Long
Dim averageX As Long

ReDim x(1 To runs)

For i = 1 To runs

x(i) = WorksheetFunction.RandBetween(VarA, VarB)

Next

stdDevX = WorksheetFunction.StDev(x)
averageX = WorksheetFunction.Average(x)
rsdCalc = stdDevX / averageX * 100

End Function

 

[Georgiboy]

Maybe this will help:

Function MultiRSD(LowerRnd As Long, UpperRnd As Long, RepeatFor As Long)
    Dim RndVar As Variant, rsd() As Variant, x As Long
   
    With Application
        ReDim rsd(RepeatFor - 1)
        For x = 0 To RepeatFor - 1
            RndVar = .RandArray(1, 5, LowerRnd, UpperRnd, True)
            rsd(x) = .StDev(RndVar) / .Average(RndVar) * 100
        Next x
        MultiRSD = .Max(rsd)
    End With
End Function

Used like:

=MultiRSD(39,46,1000)

Or

Sub test()
    MsgBox MultiRSD(39, 46, 1000)
End Sub

 

[traihan]

Hello Georgiboy, Thanks for the above. Would you kindly walk me through how to run now?​

 

[Georgiboy]

Open Excel
Press ALT+F11 (or navigate to the VBE)
Right click on 'ThisWorkbook'
Goto 'Insert' then 'Module'
Paste the MultiRSD code from above in the module you just created.

Now go back to Excel and in a cell type:

=MultiRSD(39,46,1000)

If you would rather get the result through a message box then:
In the module you created above, also paste the below:

Sub test()
    MsgBox MultiRSD(39, 46, 1000)
End Sub

Then go back to Excel and press ALT+F8 then select the macro 'test' and click run

 

[traihan]

Thank you it Worked. Gives me the largest RSD.

One thing I did not realize that it would not print out the random numbers associated with the highest RSD. One thing I also need to know are the possible set of numbers that produced the high RSDs. Is that doable? I understand it is going to be big data set.

Again appreciate all you help.

 

[traihan]

Here's a start:

Function rsdCalc(VarA, VarB, runs)
Dim x() As Integer
Dim stdDevX As Long
Dim averageX As Long

ReDim x(1 To runs)

For i = 1 To runs

x(i) = WorksheetFunction.RandBetween(VarA, VarB)

Next

stdDevX = WorksheetFunction.StDev(x)
averageX = WorksheetFunction.Average(x)
rsdCalc = stdDevX / averageX * 100

End Function

Hi bassplr19, can you please also guide me how I run this macro.

 

[bassplr19]

Open Excel
Press ALT+F11 (or navigate to the VBE)
Right click on 'ThisWorkbook'
Goto 'Insert' then 'Module'
Paste the rsdCalc code from above in the module you just created.

Now go back to Excel and in a cell type:

=[B]rsdCalc[/B](39,46,1000)

Follow nearly the exact same procedure above

 

[Georgiboy]

One thing I did not realize that it would not print out the random numbers associated with the highest RSD. One thing I also need to know are the possible set of numbers that produced the high RSDs. Is that doable? I understand it is going to be big data set.

You can try the below, it will return 5 columns with the random numbers, column 6 will be the StDev result for that row, at the top of the StDev column will be the max value from all of the StDev's:

Function MultiRSD(LowerRnd As Long, UpperRnd As Long, RepeatFor As Long)
    Dim RndVar As Variant, rsd() As Variant, x As Long, y As Long
  
    With Application
        ReDim rsd(RepeatFor, 5)
        For x = 1 To RepeatFor
            RndVar = .RandArray(1, 5, LowerRnd, UpperRnd, True)
            For y = 0 To 4
                rsd(x, y) = RndVar(y + 1)
            Next y
            rsd(x, y) = .StDev(RndVar) / .Average(RndVar) * 100
        Next x
        For y = 0 To 4
            rsd(0, y) = ""
        Next y
        rsd(0, 5) = Application.Max(Application.Index(rsd, , 6))
        MultiRSD = rsd
    End With
End Function

Book1
	A	B	C	D	E	F
1						6.994473
2	46	43	43	45	46	3.400393
3	40	43	44	40	45	5.429653
4	42	41	39	40	43	3.856436
5	40	42	45	46	44	5.549122
6	45	46	43	40	40	6.483382
7	40	40	44	45	39	6.494835
8	46	44	42	46	41	5.20628
9	45	46	46	43	41	4.90486
10	41	42	39	39	42	3.735407
11	40	45	41	40	40	5.262011
12	46	39	40	42	44	6.785697
13	45	40	45	42	46	5.756835
14	44	43	43	46	45	2.949865
15	42	41	44	45	41	4.264296
16	45	46	46	42	39	6.994473
17	46	41	44	39	43	6.342374
18	42	45	42	39	46	6.483382
19	40	44	40	44	41	4.902847
20	45	46	44	43	40	5.280213
21	46	44	41	40	44	5.696488
Sheet1

Cell Formulas
Range	Formula
A1:F21	A1	=MultiRSD(39,46,20)
Dynamic array formulas.

 

[traihan]

Thanks. will try.