Excel returning incorrect result even though evaluation gives the correct result

%3CLINGO-SUB%20id%3D%22lingo-sub-1344001%22%20slang%3D%22en-US%22%3EExcel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344001%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20spreadsheet%20is%20posting%20an%20incorrect%20result%2C%20Even%20though%20when%20I%20run%20evaluate%20formula%20the%20final%20result%20inside%20its%20popup%20is%20correct%2C%20when%20finished%20a%20different%20result%20is%20posted%20to%20the%20spreadsheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1344001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344085%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3BWould%20be%20helpful%20if%20we%20could%20see%20the%20formula%2C%20the%20expected%20outcome%20and%20the%20result%20it%20produces.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344109%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344109%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20not%20a%20formula%20issue%20as%20this%20is%20happening%20in%20a%20few%20different%20places%20on%20the%20spreadsheet.%20As%20I%20said%20the%20%22evaluate%20formula%22%20returns%20the%20correct%20answer%20as%20its%20final%20result%20but%20the%20spreadsheet%20then%20posts%20a%20different%20result.%20This%20spreadsheet%20and%20all%20its%20formulas%20has%20worked%20fine%20for%20a%20year%20or%20more%20with%20no%20changes.%20Then%20in%20the%20last%20few%20days%20it%20stopped%20working%20correctly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344164%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344164%22%20slang%3D%22en-US%22%3E%3CP%3ETried%20it%20at%20auto%20and%20manual%20and%20the%20error%20recurred.%20I%20reinstalled%20Office365%20and%20for%20now%20it%20seems%20to%20be%20working%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344293%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344293%22%20slang%3D%22en-US%22%3E%3CP%3ESpoke%20too%20soon%20the%20reinstall%20did%20not%20correct%20the%20problem.%3C%2FP%3E%3CP%3EI%20attached%20the%20sheet%20%2C%2C%2C%20look%20at%20cells%26nbsp%3B%20AO7%3AAO16%20for%20example%20though%20it%20occurs%20in%20other%20places%2C%20IN%20particular%20AO11%20and%20AO12.%20Running%20evaluate%20formula%20leads%20to%20a%20different%20result%20which%20is%20correct%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344363%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3BWow%2C%20what%20a%20schedule.%20For%20an%20outsider%20like%20myself%2C%20the%20formulae%20are%20difficult%20to%20grasp%20without%20a%20thorough%20analysis%20of%20the%20entire%20workbook.%20When%20I%20opened%20the%20schedule%20I%20got%20a%20warning%20that%20it%20contained%20circular%20references.%20Not%20a%20good%20sign.%20Then%20I%20evaluated%20the%20formula%20in%20AO11%20and%20noted%20a%20large%20number%20of%20%23VALUE!%20errors%2C%20and%20after%20about%20a%20hundred%20clicks%20it%20returned%200.00%2C%20exactly%20what%20is%20in%20AO11.%20So%2C%20I'm%20afraid%20I'm%20not%20the%20right%20person%20to%20help%20you%20here.%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344416%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344416%22%20slang%3D%22en-US%22%3E%3CP%3EYeah%20it%20is%20an%20extremely%20complex%20sheet%20for%20sure%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20for%20some%20reason%20every%20now%20and%20then%20it%20will%20go%20from%20working%20fine%20for%20months%20to%20suddenly%20getting%20a%20bug.%20Fortunately%20I%20know%20the%20sheet%20well%20enough%20that%20usually%20I%20can%20fix%20them%20quickly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344569%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20happen%20to%20have%20iterative%20calculations%20enabled%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20looked%20at%20the%20workbook%20yet%20(My%20work%20environment%20doesn't%20take%20kindly%20to%20XLSMs).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639149%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BHi%20guys%2C%20I%20experience%20this%20with%20my%20spreadsheets%20as%20well%20and%20have%20found%20a%20solution%20for%20my%20spreadsheets.%3C%2FP%3E%3CP%3EThe%20incorrect%20answer%20problem%20occurs%20whenever%20I%20have%20circular%20references%20in%20my%20formulae%20or%20if%20that%20little%20green%20triangle%20pops%20up%20in%20top%20left%20of%20cells%20anywhere%20in%20my%20spreadsheet.%3C%2FP%3E%3CP%3EIf%20I%20remove%20circular%20references%20and%20select%20%22Ignore%20error%22%20wherever%20there%20is%20a%20green%20triangle%20in%20the%20cell%2C%20then%20in%20my%20case%20the%20cell%20returning%20incorrect%20answers%20suddenly%20returns%20the%20correct%20answer%3C%2FP%3E%3CP%3E(If%20one%20hovers%20the%20mouse%20over%20a%20cell%20or%20series%20of%20cells%20with%20the%20green%20triangle%20top%20left%2C%20a%20sub-menu%20appears.%20I%20always%20choose%20%22Ignore%20error%22%20and%20the%20green%20triangle%20goes%20away.)%3C%2FP%3E%3CP%3EI%20hope%20this%20helps%20you%20too%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344135%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20returning%20incorrect%20result%20even%20though%20evaluation%20gives%20the%20correct%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20calculation%20set%20to%20'Manual'%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

The spreadsheet is posting an incorrect result, Even though when I run evaluate formula the final result inside its popup is correct, when finished a different result is posted to the spreadsheet

9 Replies
Highlighted

@Dichotomy66 Would be helpful if we could see the formula, the expected outcome and the result it produces.

Highlighted

It is not a formula issue as this is happening in a few different places on the spreadsheet. As I said the "evaluate formula" returns the correct answer as its final result but the spreadsheet then posts a different result. This spreadsheet and all its formulas has worked fine for a year or more with no changes. Then in the last few days it stopped working correctly

Highlighted
Highlighted

Tried it at auto and manual and the error recurred. I reinstalled Office365 and for now it seems to be working @Detlef Lewin 

Highlighted

Spoke too soon the reinstall did not correct the problem.

I attached the sheet ,,, look at cells  AO7:AO16 for example though it occurs in other places, IN particular AO11 and AO12. Running evaluate formula leads to a different result which is correct

Highlighted

@Dichotomy66 Wow, what a schedule. For an outsider like myself, the formulae are difficult to grasp without a thorough analysis of the entire workbook. When I opened the schedule I got a warning that it contained circular references. Not a good sign. Then I evaluated the formula in AO11 and noted a large number of #VALUE! errors, and after about a hundred clicks it returned 0.00, exactly what is in AO11. So, I'm afraid I'm not the right person to help you here. Sorry!

Highlighted

Yeah it is an extremely complex sheet for sure @Riny_van_Eekelen 

And for some reason every now and then it will go from working fine for months to suddenly getting a bug. Fortunately I know the sheet well enough that usually I can fix them quickly.

Highlighted

@Dichotomy66 

Do you happen to have iterative calculations enabled?

 

I haven't looked at the workbook yet (My work environment doesn't take kindly to XLSMs).

Highlighted

@Dichotomy66   Hi guys, I experience this with my spreadsheets as well and have found a solution for my spreadsheets.

The incorrect answer problem occurs whenever I have circular references in my formulae or if that little green triangle pops up in top left of cells anywhere in my spreadsheet.

If I remove circular references and select "Ignore error" wherever there is a green triangle in the cell, then in my case the cell returning incorrect answers suddenly returns the correct answer

(If one hovers the mouse over a cell or series of cells with the green triangle top left, a sub-menu appears. I always choose "Ignore error" and the green triangle goes away.)

I hope this helps you too