Goal Seek working intermittantly

[soqrbrad]

I am having quite some issues with Goal Seek and I'm wondering if it's just an Excel bug or what.

I am using a spreadsheet to perform iterative engineering calculations where you guess one variable and check to see if you get the answer you want. If you don't you then use goal seek to adjust your guess to get the answer you need. Goal seek has been working fine for this up until recently. I'm not sure if it's an office 365 bug but here is my problem.

I go through the goal seek process and put my guess reasonably close to the intended number. I then use goal seek to iterate and find a solution. Some times it works and goal seek will adjust my guess which results in my answer matching and I can move on to the next one. Other times it will say no iterative solution is found and will overwrite the formula I am trying to goal seek with my guess. The strangest one though is where it says it finds a solution but it doesn't change the right variable it just overwrites the formula I am trying to solve. For example:

Say I have the following setup (to simplify the engineering). I have a guess (in the Guess row) that goes through a formula (In the answer Cell Formula row) and returns an answer (the Answer Field. The value I want is in the desired result field.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Guess[/TD]
[TD]Answer Cell Formula[/TD]
[TD]Answer[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]Initial [/TD]
[TD]22.00[/TD]
[TD](complicated formula)[/TD]
[TD]-0.0354[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Goal Seek Works[/TD]
[TD]22.32[/TD]
[TD](complicated formula[/TD]
[TD]0.000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Goal Seek Error[/TD]
[TD]22.00[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Basically when I run goal seek, it doesn't run the iteration or change the guess at all. It just inserts the required value (0) into the formula and erases what is in there. It says a solution was found but then puts in 0 to overwrite the formula. I am making very sure I select my guess field as the variable I want to change.

Is this a known bug or is there something I can do to make this work more consistently?

 

[kevinfeldman]

I have the same issue. For the record I am using Excel 2016 version 16.0.6001.1078, 32-bit.

Does anyone have a solution?

Thanks.

 

[serivas]

I have the same problem! Basically it changes the las cell I clicked, rather than the cell I'm telling it to change. So it will only work if first I click the variable cell, and then I open Goal Seek