Basic of Two way Data Table
This is an extension of the post What-If analysis in #Excel.
If you do not know about Goal Seek or One-Way Data Table, you may read Start the new decade with Goal Seek in #Excel and What-If analysis in #Excel before moving on.
We talked about a situation that we’d to have a list of outcomes with different initiate savings (Variable), by using One way Data Table. Since we have two variables (the other one is the Weekly increment%), we may actually see the outcomes of different scenarios in a cross table, like this:
With Two way Data Table under What-If analysis, this can be achieved easily… as long as you know the setup required. 😉
You may download a Sample File to follow along.
The Setup
This initiate setup is discussed here. What we needed is a formula (resides in B4) resulted from at least two variables (reside in B2 and B5).
Then we put the two variables of interest in a cross table (at the range H4:M14) like this:
Tip: We may have a shorter or longer lists of variables. We can also change the hard-coded values later.
The most tricky part is the simple formula (=B4, i.e. the output) we put in upper left corner (H4) of the range. That is the key!
Once we have this setup properly, the rest is quite straight-forward:
Select the range (H4:M14), then
- Go to Data tab
- What-If Analysis
- Data Table…
Input the Row and Column input cell accordingly, as follow:
Honestly, when I first encountered this (long time ago), I found it quite confusing… which one is Row? which one is Column? I did that even by trial and error (or wild guessing… 😁)
Nevertheless when we look wider, it’s quite intuitive indeed.
- Row input cell is the cell reference where we put the variables on row;
- Column input cell is the cell reference where we put the variables on column.
Isn’t it clear?
After clicking OK…
As simple as this!
With this two-way Data Table, we can answer the question like:
“What if I want my Total Saving to be just over $2000… What is the initiate saving and weekly increment % I need to achieve that?”
Data Table is another hidden gem waiting for you to discover in Excel. It is powerful yet easy to set up! I hope you will treasure it. 😉
wmfexcel 