Use the Goal Seek function to back into an answer. Episode 436 shows you how.
This blog is the video netcast companion to the books Learn Excel from MrExcel and Excel 2007 Miracle Made Easy. Download a new two minute video every workday to learn a tip for both versions of Excel!
This blog is the video netcast companion to the books Learn Excel from MrExcel and Excel 2007 Miracle Made Easy. Download a new two minute video every workday to learn a tip for both versions of Excel!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's talk about using Goal Seek today.
Now, many times in Excel we set up a formula, and we try to find the right numbers to plug into our spreadsheet to create a certain answer in that formula.
Here's a simple one, where I'm trying to figure out a car payment.
So, I have a car that's going to cost 29995, 60 months and the rate is 5.25% Down here in cell B5, I've used the payment function PMT, to figure out the car payment.
Now, once I have this, I can start to do some What-If-Analysis.
I can say, well, I don't want to pay that much, so I wonder, you know, if I go for a lower priced car.
Or maybe, pay for it over more months, or try and find a lower rate.
But the problem with this is, you end up playing kind of the higher lower game.
Like they used to do on the prices, right?
Now what you can do is, there's a great feature called Goal Seek.
And in Excel 2003, it's on the Tools menu.
You go to Tools, Goal Seek, and say that we want to set that payment cell, equal to let's say 450, by changing either the price or the term.
So, let me change the price. Click OK. Very quickly, excel does the higher lower game.
And it comes up with an answer most of the time.
Now, it shows you the answer, that if we come up with a price of 25748, our formula will come up with the 450.
You can either choose to accept that or to cancel.
And the beautiful thing is, all of the formulas are still alive.
So, I can go in and change any of the original numbers, and my result will change.
Now in Excel 2007, they have the exact same feature.
Except it, instead of on the Tools menu, it's now on the Data ribbon.
So, I have the exact same spreadsheet here in 2007, but I'm, go to the Data ribbon, and then in the Data Tools group on the What-If-Analysis drop-down, choose Goal Seek.
Now we are back to familiar territory, just like we had in excel 2003.
Want to set this cell to a certain value, by changing another cell.
Click OK.
And now we're back in familiar territory Hey, there you go. Thanks for stopping by.
Will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Let's talk about using Goal Seek today.
Now, many times in Excel we set up a formula, and we try to find the right numbers to plug into our spreadsheet to create a certain answer in that formula.
Here's a simple one, where I'm trying to figure out a car payment.
So, I have a car that's going to cost 29995, 60 months and the rate is 5.25% Down here in cell B5, I've used the payment function PMT, to figure out the car payment.
Now, once I have this, I can start to do some What-If-Analysis.
I can say, well, I don't want to pay that much, so I wonder, you know, if I go for a lower priced car.
Or maybe, pay for it over more months, or try and find a lower rate.
But the problem with this is, you end up playing kind of the higher lower game.
Like they used to do on the prices, right?
Now what you can do is, there's a great feature called Goal Seek.
And in Excel 2003, it's on the Tools menu.
You go to Tools, Goal Seek, and say that we want to set that payment cell, equal to let's say 450, by changing either the price or the term.
So, let me change the price. Click OK. Very quickly, excel does the higher lower game.
And it comes up with an answer most of the time.
Now, it shows you the answer, that if we come up with a price of 25748, our formula will come up with the 450.
You can either choose to accept that or to cancel.
And the beautiful thing is, all of the formulas are still alive.
So, I can go in and change any of the original numbers, and my result will change.
Now in Excel 2007, they have the exact same feature.
Except it, instead of on the Tools menu, it's now on the Data ribbon.
So, I have the exact same spreadsheet here in 2007, but I'm, go to the Data ribbon, and then in the Data Tools group on the What-If-Analysis drop-down, choose Goal Seek.
Now we are back to familiar territory, just like we had in excel 2003.
Want to set this cell to a certain value, by changing another cell.
Click OK.
And now we're back in familiar territory Hey, there you go. Thanks for stopping by.
Will see you next time for another netcast from MrExcel.
