Goal seek

[MrGarciaZ]

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

 

[Eric W]

Welcome to the Board.

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

https://www.ablebits.com/office-addins-blog/2016/06/22/how-to-use-solver-in-excel-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.

 

[MrGarciaZ]

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

 

[Eric W]

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.

 

[MrGarciaZ]

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

 

[Eric W]

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.