Data Table only calculates correctly when saving

[najibk]

[FONT=&quot]I have a worksheet where I am running a mote carlo simulation. It has about 1000 rows. Several of the columns have random numbers in there (rand() and randbetween(0,5)). One Column has binom.inv with rand() for alpha.

[/FONT]
[FONT=&quot]The output is the sum of the final column's 1000 rows.

[/FONT]
[FONT=&quot]Anyway, in order to run this simulation several times I created a data table where the column input cell is a blank cell. It works the first time always.

[/FONT]
[FONT=&quot]However, if I change any of the assumptions it stops calculating correctly. I have tried F9. shift + F9, ctrl + alt + shift f9, all of them. Nothing works. I have it calculation set to manual for convenience, but I have tried automatic and automatic except data table, and the issue remained.

But, if I click save it magically recalculates everything correctly. I am not sure why.

[/FONT]
[FONT=&quot]What is going on? How do I fix it?

Thanks for the help[/FONT]

 

[BrianJN1]

This a workbook with only one worksheet.
Everything runs directly from formulae.
You have set "Calculation" at various modes.
At the end you have a summary line as the output some 1000 rows down.

If you had, say, only 3 rows in your simulation, would you get a correct summary?

Maybe a copy of the formulae of your first row of simulation might assist. You don't seem to have any macros running.

Sorry, I don't know but I'm trying to tease out where you might explore.

 

[najibk]

Ya, no macros, 1 worksheet. I need the ~1000 rows because the further down you go the results approach 0, and I need to get to that to "end" 1 round.

I've tried copy pasting the sheet to a different file, using a different computer, etc. and same result, so there must be something wrong with the model itself...

 

[BrianJN1]

Maybe you could either post an image or a few rows of your workbook using one of the options offered on this site of some cloud environment with a link that can be shared.

 

[najibk]

Here is the full model, would be easier than the formulas. Thanks for the help. https://www.dropbox.com/sh/08rqa659bc49zv4/AAA19-rmoQBnBIBRMr7ss9-Ia?dl=0

 

[BrianJN1]

I have downloaded and opened the model BUT I found that 26 lines were hidden, well not exactly hidden but having no row height. Is there some reason why they were hidden especially since formulae reference that area?

Now, the end result again is what?
The columns to the side, those beyond "Z"? At this point I am still asking questions of the table as well as of what I think you may be actually asking but not stating specifically.

 

[najibk]

I have downloaded and opened the model BUT I found that 26 lines were hidden, well not exactly hidden but having no row height. Is there some reason why they were hidden especially since formulae reference that area?

Now, the end result again is what?
The columns to the side, those beyond "Z"? At this point I am still asking questions of the table as well as of what I think you may be actually asking but not stating specifically.

Theyre just hidden for convenience as they are just some assumptions, they can be unhidden. The end result is Q36:Q38. That is the average of the data table used to run the monte carlo sim.

The data table links to Q29, 30, and 31, which are based on the sums columns K and M

 

[BrianJN1]

I can't be quite sure about your issue but it may just be a matter of timing.
I opened up the assumption area, set the sheet to calculate automatically and then changed the bet value to 20, 15, 11, whatever. It took somewhere between 10 and 15 seconds to recalculate the data appearing in those Q cells.