Click Developer - Stop Recording Looking at the recorded code Lets take a brief look at the recorded code.This got my mind buzzing with ideas, so I wanted to share it with you, along with a few of my own developments.Ive not used the technique extensively to date, but it seems to be a useful approach.While probably one of the best features in Excel, Goal Seek is quite clunky to use.
Its hidden away in the Data menu; so you need to know its there to find it. Wouldnt it be great if we could make it work with a single click, or maybe automate it completely Well guess what, thats exactly what were going to do. Use Goal Seek In Excel Download The FilesWatch the video: Watch the video on YouTube Download the example file I recommend you download the files which support this post, as youll be able to work along with examples. Use Goal Seek In Excel Plus The FileYoull be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers. You will also receive: My favorite tips and tricks direct to your inbox Exclusive content (which is only available to subscribers) FREE tools and downloads If youre already a subscriber, click here to log-in to the subscriber downloads area. Use Goal Seek In Excel Zip Contents TheThe filename for this post is 0022 Automated Goal Seek.zip Contents The Scenario Manual Goal Seek One-click Goal Seek Record the macro Looking at the recorded code Assign the macro to a button Using Named Ranges Adding an error check Automated Goal Seek Conclusion The Scenario The Goal Seek feature determines the input required to achieve a specific outcome. Based on the sales units, sales price, cost price and fixed costs, we calculate a profit. The grey cells are the variables, if any of the variables change, the final profit changes. Using the numbers above, lets assume we want to find out how many units we would need to sell to break-even. Click Data What If Analysis - Goal Seek to open the Goal Seek tool In the Goal Seek window, set the following parameters: Set cell: E12 To value: 0 By changing cell: C4 Click OK to run the Goal Seek. If youre following along with the example file, you will see that we need to sell 571 units to break-even. If one of the variables were to change (e.g., if the sales price increased to 110), then we would need to rerun the same Goal Seek process. Its quite a time-consuming process, plus if you are designing the spreadsheet for somebody else, then its not great user experience. One-click Goal Seek Next, we will create a one-click solution using a macro and attached to a button. Then, anytime the variables change, it will just take one click to re-run the Goal Seek. Record the macro In this scenario, the Macro Recorder will be a solid starting point. Click Developer - Record Macro In the Record Macro dialog, accept the default option by clicking OK. Undertake the same steps as the manual version we used above.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |