What-If analysis in #Excel – Part 2

Basic of Two way Data Table

This is an extension of the post What-If analysis in #Excel.

Excel Tips - TwoWay Table 1

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:

Excel Tips - TwoWay Table 4

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

Excel Tips - TwoWay Table 5

Then we put the two variables of interest in a cross table (at the range H4:M14) like this:

Excel Tips - TwoWay Table 3

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

  1. Go to Data tab
  2. What-If Analysis
  3. Data Table

Excel Tip - OneWay Data Table

Input the Row and Column input cell accordingly, as follow:

Excel Tips - TwoWay Table 7

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.

Excel Tips - TwoWay Table 6

  • 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…

Excel Tips - TwoWay Table 1

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?”

Excel Tips - TwoWay Table 8

 

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

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.