+ Reply to Thread
Results 1 to 12 of 12

Automate Goal Seek

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Automate Goal Seek

    I am trying to automate a goal seek function. The attached spreadsheet should be self explanatory for the issue. THANKS for your help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    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.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,331

    Re: Automate Goal Seek

    I'm not exactly sure I understand what you are doing. I focused on this from your spreadsheet:
    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;
    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.

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    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.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Automate Goal Seek

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,331

    Re: Automate Goal Seek

    This help file should explain how to use Goal Seek: https://support.office.com/en-us/art...3-4f369d6e66c7

  7. #7
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    Sorry, I didn't know that posting to unrelated forums was against the rules.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Automate Goal Seek

    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

  9. #9
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    THANKS for your help.

  10. #10
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    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.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,331

    Re: Automate Goal Seek

    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.

  12. #12
    Registered User
    Join Date
    05-15-2019
    Location
    Lake Forest, CA
    MS-Off Ver
    10 Pro
    Posts
    16

    Re: Automate Goal Seek

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Use VBA to automate goal seek (involving a cell in another worksheet)
    By chayanja in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-29-2019, 08:59 AM
  2. Automate Goal Seek Function
    By korny88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-20-2016, 11:27 AM
  3. Trying to automate Goal Seek Function
    By mjrubin23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2016, 10:13 AM
  4. Automate What IF Analysis (Goal Seek)
    By Savan87 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-13-2011, 01:03 PM
  5. Automate Goal Seek in VBA??
    By shaun2985 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2009, 09:17 AM
  6. Automate Goal Seek
    By marcghannoum@gm in forum Excel General
    Replies: 6
    Last Post: 01-26-2009, 02:47 PM
  7. Automate Goal Seek?
    By John Richards in forum Excel General
    Replies: 2
    Last Post: 04-23-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1