All language subtitles for 4. Data Tables Two Variables

In the last lesson, we saw how we could put together a one variable data table.

And in this lesson, I'm going to show you how you can do a two variable data table.

And if you understood what we were doing in the last lesson, then you're really not going to find this

too much of a problem.

So the only real difference here is that we have to impose instead of one.

So this time we have our interest rates running across the top and then we have different loan amounts

running down the side.

So effectively, I want to create a nice big table that's populated with monthly payments, which are

dependent on the interest rates and the loan amount.

Now, as always, everything here starts with a PMT calculation.

And for this one, I haven't actually completed it in Selby.

Six.

So let's go ahead and do that.

I'm going to type in equals PMT.

Remember, our first argument is the rate, so the rate is in-cell before.

Notice that this is also a yearly rate.

And because we're working out monthly payments, we need to divide it by 12.

Our next argument is the number of payments.

Now again, the term here is 30 years, so this is a pretty long term.

So this is going to be something like a mortgage payment now because we have it there written out as

30 years.

We need to turn this into two months.

So our number of payments is going to be be five multiplied by 12 this time comma.

And our final argument for this calculation is Peavey the present value.

So that is going to be the loan amount, which is B three.

Now remember, if you want this to show as a positive value, you need to put a minus in front of the

present value argument.

Let's close off the bracket, hit enter and see what we get.

Now again, I have some currency issues going on here because I use two different currencies in my spreadsheet,

so let's change that to currency and dollars.

Now, once again, I want to use this BMT calculation as the basis for my data table.

And because we need to be able to select a rectangular area, I need to link to my PMT calculation from

Cell B nine.

So let's click in sell B9 type in equals and select Sell B6 and Hansa.

So now I pretty much have everything I need in order to build this data table.

So let's select this range of cells up to the data tab across to what-if analysis and data table.

Now, last time, we only filled out the column input cell because it was a one variable data table.

Because this is a two variable data table, we need to have a row input and a column input.

So our row input this time is going to be our interest rate.

So we need to select sell before from the table and our column input cell is going to be the loan amount.

So let's select that from the table as well and click on OK.

And like magic, we have all of our calculations now.

I need to apply some formatting here, so let's go to the Home tab.

I'm going to apply currency formatting, and yes, I'm going to leave it on to decimal places.

Leinster will check to make sure our calculations are correct.

Now, if I take a look at the table, the loan amount 300000 at a three percent interest rate should

be one two six four eight one.

And if I look at my table, I can see that yes, that is one two six four eighty one.

So my table looks to be working correctly.

Don't forget that if you want to hide the PMT calculation in the corner, control one to jump into format.

Cells go to custom formatting and in the tight box, which is going to type in three semicolons and

click on OK.