Issue with Goal Seek suddenly not working - June 6, 2021

[u144175]

I am suddenly having an issue with Goal Seek not working correctly.  I have successfully used it many, many times in the past and cannot begin to figure out what has happened.  The issue first came to light within a spreadsheet much to complicated to describe here but I created a new workbook with just this limited data to illustrate the issue.  Cell A1 = 1.  Cell A2 = 2.  Cell A3 = 8.  Cell A4 = Average(A1:A3), which correctly shows as 3.66667.  Now, I run Goal Seek and tell it to set cell A4 to a value of 10 by changing cell A2.  Rather than Goal Seek iterating on cell A2 to determine the value of 21 necessary in that cell to achieve a value of 10 in cell A4, it iterates cell A4 and eventually "finds" a solution of 10 in A4.  So, it has 1.) iterated on the wrong cell and 2.) replaced my =average(A1:A3) formula in cell A4 with a value of 10.  What is going on?

I am attaching screen grabs of the before Goal Seek, the Goal Seek setup, and the after Goal Seek.  (Note, in the 'after' screen grab, A4 still shows to be a formula equal to =Average(A1:A3) but that is just because I had not closed the Goal Seek window yet.  Once that is done, the formula is replaced by the number 10.  The replacement occurs even if I click Cancel which would normally forget the Goal Seek results and revert the cell that was iterated on back to its original value.)

I am using Microsoft Office Professional Plus 2019, Excel version 2105, Build 14026.20246.

Thank you in advance for any assistance on this very odd issue that is driving me crazy.

Brian

[#2]

Sure looks strange. I cannot replicate the problem with Microsoft 365 Apps. Goal Seek changes A2 to 21 and A4 still contains the AVERAGE formula with a calculated value of 10.

[#3]

iDon't see anything wrong with it! You want the average to be 10 by changing the cell A2 - The value in Cell A2 Is 21 and then the avegrage of 1+ 12 + 8 =10 which is exactly what Goal seek does if you use shortcut ALT D W G

[#4]

@Donald_Genes  ....  This is an old thread, so I don't know why you are responding -- and with an unconstructive comment, at that ("I don't see anything wrong with it").

But in another thread, a user claimed to trace the root cause to a particular add-in.  He wrote:  ``The error appears to be with MorningStar Office's add-in. I disabled it and it worked just fine.``

Unfortunately, I did not record the link to the thread.  If I find it again, I will amend this response.

And I don't know if that is the only add-in that causes this problem.  I'd be surprised if it were, because we see this problem posted "often".

[#5]

@Joe User...thank you for the response.  I have no add-ins loaded so that is not the issue.  Not sure what is going on as the issue now seems to come and go, but always manifests in the same way.

[#6]

@u144175  wrote: ``I have no add-ins loaded so that is not the issue``

Well, that's too bad.  Since we have never been able to duplicate the problem, I had hoped it was that simple.

Also, it is too bad that the problem is intermittent for you.  That means that if we suggest an experiment and the problem "goes away", we cannot say with impunity that it worked around the problem.  The problem might return.

When you say that it "comes and goes", do you mean with the same data and formulas?  Or with different data and/or formulas? 

If the problem was consistent with the example in your original posting, and now it "comes and goes" with __that__ example, do you have any idea what changed?

And how often do you get bit by the problem?  Hopefully often enough that if we suggest an experiment and the problem goes away "long enough" -- comes back "soon enough" after stopping the experiment, we can say with some confidence that the experiment points to the root cause?

Have you ever seen the problem using Solver instead of Goal Seek?  If not, is there any reason not to use Solver instead?

(It would be nice to understand the root cause of the problem.  But I suspect that is not __your__ primary interest.)

Finally, if you have an example that misbehaves in this way consistently or "most of the time", you might try starting Excel in safe mode and see if the problem "never" appears.  Close all windows (*).  Press __and_hold__ the Ctrl key, then click an Excel __program__ icon (not a file icon).  You should get a yes-or-no prompt to continue in safe mode.  Click yes.  In Excel, click on Open to open the Excel file in safe mode.

-----

(*) Close all windows because otherwise, we cannot see the yes/no safe mode prompt. (sigh)

[#7]

It comes and go with different data / workbooks. When I first experienced the issue, it was within a large workbook I use virtually every day. That's when I created the simple example sheet I provided in my original post above to illustrate the situation. A week or two later, the issue went away and goal seek was working correctly again. Everything seemed ok until about a week ago and then I once again saw the same behavior, this time in a workbook that I only use occasionally (about once a month). Different workbook but same incorrect goal seek behavior. I did not attempt to use solver instead of goal seek. Goal seek is presently working again so any troubleshooting is moot right now however I will try solver and safe mode if/when I again see the aberrant goal seek behavior. Thanks for the suggestions on this very odd, intermittent issue. I do appreciate the help.

[#8]

@u144175  wrote:  ``then I once again saw the same behavior, this time in a workbook that I only use occasionally (about once a month)``

Rats!  Check astronomical tables for phases of the moon, alignment of planets, position of stars in zodiacal signs, solar flares, etc.  Just kidding!

I usually find that "intermittent" problems are not as random as they might appear.  Eventually, we recognize a pattern that points to a deterministic explanation.

Good luck!

PS.... One other variable to consider: automatic updates of Office 2019?  I disable all automatic updates whenever possible.  But IIRC, that is not possible with Office 365 -- which is why I avoid it like the plague.  I don't know about Office 2019.  I think MSFT has changed the notion of "standalone" software nowadays.  (sigh)

[#9]

@u144175 do you by any chance use Oracle Content (the Oracle version of Dropbox)? I work for Oracle and find that the Excel add-in for Oracle Content causes the same problem with Excel Goal Seek. It's really annoying.

It could also be another add-in.

1. Go to File->Options->Add-ins
2. Look at the "Active Application Add-ins"
3. You can then select "COM Add-ins" at the bottom of the dialog and to manage the addins and remove them. (you need to run Excel as admin)

Removing the Oracle Content add-in fixed the problem for me, but it does get added back in when the software updates.

Hope this helps somehow,.

[#10]

@hotchilli66thanks for the advice however I have no addins active.