UDF that changes after random number updates

[OleMiss2010]

Essentially I am creating a Monte Carlo simulation that will look at various metrics across many scenarios. I would like to have a UDF in place that will count and sum certain outputs after each iteration of the simulation so in the end I have a count/sum (depending on the output) of all of the simulations rather than have to have many copies of my sheet or having to copy and paste over and over.


Is this clear?

 

[Luke M]

Welcome back Ole Miss!

If you declare a public variable, at the top of a module, it would retain it's value even after the routine has run. I think a regular UDF would end up causing a circular or overflow error, but your regular macro could be triggered by a button push or worksheet_change event. Overall code would look like:

[pre]

Public MyValue As Double

Sub UpdateScores()
'Get new total
MyValue = MyValue + Range("A5").Value

'Output
Range("B5") = MyValue
End Sub

[/pre]

 

[OleMiss2010]

Thanks for the welcome back. Sorry I haven't been a contributing member of the forums as of late.


Here's an interesting twist on my question, what if I wanted to only look at the last 1000, 100, or any other number of iterations. Is there a way to introduce this to the Sub so that it will essentially replace one value with another after I hit my target number of simulations? Perhaps I could use another cell to hold the value of the -nth iteration?

 

[Luke M]

Might be easier to store the values in the workbook. Could do something like:

[pre]

Dim RecordCount as Integer
Range("A2:A" & RecordCount) = Range("A1:A" & RecordCount - 1)
Range("A1") = NewValue
TotalScore = WorksheetFunction.Sum(Range("A1:A" & RecordCount))

[/pre]
Just a starter idea. If you want to try using a VB array (something I am currently working on learning more about), I'd recommend starting here:

http://www.cpearson.com/excel/vbaarrays.htm

 

[OleMiss2010]

I was thinking about creating a values sheet or something that would store the first n number of values and then replace the oldest one thereafter or something. I'll read up on the VBA arrays as well. Thanks for the help Luke.

 

[Hui]

Ideally it would be nice to use two Data Tables and constrain the first table (no of iterations) using a column input value from a second table

Unfortunately Excel doesn't handle this, which is a real shame.


Can you post a sample of what your trying to achieve to enable us to assist further


Did you read the section on Multiway data Tables in http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide

It's about half way down the post.