Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
When you create a formula in Excel, it's easy to ask questions such as, how much is 15% of 200. It's much more difficult to ask a question such as, how much can my business borrow and have a monthly payment of $125,000 or less. Rather than create a complex formula, you can use the Goal Seek feature to find answers to that type of question quickly. In this worksheet, I have information about a loan, the term, interest rate, and monthly payments, and also, I have my target payment of $125,000.
So as these things stand, my loan amount, term of paying off over 10 years, and interest rate of 5.75% will result in a monthly payment of over $164,000. I want to get that payment to $225,000 or less, and the only way that I can do that, at least for the purposes of this exercise, is to change my loan amount. If I want to find out how much you can borrow on these terms and end up with the target payment of $125,000, I can use what's called Goal Seek. To work with Goal Seek, I can go to the data tab of the Ribbon, click the What-If button, and then click Goal Seek.
The Goal Seek dialog box has three controls. The first is Set cell, and that cell is the cell that contains my target value. In other words, my payment, I want to be $125,000, so that is the cell I am going to change. This cell here, B9 is only for illustrative purposes. It's a flat value. I can't add anything to it, so instead, I need to use this cell here, which has a formula that calculates a monthly payment based on loan amount, term, and interest rate. So the cell that I want to set is cell B7.
I want to sell it to my target value of $125,000, and I want to do that by changing the loan amount, which is in cell B3. Everything looks good. When I press Return or click OK, Goal Seek returns a solution, and it also displays the solution in the dialog box. If I want to have a target payment of $125,000, I can borrow $11,387,000, and a little extra. If you want to keep that value in the worksheet, you can click OK; or if you want to go back to your previous values, you can click Cancel.
Goal Seek analysis let's you find answers to questions that are easy to solve using a formula. Whenever you want a formula in one cell to produce a target value arrived at by changing the value of another cell, try using Goal Seek to discover the answer.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.