This is not about how to use Data Table. Just to share a problem I encountered with Data Table recently. If you have never heard about Data Table in Excel, you may skip this post. 🙂
Data Table (part of the What-If analysis tools) is one of my favorite tools in Excel. Nevertheless, it is also one of the most under-used tools. I guess not many people use it. If you are one of Data Table users, have you encountered a situation that gives you unexpected result? I just did.
The following screenshot shows the problem: 
What I tried to do was to set the variables in column label to be dynamic, based on the original value used in the calculation.
Take a look at the value I put in column G, where I actually referenced to B1 as the starting point. “=G3+1” was used to set up incremental value by one… However, the answers returned were incorrect! (I was lucky enough that the calculation was not a complicated one so that I could spot the incorrect figures easily.)
For comparison, the column label in column D were all hard-coded values; and the Data Table returned correct results as expected.
I then tried to hard code the value in G3 from “=B1” to “100”; and the Data Table returned correct answers.
I am not sure why it happens. What I guess is that the reference (i.e. “=B1”) of the column label back to the calculation thread triggers a loop of calculation instead of simply substituting the values from column label into the calculation. In our example, the result for 105 is actually the sum of (100, 200, 300, 400, 500) = $11,500. And this applies to the rest of the result displayed on the Data Table on H4:H13.
Conclusion
Do not reference a label (be it on row or column) back to the calculation thread used when setting up Data Table.
If you have an explanation or a workaround for this situation, please share by leaving comment.
wmfexcel 
Wow. I have encountered exactly the same issue with a data table. In my case, the column contains dates, being the first day of consecutive months:
Row 1 of the DT returns the correct result
Row 2 likewise
Row 3 (month 3) returns the result that belongs to month 4 (i.e. it skipped 1 result/month)
Row 4 (month 4) returns the result that belongs to month 7 (i.e. it skipped 2 results/months)
Row 5 (month 5) returns the result that belongs to month 11 (i.e. it skipped 3 results/months)
Row 6 (month 6) returns the result that belongs to month 15 (i.e. it skipped 4 results/months)
Row 7 (month 7) returns the result that belongs to month 21 (i.e. it skipped 5 results/months)
and so on, and so forth. I’ve no clue what it causing this weird behaviour.
LikeLike
I’ve figured out my own problem. The first value in my column (i.e. the first date) was linked to the column input cell, which must have created a circular reference of sorts. I hard coded that first date in the data table, and all is behaving as I would have expected it to.
LikeLike
Thanks for sharing your case. Glad to hear that you fixed it. 👍🏻
LikeLike
Hi Tonje, thanks for sharing your case here. I hope there is a solution too.
LikeLike
I experienced exact the same problem! Thanks for posting this. This seems like a bug, and in my Excel sheet, I got exact the same error and multiple on the error as you got in your simplified example. I have tried several times, everytime when having reference to the variable to simulate on in the Table I got the same problem. From row 3, the results starts to turn out wrong, and then the error escalates. The multiple of the error, relates to the difference you are shifting the variable. Row 3, by 1 times the multiple, row 4 by 6, row 5 by 10, row 6 by 15. Would really appreciate a more flexible solution to avoid this error!
LikeLike
Has anyone found a solution to this?
LikeLike
Thank you for your advise. Have a different problem with my table, which calculates cash flows based on different production scenarios. When selecting any of these scenarios, the spread sheet works properly and gives the correct result. However, in the comparison table for these scenarios, the table content changes depending on which scenario I selected, which must not be the case. After long searching I identified the problem. One of my calculation lines contained a power calculation which was linked to another table as the cell content was the result of a probability calculation. So it appears that when excel calculates a table based on different parameters, it will not calculate sub-tables which feed in. [there is no circular reference in my model, and the sub-table uses different parameters than the main comparison table.
LikeLike
If you want to learn a lot more about data Tables have a read of:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
LikeLike
Hi Guys
If you want to know a lot more about data Tables and some of their uses have a read of:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
LikeLike
Hi Hui,
Thanks for stopping by.
You know what, I read that article before. It’s a good one on the Monte Carlo Simulations.
Thanks for sharing the link here.
Btw, do you have any comments on the problem I encountered as described in my post? Appreciate your advice.
Cheers,
LikeLike
Data Tables are weird. The only time in 15 years that I encountered one was when an Excel novice was getting wrong results and I kept asking, “where the heck are your formulas?” She’d been given step-by-step instructions on how to do something and it was a mess. The Data Tables weren’t even necessary for what she was trying to do.
I talked with someone else about Data Tables. In his long experience he said he’s seen them in tutorial and books but NEVER in practice. Thanks for bringing this up. You have me at least curious again.
LikeLike
Hi Oz,
Thanks so much for your comments 🙂
To my surprise that you do not like Data Tables. While I totally agree on you that some people do not use Data Table the way it should be (well, that applies to not only Data Table but lots of functions in Excel, isn’t it?), I do find it quite useful in conducting What-if analysis.
A typical question from boss: What if X increased by X% and Y increased by Y%?
Most of the time, I could impressed my boss with a Tw0-Way Data Table that showed results under different scenarios of two variables, because I knew he/she would ask for another value for X and Y after I showed the result he/she originally asked for. So why not make a list beforehand? 🙂
Cheers,
LikeLiked by 1 person
Gotcha! You’re providing some real insight for me here.
I’ve never done what-if analysis in my work. So, it’s interesting to be exposed to some aspects of Excel that wouldn’t naturally cross my path.
LikeLike