Results 1 to 6 of 6

Thread: Goal seek
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Goal seek

    Hi guys,

    Im looking for a sub goalseek that involves a loop making U8:U207 = 0 by changing the values in b2:q12

    thank you

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    7,969
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Goal seek

    Welcome to the Board.

    Sounds like you need the built-in Solver tool. Check out this link:

    https://www.ablebits.com/office-addi...with-examples/


    It is possible to write a custom VBA routine to do that, but it would require a lot more details on what you want.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Goal seek

    No sorry , cause with the solver tool I just can set one objective cell and my objective is to change every cell from u8 to u207

    Thank you anyways

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    7,969
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Goal seek

    The Solver tool sets one cell (the objective) to a given value (or minimum/maximum) by changing all of the parameter cells. You can set B2:Q12 to be those parameter cells. You can set A1 to be the objective cell with a formula of =SUM(U8:U207) and have the Solver set that to 0. You may need to add additional parameters to make sure that U8:U207 are non-negative.

    With the information given, that's the best suggestion I can give. If you give more details about what you want to accomplish, I might have better suggestions.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Goal seek

    Quote Originally Posted by Eric W View Post
    The Solver tool sets one cell (the objective) to a given value (or minimum/maximum) by changing all of the parameter cells. You can set B2:Q12 to be those parameter cells. You can set A1 to be the objective cell with a formula of =SUM(U8:U207) and have the Solver set that to 0. You may need to add additional parameters to make sure that U8:U207 are non-negative.

    With the information given, that's the best suggestion I can give. If you give more details about what you want to accomplish, I might have better suggestions.

    https://imgur.com/a/tvZbGoK

    I want to do a perfect match between assets and liabilities, assets values(i have 16 assets) are in b2:q12 , I have to make either u8:207 or v8:207 to be 0(those are net value and cumulative net value) which means that Assets - liabilities are 0 in every date .

    thank you

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    7,969
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Goal seek

    I'm a bit lost on what you want. First, I think you have a persistent typo on the asset values, do you mean B2:Q2 (NOT B2:Q12)? Next, those values are all 1000 on your screen print. You want to change those values to something else, which I assume will affect the values in the column below, and that those values are rolled up into the values in column R (assets)? What formulas exist in B8:Q207? What formulas are in R8:R207? How are the liability values in T8:T207 created? Depending on what you really want, it's possible that it's impossible to do. It looks like you're trying to solve a system of 200 equations with 16 unknowns. Which, unless the stars align perfectly, will usually result in inconsistent results. In which case you could set Solver to minimize the sum of differences.
    Last edited by Eric W; May 20th, 2019 at 05:40 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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