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.
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.
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.