I have a monte carlo simulation I am running in the cloud based on an Excel spreadsheet. The process works in a loop, and goes through 10000 iterations. The way I have it set up, it goes through the cells that will hold the random variables and assigns them a random number based on a distribution. After it’s done assigning the new random numbers, I have to pull the resultant calculation from a single cell on another sheet.
Which should I used if I must recalculate my spreadsheet 10,000 times?
I believe I know what’s happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.
jpo645:I believe I know what's happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.
jpo645:I saw in another post, someone used something like FormulaCalculation(true, CustomFunction)... what is that second parameter in the FormulaCalculation method? Perhaps I could circumvent having to rewrite every formula by using a custom formula. (In fact, how I am rewriting formulas now is to replace a UDF I had created...)
Thank you! If I replace my custom function with this, and then simply iterate 10000 times (Without having to rewrite the formulas each time), do you believe that should speed things up considerably?
I can’t really provide you even a console application as the work is proprietary. What I can do is provide you the code (slightly edited) where the bottleneck takes place:
One other question, if I am using