Workbooks contain worksheets. Worksheets contain ranges. But what is Goal Seek contained in??? If I don’t know then the macro recorder is the best tool 👍
This is valuable example of some quite useful code. If you're in any type of planning effort as (a part of) your job, this is a must-have tool. Thanks Mark!
I learnt a lot from this video. Wonder if it is possible to further enhance the macro by sensitizing the Target Value (which is currently the "Profit", say, from 0, 10000, 20000, to 50000) and return to a their corresponding result (which is currently the SalesUnits)? It is troublesome to change the Target Value cell one by one but a the general Goalseek function does not work because the current automatic is generated by Macro.
Amazing tip. I am trying to take it another step forward as I want to use the automated goal seek approach for one row of data. It then should do the same thing for the next row (with respective data) and the next and so on.... Do I have to write a new Macro for each row or is their an easier way copy the Macro for the next row?
Hi Mark, This is at an altogether different level! I was introduced to your excel skills and genius when I attended your presentation on the Named Ranges in the MS Excel Toronto Meetup session. I have a question though. How can I automate this if I want to come up with a budget for the full year by months and want Goal Seek to project the COGS considering I have target Gross Margin % by month? Thanks, Rajiv
Hi Rajiv - Thanks for your kind words. If you have the GM% already then it should be possible to reverse calculate the COGS without needing Goal Seek because you only 1 one variable (which is the Turnover). COGS = Turnover x (1 - GM%) Or have I missed the point?
@@ExcelOffTheGrid Hi Mark, Apologies for the confusion. Let me explain in a detailed manner. I have a Budget Model Summary worksheet wherein the numbers for the different KPIs' are linked from the various worksheets in the workbook. I do not want any manual intervention in the Budget Summary worksheet. As you rightly mentioned, my formula for GM% is (Sales - COGS) / Sales with COGS being the unknown or missing variable. Currently the way I get this is by calculating it another worksheet by bringing in the monthly sales in columns , manually inputting the target GM% by month. This will give me the Gross Margin amount by month (Sales*GM%). COGS will now be a delta between Sales and Gross Margin. The COGS that's now calculated will be linked to the Budget Summary worksheet. I know this simulation method also works like a crude way of Goal Seek. I understand it's sometimes difficult to visualize the requirements this way. Please let me know if you want me to share an example file. Alternatively, how would you automate this if in your example, you had to arrive at monthly numbers? Thanks, Rajiv
Hi Rajiv - To implement this over multiple periods you would need the macro to loop through the 12 periods, using a For loop, and iterating over the cells one period at at time, which would run the goal seek each time. As you're running it 12 times, it might get a little bit slow. You would need to try it out and see if the speed is OK.
Hi Mark, Thanks for this video - it's exactly what I've been looking for! I tired the VBA method but I hit a bit of a problem, possibly because my variables are on a separate sheet within my workbook. Is there a way of getting round this that you could suggest? Thanks in advance!
Good question. This issue is caused by the code being enclosed within the sheet module. Here is how to change it: Create a macro in an standard module which executes the Goal Seek: Sub RunGoalSeek() Range(Range("SetCell").Value).GoalSeek Goal:=Range("TargetValue").Value, _ ChangingCell:=Range(Range("ChangeCell").Value) End Sub Then in the Worksheet_Change macro replace this: Range(Range("SetCell").Value).GoalSeek Goal:=Range("TargetValue").Value, _ ChangingCell:=Range(Range("ChangeCell").Value) With this: Call RunGoalSeek Hope that helps :-)
Awesome work as usual Mark. Thank you so much. I'm gonna use this in a file that we currently have to use Goal seek a few times before we get the desired results. I especially like that the macro runs with a change event. Thanks again and keep sharing!
Mark, you're an absolute legend! 🏆 You've just guided me through the final steps I needed in order to finish building a tool I've been working on most of the day. The tool is to be used to work through over 100 seperate cases, with more likely to come, which each take 15 minutes to complete, at best. This has changed my individual case time down to a little under two minutes. That's a massive saving of over 22 hours on just this current batch!!! 😲 Add one to the subscriber list, matey. If it wasn't for the current pandemic I could kiss you! 😘
@Md. Shazzad Kabir Not sure how good a teacher I am, but I can give it a try. It's a very versatile tool, but not always the best one for the job. What exactly are you trying to achieve? Are you looking to run multiple goal-seeks from the same data set, or are there different data sets contained in your single sheet and you're looking to run a goal-seek on each one but have them displayed in a single sheet for ease of reference? Anything you can tell me about the data would help me to guide you, but I understand if you can't give specifics if it's sensitive stuff (that's why my comment was purposely vague). If you can't give specifics then broad hypotheticals will do.
Is there a limit on the number of input cells I can add in VBA? or would I need to add a second row? I'm not sure how i can add more than 5 input cells . Thankyou!
The input cells are all named ranges. So you need to create more named ranges to include more parameters. If you start creating a lot there could be performance issues. I’ve never tried it with big number. Work through the example and it should become clear.
So I just filled out the formula you were showing for finding SUM from multiple tabs. But it just keeps saying #NAME?. This is how I typed it. =Sum(Jan:Dec!E165)
If you want multiple criteria to find the optimal solution, then Solver might be a better option. It's an add-in already in Excel, you just need to enable it.
Hi Mark Thanks For the video. I have a question. is there any mean to include formula in the target value cell, so the VBA for goal seek run once the value of the formula in the target value cell changes?
I could not figure out for the longest time how to make a Goal Seek Macro work where the "target value" was referenced from a specific cell rather than a number that is used over and over again. I cannot believe the workaround was as simple as naming the cell... Thank you! Looking forward to watching more videos.
Question for you: Why do you need to reference the named ranges in the spreadsheet? Can't you just point the macro to them directly? And thank you very much for posting this video. It was super helpful!
I use the named ranges in the worksheet so that it can be flexible with which items are used in the goal seek calculation. If you always want to change the same items then you can just point the macro to them as you suggest.
Mark - Fantastic. Thank you for sharing. Subbed. ❤ I want to apply the learning shared by you in my business problem. I have about 20 rows and each rows requires a goal seek solution. As mentioned by you, I can loop through those 20 rows, using your Macro. Currently, I do a manual interactive Goal seek method in Excel to find goal seek solution for each row. But before, I can use your Macro, I have 3 problems, one that I would want Excel to be SAVED after each row has found the GoalSeek solution, secondly I want to limit GoalSeek to say a maximum of 30 iterations, and thirdly save the value of 30th Iteration. This is because my problem isn’t a linear one and sometimes GoalSeek can’t find a solution, and GoalSeek may continue forever. So I want to restrict GoalSeek to a maximum of 30 iteration. If GoalSeek can’t find a solution even after 30 iterations, I would want to see the last optimised value so that I can manually optimise it later.
Just now created cash in hand to gross pay calculator with the help of this vid. It never crossed my mind to combine goal seek & macro. Thanks a lot. Subbed!
Yes it can. You just need to write your macro so that the first one calculates, and then the next one calculates. Though at some point it becomes too complex to be useful.
Hey Mark, how would I make it work automatically if the target value is the value in cell that gets updated as the last value in a column as it changes? It seems to only work if I throw in a bogus value into the "change cell" and then press enter and it corrects itself
I thought target value can’t be a formula, because if I use the interactive method of GoalSeek when target value is a formula, Excel refuses to GoalSeek saying that TargetValue cannot be a formula.
Thanks for this video at @excel off the grid, i have a long and so many payments to make via payroll but i have the net amounts and i want to use Goal Seek to help me find the gross amounts for all the 3000+ employees, how can i go about it for multiple selections?
Depending on your local tax legislation, it may not be possible to do it. In the UK for example there are tax codes, gift-aid, pensions, car allowances, etc which all affect the tax calculation. Two people can have the same net pay, but different gross pay.
@@ExcelOffTheGrid well, it is possible and currently we have used it on so many occasions as we have been using it as well, I want to ask in a nutshell, is there a way one can automate goal seek for multiple choices for numbers coming to 100 for instance. If I can have your email I can further discussion or get a script of macro running the multiple choices.
All the website and RU-vid support files are in the Newsletter Subscriber area (it helps if I keep everything in one place). Click the link, and enter your details. The file 0022 Automated Goal Seek.zip is currently towards the bottom of the page.
After you give your name and e-mail you get to the subscriber downloads page. Scroll down and find the download link using the reference 0022 Automated Goal Seek.zip
