2 Way Data Table issues when running monte carlo simulation

**excel_nuance** · 03-03-2009, 09:32 PM

Hello Excel gurus,

I've spent the past 4 days running into an odd problem and despite searching all over the web and through various books, have not been able to determine the solution. I'm hoping one of you may be able to provide the answer to my problem.

The issue is that I've noticed that as I try to run monte carlo simulations using 2 way tables that sometimes the 2 way tables will work correctly by applying the formula throughout the data set of the table and then other times it will just copy the values in the row headers throughout the dataset.
This becomes extremely frustrating as I can never be sure when Excel will do it and when it will decide not to update the data set correctly.

I'm unaware whether there is a nuance to the use of the 2 way data table that I may be missing that is causing this issue or is there something I am blatantly doing wrong?

Typically I will set up my base equations and calculations; set the upper left corner of the data table to the equation, set up the left hand column under the equation to go from 1 to 1000 and then set up my row values in the row adjacent to the equation. Then I go to what if analysis in Excel 2007, select data tables, select the appropriate row and column and cross my fingers hoping it will work.

I've attached my examples to this email in the hopes that it may provide an idea of my issue.

Thanks in advance.

Best regards,
Alpesh

**Andy Pope** · 03-04-2009, 11:37 AM

The 2 sheets are not doing the same thing.
It's easier to see the differences when the data layouts are the same. So to
do that I added 2 rows.

Now you can see that the formula and data table information are not the
same.

**excel_nuance** · 03-04-2009, 12:00 PM

Thanks Andy for your response.

How was it that you were able to determine that the calculations in the note were not the same?

I tried to follow suit with a similar example, but have run into a similar issue as the previous sent. As suck I would like understand how you are able to determine and then repair this issue.

I have attached a sample file. One table use a directly entered equation and the other uses a reference. Yet the one with the equation works and the other does not. In this situation how would you fix the issue?

Again thank you for all of your help.

Sincerely,
Alpesh