Forum Discussion
Issue with Goal Seek suddenly not working - June 6, 2021
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
9 Replies
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.
- Go to File->Options->Add-ins
- Look at the "Active Application Add-ins"
- 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,.
hotchilli66thanks for the advice however I have no addins active.
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".
JoeUser2004...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.
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)
- 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
- 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.
