Run many what-if scenarios at once using the Data Table command. Episode 794 will show you how to set up and use this powerful command.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
You know, lots of times, I show this formula for creating a car payment using the PMT function.
This is one of the standard things in my seminar, and I have a cool trick today.
For one, we want to do some what-if analysis.
Usually, you know, you might come here and start to plug in different numbers, you know, plug in different numbers, but today's trick will allow us to do 40 different what-if analyses in one command.
So, basically, what we want to do is start with that formula, your key formula, in the top left corner cell.
It has to be in the top left corner cell.
Down the left-hand column, have one variable.
So, here, I'm going to say that I want to see this car payment if I paid for it over 48 months, 54 months, 60 months, or 66 months, and then, across the top row, all the different prices that you're considering, so here's 22,000, 24, 26, 28, and 30, and, basically, I want to run that formula for every combination of the terms along the left and the prices along the top.
[ =PMT(B3/B12,B2,-B1) ] So, I select the key formula plus the number of rows and the number of columns, and then I'm going to come to the DATA menu and choose TABLE -- DATA, TABLE -- get a little dialog box here.
This dialog box basically says, hey, look, you have a whole bunch of items along the top row, and you want me to take those numbers and plug them into which cell?
Well, I want to plug it right here into the PRICE cell, and then it says, hey, you have a whole bunch of numbers along the left column.
You want me to take those and plug them into which cell?
Well, I want to plug that here into cell B2 where my TERM is, and when I click OK, it creates an amazing function in the middle here.
It's called the table function, the table function.
Basically points to 2 cells and this is a live function.
You know, I can change this.
So, for example, if I decide, well, now I want to look between 23,000 and look in increments of a 1000 going across the top, so I change those numbers across the top, everything changes automatically.
If, for some reason, I say I want to start at 36 and look at 48, 66, 60, and 72, everything changes.
In fact, I could even change this formula and everything will automatically update, you know.
So, for example, I said I wanted to take B1÷B2, which I realize makes no sense at all, but the point is this interactive what-if table in the middle is completely live.
I can change any of the numbers along the left column, the top row, or even the formula there in the top, and see all of the different what-if analyses very, very quickly.
That's under the DATA menu, choose TABLE.
It's still there in Excel 2007 on the DATA tab, what-if analysis, and then DATA, TABLE, you'll find the same command.
Very, very powerful.
It's something I used to use all the time before there were pivot tables.
Now that we have pivot tables, I don't use it as much, but it is a very, very cool trick.
Alright.
Well, hey.
Tomorrow is the 4th of July in the United States.
We’ll still do a netcast tomorrow: so, for some of you, we’ll see you tomorrow; others, we’ll see you on Monday when you get back from the holiday weekend.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
You know, lots of times, I show this formula for creating a car payment using the PMT function.
This is one of the standard things in my seminar, and I have a cool trick today.
For one, we want to do some what-if analysis.
Usually, you know, you might come here and start to plug in different numbers, you know, plug in different numbers, but today's trick will allow us to do 40 different what-if analyses in one command.
So, basically, what we want to do is start with that formula, your key formula, in the top left corner cell.
It has to be in the top left corner cell.
Down the left-hand column, have one variable.
So, here, I'm going to say that I want to see this car payment if I paid for it over 48 months, 54 months, 60 months, or 66 months, and then, across the top row, all the different prices that you're considering, so here's 22,000, 24, 26, 28, and 30, and, basically, I want to run that formula for every combination of the terms along the left and the prices along the top.
[ =PMT(B3/B12,B2,-B1) ] So, I select the key formula plus the number of rows and the number of columns, and then I'm going to come to the DATA menu and choose TABLE -- DATA, TABLE -- get a little dialog box here.
This dialog box basically says, hey, look, you have a whole bunch of items along the top row, and you want me to take those numbers and plug them into which cell?
Well, I want to plug it right here into the PRICE cell, and then it says, hey, you have a whole bunch of numbers along the left column.
You want me to take those and plug them into which cell?
Well, I want to plug that here into cell B2 where my TERM is, and when I click OK, it creates an amazing function in the middle here.
It's called the table function, the table function.
Basically points to 2 cells and this is a live function.
You know, I can change this.
So, for example, if I decide, well, now I want to look between 23,000 and look in increments of a 1000 going across the top, so I change those numbers across the top, everything changes automatically.
If, for some reason, I say I want to start at 36 and look at 48, 66, 60, and 72, everything changes.
In fact, I could even change this formula and everything will automatically update, you know.
So, for example, I said I wanted to take B1÷B2, which I realize makes no sense at all, but the point is this interactive what-if table in the middle is completely live.
I can change any of the numbers along the left column, the top row, or even the formula there in the top, and see all of the different what-if analyses very, very quickly.
That's under the DATA menu, choose TABLE.
It's still there in Excel 2007 on the DATA tab, what-if analysis, and then DATA, TABLE, you'll find the same command.
Very, very powerful.
It's something I used to use all the time before there were pivot tables.
Now that we have pivot tables, I don't use it as much, but it is a very, very cool trick.
Alright.
Well, hey.
Tomorrow is the 4th of July in the United States.
We’ll still do a netcast tomorrow: so, for some of you, we’ll see you tomorrow; others, we’ll see you on Monday when you get back from the holiday weekend.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
