Sep 16 2021 12:14 PM
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?
Sep 16 2021 12:51 PM
@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.
Sep 16 2021 02:31 PM
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.
Sep 16 2021 02:48 PM
I don't have Microsoft 365 but Office 2019 Click-to-Run. I restored the formula in B1, then ran Goal Seek. It worked as intended. Apparently Excel in Office 2019 (which shares most of its code with Excel in Microsoft 365) doesn't have the error.
Sep 17 2021 12:16 AM
@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)
Sep 17 2021 07:54 AM
SolutionSep 17 2021 07:55 AM
Sep 17 2021 07:54 AM
Solution