Goal Seek Bug

[AddisonScott]

Goal Seek is not working correctly for me for some reason.

For example, take the three cells: A1, B1, C1.

C1 = A1 + B1

A1 = 1

Now, I start Goal Seek and use the following information:

Set: C1

To value: 8

By changing: B1

Instead of Goal Seek changing B1 to 7 (1 + 7 = 8), I get the following:

A1 = 1 (good, nothing changed)

B1 = 2.02 (this is a problem)

C1 = 8 (this would be OK, except that it is manually typed in!, my formula of C1 = A1 + B1 is gone!)

I checked that I have "Enable iterative calculation" turned on and my "Maximum Change" is set to 0.001.

I'd appreciate any insight into fixing this problem.

Thanks!

Below is a link to a GIF of the problem.

https://drive.google.com/file/d/1qBf_VsJOIvutb5p4EIcSdv6kUt7AsZYp/view?usp=sharing

 

[AddisonScott]

I forgot to mention. I tried the same thing on a coworker's computer and he also could not get Goal Seek to work.

 

[joeu2004]

C1 = 8 (this would be OK, except that it is manually typed in!, my formula of C1 = A1 + B1 is gone!)

Works fine for me with Excel 2010.

But in other forums, people have a reported a similar problem (the Set Cell formula is replaced) using Excel 2016 -- I think the version in subscription Office 365, not the standalone Excel 2016.

I checked that I have "Enable iterative calculation" turned on

Not a good idea, if you don't need it. (And you almost never do.) Setting this option disables the circular reference check, which is much more useful, in general, than relying on circular reference by design or increasing "what if" iterations.

 

[AddisonScott]

Thanks joeu2004.

Yes, I will turn off iterative calculations. I only turned it on because several other help pages I found suggested the problem was with that or with the Maximum Change.

The version of Excel I'm using is Microsoft Office Professional Plus 2013.

 

[joeu2004]

Not an answer. But for moral support, you might look at https://answers.microsoft.com/en-us...cel-2013/9f6fba3b-253c-4a80-9b55-40a0b694022c. Unfortunately, I don't see any resolution there.

I googled "excel 2013 goal seek replaces formula" without quotes. I suggest that you do the same and see if you can find an acceptable resolution.

 

[joeu2004]

I will turn off iterative calculations. I only turned it on because several other help pages I found suggested the problem was with that or with the Maximum Change.

Obviously, not __this__ problem (__replacing__ the target formula). Increasing the number of iterations and the precision of max change might improve the approximate result. But so does clicking Goal Seek two or more times without changing the result from the previous Goal Seek. Not worth the risk of ignoring circular references, IMHO.