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.
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.
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!
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!
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.
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.
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, 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 :-)
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.
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?
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.
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.
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)
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.
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.
All the website and UA-cam 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.
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.
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.
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
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.
Thanks Tony, glad I could help.
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! 😘
Thanks John, welcome aboard.
A saving of 22 hours!!!! That's amazing - I'm glad I could help :-)
@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.
Just what I was looking for as I had no idea how to do it and worked perfectly for me.
Yay!! Great news.
this is awesome man! this has been the only one I was able to comprehend this function! good job and I'm officially your fan now!
Awesome, thank you! 😁
Hi Mark, your videos do helped me a lot in the last couple of month, please continue to show us more 💪💪
Thanks Dietmar - I’m trying to post weekly at the moment, so definitely more coming soon.
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!
Will do, thanks Peggy 😃
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!
Woop woop!! Great work. Glad I could help you with that 👍
Awesome approach Mark, I'm impressed you used the macro recorder at the beginning of all of this. 😁👌
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 👍
You know right?!
You are the best teacher. Thank you a lot.
This was good and simply explained.
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!
Thanks for watching, I’m glad it was useful.
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.
Very Nicely Done!
Thank you :-)
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.
Sounds like a great project.
That will be quite a lot if code, but definitely doable.
@@ExcelOffTheGrid Thank you Mark. Just receiving your reply and hearing you gives me enough confidence.
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?
In that scenario you would loop through the cells. So, it’s one goal seek macro applied multiple times.
Mark - Sorry, maybe it is too much to ask, would be great to see the Loop code from you. 😌
Great video, this will same me soooo much time. Can you have one button run more than one goal seek? I cant figure it out..
You can. I would recommend looping through based on a list of the parameters that you wish to use.
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 :-)
@@ExcelOffTheGrid That worked perfectly! Thanks so much for your help and your video, it's been a lifesaver!
great video, but at the end, the only dynamic cell is the Units Sale right? What I f I want also "play" with the price and costs?
Goal seek can only have a single variable. I think you need to look into the Solver Add-in if you want more.
This isn't possible to set like Profit for example to a number in cell B2 right? It has to be a fixed number if I'm not mistaking?
If you want multiple criteria, then the Solver add-in might be the best option.
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.
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?
If I understand you question correctly, the value being changed needs to be a value which is not formula driven. Otherwise Goal Seek can't change it.
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.
@@ExcelOffTheGrid Thanks Mark
Thank you for the Great Vid, can this VBA method be applied to multiple cells different rows (Same Goal) Thanks
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.
@@ExcelOffTheGrid Thanks a lot :)
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)
You need single quote marks around the page names.
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
Just figured it out, I had to name the column! Thanks heaps for your video!
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.
Can the automated goal seek code reference from another goal seek, or does the cell have to be active with a manual input?
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.
Hello Mark. Great video, but I was not able to find the example file when clicking on the link. Can you help please. Thank you.
All the website and UA-cam 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.
@@ExcelOffTheGrid Thank you very much!
can you make a version of cells instead of range?
Are you referring to the VBA part? If so, yes you can use the cells property to define the location of the parameter.
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.
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.
wowow
How to download the file sir ? it's really confused after download 100 code and subscribe but no one is function as right
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