All language subtitles for 3. Data Tables One Variable

In this case, we're going to take a look at the third type of what-if analysis utility, and that is

data tables using one variable.

And there are two kinds of data tables one variable and two variables, and the variable really relates

to how many inputs we have.

So in this lesson, we're going to look at the one variable data table and then in the next lesson,

we're going to look at two variables.

Now, a data table allows you to effectively work out payments with varying interest rates.

So what we have at the top here again is a very similar table to ones we've been working on in previous

lessons.

We have a loan amounts, we have the yearly interest rates, we have the term of the loan in months.

And then I've used that PMT calculation again to work out the monthly payment.

Now again, this is showing as a negative value.

Remember, if you want this to display as positive, you can simply click in front of the present value

argument and add a minus sign, and that will just turn that into a positive value.

Now, this monthly payment is being calculated at a two percent interest rate.

But what if I want to see what my monthly payments will look like with lots of different other interest

rates?

So underneath I basically have interest rates running from one percent down to 3.5 percent, and I want

to see what my monthly payments are going to look like.

Now when are you using data tables?

One of the things you need to remember is we basically need to be able to select all of our inputs that

we need in a kind of rectangle formation.

So we need to be able to drag our mouse over all of the inputs in order for the data table to work on.

One of the inputs that we need is the monthly payment.

So if I leave the monthly payment up here, it's not really going to work because if I do this, I'm

kind of including the interest rate title and some blank cells as well.

So all I'm going to do here is I'm going to link to the monthly payment simply by typing equals B6.

Now it looks like I have two different currencies going on here.

Let's change this one to us dollars, and I'm going to change that to currency format as well.

That's better.

So now I have everything I need for this state table in an easily selectable range.

So let's select it.

Let's jump up to data.

Go into what if analysis and data table.

Now, if I was doing a two variable data table, I would need to have inputs for the row and the column.

But we're only doing a one variable data table, so we only need to complete one of these.

And the one that we complete is basically the one that contains all of our values.

So our values, our interest rates are listed in the column.

So the column input cell is going to be the interest rate.

So we're going to select the interest rate from the table.

Let's click on.

OK, now I'm going to apply currency formatting to these as well, just so everything looks nice and

consistent, and we can easily check if these calculations are correct by just adjusting what we have

in the table.

So let's take let's take the bottom one.

Five percent.

So if I change this value up here to three point five, we should get exactly the same as what we get

in our data table, which we do.

So I know that this is working correctly.

Now, another little quick tip whilst we're here, if you are putting together a table like this, it

might be that you don't necessarily want to have this PMT calculation showing at the top of the list.

Now, a really easy way to kind of hide this.

And when I say hide, I mean, the value is still effectively there.

You just can't see it in the cell.

If we press control one to pull up our format cells dialog box and go to custom formatting, what we

can do is type in three semicolons and click on Okay, and that's going to hide that value if I click

back on that cell.

Notice that the value is still there, it's still linking through to the monthly payment.

So our formulas are still going to work.

We just can't see it in the cell.