Goal Seek Overwriting Set Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2757827%22%20slang%3D%22en-US%22%3EGoal%20Seek%20Overwriting%20Set%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757827%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22pf0%22%3E%3CSPAN%20class%3D%22cf0%22%3EI%20am%20using%20Microsoft%C2%AE%20Excel%C2%AE%20for%20Microsoft%20365%20MSO%20(16.0.14326.20324)%2064-bit.%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%3CSPAN%20class%3D%22cf0%22%3EWhen%20I%20use%20goal-seek%2C%20it%20overwrites%20the%20cell%20containing%20the%20formula%20with%20a%20value%20and%20does%20not%20give%20me%20the%20correct%20answer.%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%3CSPAN%20class%3D%22cf0%22%3EFor%20example%2C%20I%20made%20the%20following%20tables%20(formulas%20listed)%20and%20then%20used%20goal%20seek%20where%20Set%20Cell%20%3D%20B1%2C%20To%20value%20%3D%20100%2C%20%26amp%3B%20by%20changing%20cell%20%3D%20%24B%245.%20(My%20calculation%20options%20are%20in%20the%20attached%20screenshot.)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2250%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3E%3CSTRONG%3EA%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3EFormula%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E%3Dsum(B2%3AB6)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3EValue%201%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3EValue%202%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3E4%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3EValue%203%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E30%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3E5%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2268.5781px%22%3EValue%204%3C%2FTD%3E%3CTD%20width%3D%22100.297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20goal%20seek%2C%20the%20formula%20in%20B1%20was%20replaced%20with%20a%20value%20of%200%20and%20cell%20B5%20was%20set%20to%200.01.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20already%20tried%20uninstalling%20and%20reinstalling%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20if%20this%20is%20a%20user%20error%20or%20something%20else%3F%20Do%20you%20know%20what%20I%20need%20to%20change%20to%20make%20goal%20seek%20work%3F%3C%2FP%3E%3CP%3E%3C!--%20%20%20%20EndFragment%20%20%20%20%20--%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2757827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2757971%22%20slang%3D%22en-US%22%3ERe%3A%20Goal%20Seek%20Overwriting%20Set%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158246%22%20target%3D%22_blank%22%3E%40KVPSFA%3C%2FA%3E%26nbsp%3B%20I%20cannot%20duplicate%20the%20problem%20with%20that%20example.%26nbsp%3B%20Not%20too%20surprising%2C%20since%20I%20use%20Excel%202010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20have%20stumbled%20onto%20an%20Office%20365%20Excel%20defect%20that%20seems%20to%20occur%20from%20time%20to%20time.%26nbsp%3B%20A%20google%20search%20turns%20up%20similar%20questions%20as%20far%20back%20as%202017.%26nbsp%3B%20I%20don't%20find%20any%20threads%20with%20a%20dispositive%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20case%2C%20it%20would%20behoove%20you%20to%20attach%20an%20actual%20Excel%20file%20that%20demonstrates%20the%20problem%20(for%20you).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20one%20thing%20to%20do%3A%26nbsp%3B%20Be%20sure%20there%20are%20no%20circular%20references.%26nbsp%3B%20Click%20Formulas%20%26gt%3B%20Formula%20Auditing%20%26gt%3B%20Error%20Checking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20imagine%20how%20that%20might%20explain%20the%20misbehavior%20that%20you%20report.%26nbsp%3B%20But%20when%20Excel%20encounters%20CR%20errors%2C%20%22weird%20things%22%20have%20been%20known%20to%20happen%2C%20even%20in%20unrelated%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758276%22%20slang%3D%22en-US%22%3ERe%3A%20Goal%20Seek%20Overwriting%20Set%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20saw%20that%20I'm%20not%20the%20only%20person%20with%20this%20error%2C%20but%20I'm%20still%20a%20little%20frustrated%20with%20the%20problem.%20Hopefully%20someone%20at%20Microsoft%20can%20weigh%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20meantime%2C%20I%20made%20a%20new%20file%2C%20which%20is%20attached%2C%20where%20I%20recreated%20the%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Before%20Goal%20Seek.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310877iC65CF81C306C75D3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Before%20Goal%20Seek.PNG%22%20alt%3D%22Before%20Goal%20Seek.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Goal%20Seek%20Processing.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310878i2A67A72F29A18CAE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Goal%20Seek%20Processing.PNG%22%20alt%3D%22Goal%20Seek%20Processing.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22After%20Goal%20Seek.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310879iF5F722059A44DE34%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22After%20Goal%20Seek.PNG%22%20alt%3D%22After%20Goal%20Seek.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758348%22%20slang%3D%22en-US%22%3ERe%3A%20Goal%20Seek%20Overwriting%20Set%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158246%22%20target%3D%22_blank%22%3E%40KVPSFA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20have%20Microsoft%20365%20but%20Office%202019%20Click-to-Run.%20I%20restored%20the%20formula%20in%20B1%2C%20then%20ran%20Goal%20Seek.%20It%20worked%20as%20intended.%20Apparently%20Excel%20in%20Office%202019%20(which%20shares%20most%20of%20its%20code%20with%20Excel%20in%20Microsoft%20365)%20doesn't%20have%20the%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.)

 

 AB
1Formula=sum(B2:B6)
2Value 110
3Value 220
4Value 330
5Value 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?

6 Replies

@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.

@Joe User 

 

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.

 

Before Goal Seek.PNG

Goal Seek Processing.PNG

After Goal Seek.PNG

  

 

@KVPSFA 

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.

@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)

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.