Excel Data Analysis : Data Tables

in microsoft •  5 years ago  (edited)

In this session, we are going to look how Excel Data Tables work within What-If analysis.
Data Tables is a way for us to interject alternative values into a calculation without the need to recreate the
calculation.
Excel through the use of a data tables will essentially take the alternatives and put them into the formula and recalculate it for us.

1.PNG

We have a simple table with three values ( mortgage amount, interest rate & term in months). Below we have a list of alternative interest rates.
What I want to know is how much my monthly payment is going to be, based on the three given values. I want to know what that payment would be like if a varied up the percentage rates. Also, I want to be able to change the mortgage amount or number of months on the fly if I want to.

We can recreate the formula that figures out the monthly payments 8 different times OR we can use our table from B7 to C14 range as our data table and Excel will do the rest for us.

There are two different types of data tables:

  1. Single variable data table - meaning there is only a single set of alternatives
  2. Multi variable data table - meaning there are two or more different sets of alternative values

2.PNG

In our example, we have a single variable data table, which has to be set up in a very specific way.

3.PNG

The very first column in the table has to be the list of alternative values, in our case is the percentage rate.

4.PNG

The second column is where the original formula is going to be placed and down bellow, it is where excel is going to recalculate all for us automatically based on the alternatives and original formula.

5.PNG

In the second column, the first row we are going to put the formula =-pmt(C3/12,C4,C2)
We put a negative value in front of the formula just to put the value positive.

In our case, we have to pay roughly $17k for a $180k mortgage amount for an 8% interest rate for the next 200 months.
So now we have the single variable table set up, first column alternative values, second column first row and down bellow the excel will fill next for me. Once it's set up, the rest is a very simple process.

6.PNG

The first step is to convert into a data table. Select the table from B7 to C14 and go to data tab > data tool section > what if analysis > data table

7.PNG

Since our is a single variable table, we are going to use the -Column input cell- which essentially means that we are going to take the column of alternative rates and place it into C3 to change the 8% rate where our formula is looking. And it's done.

8.PNG

Anytime you got a set of what-ifs, you can set up a simple table with alternatives and let excel calculate all of those alternatives for you.

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

hay, nice to see you back!

Congratulations @exgap! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 1 year!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!