Forum Discussion
Goal Seek Overwriting Set Cell
I am using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20324) 64-bit.
When I use goal-seek, it overwrites the cell containing the formula with a value and does not give me the correct answer.
For example, I made the following tables (formulas listed) and then used goal seek where Set Cell = B1, To value = 100, & by changing cell = $B$5. (My calculation options are in the attached screenshot.)
| A | B | |
| 1 | Formula | =sum(B2:B6) |
| 2 | Value 1 | 10 |
| 3 | Value 2 | 20 |
| 4 | Value 3 | 30 |
| 5 | Value 4 |
After goal seek, the formula in B1 was replaced with a value of 0 and cell B5 was set to 0.01.
I have already tried uninstalling and reinstalling Excel.
Do you know if this is a user error or something else? Do you know what I need to change to make goal seek work?
- The error appears to be with MorningStar Office's add-in. I disabled it and it worked just fine.
6 Replies
- The error appears to be with Morningstar Office's add-in. I disabled it and it worked just fine.
- The error appears to be with MorningStar Office's add-in. I disabled it and it worked just fine.
KVPSFA I cannot duplicate the problem with that example. Not too surprising, since I use Excel 2010.
You might have stumbled onto an Office 365 Excel defect that seems to occur from time to time. A google search turns up similar questions as far back as 2017. I don't find any threads with a dispositive explanation.
In any case, it would behoove you to attach an actual Excel file that demonstrates the problem (for you).
And one thing to do: Be sure there are no circular references. Click Formulas > Formula Auditing > Error Checking.
I cannot imagine how that might explain the misbehavior that you report. But when Excel encounters CR errors, "weird things" have been known to happen, even in unrelated cells.
I saw that I'm not the only person with this error, but I'm still a little frustrated with the problem. Hopefully someone at Microsoft can weigh in.
In the meantime, I made a new file, which is attached, where I recreated the issue.
KVPSFA I have MS365 on both PC and Mac. No problem on either of these.
PC version: 2110 (14.509.20000), Beta
Mac version: 1654 (21091405)
