I am trying to automate a goal seek function. The attached spreadsheet should be self explanatory for the issue. THANKS for your help.
I am trying to automate a goal seek function. The attached spreadsheet should be self explanatory for the issue. THANKS for your help.
One addition, I only experimented with the first 5 or so cells in column Q. When I couldn't get that to work, I stopped and asked for help.
I'm not exactly sure I understand what you are doing. I focused on this from your spreadsheet:Q9 contains an IF(...) formula that chooses between 500 and another formula, depending on the year. As the error message from Goal Seek states, this is definitely a formula and not a constant, stand-alone value. An IF() formula that chooses between a constant and some other formula is still a formula.I can't figure out how to automate Goal Seek ("set S38 to 0 by change in cell Q9). While I can put a value into cell Q9 and run Goal Seek, when I let my formula put in a number, Goal Seek says Q9 must have value. But, I put in a value with my formula;
What you need is to put the 500 in a cell all by itself, then have Goal Seek change that cell. What I did was:
1) Enter 500 into a convenient cell (I chose O2)
2) Edit the IF() functions in Q8:Q12 so that they refer to $O$2 (note the absolute reference) instead of the value 500.
3) Call Goal Seek and tell it to set S38 to a value of 0 by changing O2.
Goal Seek seemed to have no trouble finding a solution from there.
Did I understand correctly, or is there more to the question?
Originally Posted by shg
Thanks. Yes, since I posted I did figure our your solutions 1 & 2 along with a few other changes. However, I'm not sure how to "call" Goal Seek. Is that a macro or ?? THANKS for your help.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. As you are new, I have done it for you this time. Please read our rules.)
https://chandoo.org/forum/threads/au...al-seek.43096/
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
This help file should explain how to use Goal Seek: https://support.office.com/en-us/art...3-4f369d6e66c7
Sorry, I didn't know that posting to unrelated forums was against the rules.
Here are the rules to which you said you agreed upon joining (as it's probably time you read them): https://www.excelforum.com/forum-rul...rum-rules.html
THANKS for your help.
I did review the document link and I already knew how to do a Goal Seek. What I need to accomplish is to automate the Goal Seek anytime variables change. In my worksheet, I have inputs on the Summary tab. If I change an input there, I need to have the Goal Seek rerun automatically. Unfortunately, this also necessitates finding the last value in the column in order to rerun the Goal Seek. This is a little over my head. Any help is GREATLY appreciated.
Which part is over your head? Someone over on the chandoo forum gave you an example VBA statement that will call goal seek, assuming that you would be able to provide the rest of the VBA context for that statement to run in. If you are unfamiliar with VBA in general, then maybe you want some generic VBA coaching or some examples of calling goal seek in VBA. Here's one example:
https://www.excelforum.com/excel-pro...-function.html
I often prefer Solver over Goal Seek, the algorithms seem more robust. If you want to use Solver here are some places to start:
https://peltiertech.com/Excel/SolverVBA.html
https://www.excelforum.com/excel-for...tool-pack.html
I would also mention that the spreadsheet looks like an amortization schedule, and I would not be surprised if it will reduce to a PV, NPV, or XNPV type problem. I don't know the financial models well enough to say how they might apply. If you understand the different financial models, the best approach might be to use Excel's built in functions to perform the amortization.
Thanks to all for your help. I did get my worksheet where I needed it. It may be a little kludgy, but it works. Also, I didn't realize I couldn't query unrelated forums, now I do. My apologies.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks