Formulas not computing

%3CLINGO-SUB%20id%3D%22lingo-sub-2281603%22%20slang%3D%22en-US%22%3EFormulas%20not%20computing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281603%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Mac%20running%20Mojave%20(10.14.6)%20and%20Office%20365.%20%26nbsp%3BI%20periodically%20find%20that%20formulas%20in%20one%20of%20my%20files%20will%20not%20compute%2C%20i.e.%2C%20they%20remain%20in%20the%20cell%20as%20typed%20rather%20than%20computing%20the%20result.%20%26nbsp%3BI%20have%20seen%20references%20to%20this%20problem%20advising%20to%20be%20sure%20the%20cell%20is%20not%20Text%2C%20which%20doesn't%20make%20sense%20as%20the%20result%20of%20the%20computation%20%3CEM%3Eis%3C%2FEM%3E%20text.%20%26nbsp%3BThere%20are%20also%20references%20to%20being%20sure%20there%20are%20no%20circular%20references.%20%26nbsp%3BHowever%2C%20I%20have%20found%20that%20I%20can%20force%20computation%20by%20doing%20a%20Replace%20of%20anything%20in%20the%20cell%2C%20e.g.%2C%20Replace%20the%20%22%3D%22%20with%20an%20%22%3D%22.%20%26nbsp%3BThis%20convinces%20me%20that%20there%20is%20nothing%20wrong%20with%20the%20formula.%20%26nbsp%3BOnce%20it%20starts%20to%20misbehave%2C%20it%20does%20not%20compute%20formulas%20for%20any%20cell%20visited%20and%20altered.%20Formulas%20already%20computed%2C%20as%20from%20the%20saved%20file%2C%20compute%20properly%20until%20an%20attempt%20is%20made%20to%20change%20them%2C%20i.e.%2C%20if%20I%20begin%20with%20a%20proper%20file%2C%20the%20cells%20I%20don't%20change%20compute%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20appears%20to%20be%20a%20state%20into%20which%20Excel%20gets%20itself%20as%20once%20it%20starts%2C%20it%20continues%20for%20an%20undetermined%20time%20in%20all%20(altered)%20computational%20cells%20and%20then%20apparently%20fixes%20itself%20and%20goes%20away.%20%26nbsp%3BDuring%20the%20period%20of%20non-computing%20I%20do%20nothing%20to%20the%20cell's%20formatting%20or%20to%20the%20preferences.%20%26nbsp%3BSometimes%20saving%20the%20file%20and%20quitting%20with%20restarting%20Excel%20fixes%20it%2C%20but%20not%20always.%20%26nbsp%3BRestarting%20the%20computer%20seems%20to%20have%20no%20effect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorking%20in%20this%20mode%20is%20quite%20tedious%20as%2C%20once%20you%20make%20a%20change%20in%20a%20cell%2C%20you%20then%20have%20to%20do%20the%20Replace.%20%26nbsp%3BI%20don't%20know%20what%20the%20problem%20is%20with%20Excel%20on%20a%20Mac%2C%20but%20the%20Replace%20menu%20item%20indicates%20that%20%22%5EH%22%20is%20a%20hot%20key%20for%20Replace.%20%26nbsp%3BOn%20my%20rig%2C%20%22%5EH%22%20or%20%22%5Eh%22%20merely%20produces%20%22H%22%20or%20%22h%22%20correspondingly.%20%26nbsp%3BIn%20Excel%20it%20produces%20them%20in%20the%20Font%20and%20Size%20windows.%20%26nbsp%3BIf%20this%20hot%20key%20worked%2C%20it%20might%20be%20less%20tedious%20to%20make%20the%20formulas%20work%2C%20but%20I%20shouldn't%20have%20to%20resort%20to%20this%20stratagem.%20%26nbsp%3BWhile%20I'm%20at%20it%2C%20I'd%20say%20that%20I%20much%20prefer%20the%20implementation%20of%20Find%2FReplace%20in%20Word%20in%20Office%202011%2C%20where%20the%20Find%2FReplace%20window%20remains%20open%20if%20one%20does%20not%20specifically%20close%20it%20and%20is%20just%20another%20window%20which%20can%20be%20used%20without%20resorting%20to%20the%20Ribbon%20or%20the%20Menu%20bar.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281603%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282284%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20not%20computing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033904%22%20target%3D%22_blank%22%3E%40pokeyarw%3C%2FA%3E%26nbsp%3BOn%20a%20Mac%20myself%20though%20running%20Big%20Sur.%20Mojave%20is%20quite%20old%20and%20perhaps%20an%20upgrade%20helps.%20Can't%20replicate%20the%20erratic%20%26nbsp%3Bbehaviour%20you%20described%20and%20have%20never%20experienced%20it%20myself.%20I%20don't%20believe%20you%20are%20accidentally%20switching%20off%20and%20on%20the%20automatic%20calculation%20mode%20either.%20Or%20do%20you%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-23%20at%2010.11.29.png%22%20style%3D%22width%3A%20278px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274922i6767BCC31C22D156%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-23%20at%2010.11.29.png%22%20alt%3D%22on%20the%20Formulas%20ribbon.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Eon%20the%20Formulas%20ribbon.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regards%20to%20the%20Find%20%26amp%3B%20Replace%20window%2C%20press%20ctrl-H%20to%20open%20it.%20If%20that%20doesn't%20work%20for%20you%20something%20is%20wrong%20indeed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

I have a Mac running Mojave (10.14.6) and Office 365.  I periodically find that formulas in one of my files will not compute, i.e., they remain in the cell as typed rather than computing the result.  I have seen references to this problem advising to be sure the cell is not Text, which doesn't make sense as the result of the computation is text.  There are also references to being sure there are no circular references.  However, I have found that I can force computation by doing a Replace of anything in the cell, e.g., Replace the "=" with an "=".  This convinces me that there is nothing wrong with the formula.  Once it starts to misbehave, it does not compute formulas for any cell visited and altered. Formulas already computed, as from the saved file, compute properly until an attempt is made to change them, i.e., if I begin with a proper file, the cells I don't change compute properly.

 

This appears to be a state into which Excel gets itself as once it starts, it continues for an undetermined time in all (altered) computational cells and then apparently fixes itself and goes away.  During the period of non-computing I do nothing to the cell's formatting or to the preferences.  Sometimes saving the file and quitting with restarting Excel fixes it, but not always.  Restarting the computer seems to have no effect.

 

Working in this mode is quite tedious as, once you make a change in a cell, you then have to do the Replace.  I don't know what the problem is with Excel on a Mac, but the Replace menu item indicates that "^H" is a hot key for Replace.  On my rig, "^H" or "^h" merely produces "H" or "h" correspondingly.  In Excel it produces them in the Font and Size windows.  If this hot key worked, it might be less tedious to make the formulas work, but I shouldn't have to resort to this stratagem.  While I'm at it, I'd say that I much prefer the implementation of Find/Replace in Word in Office 2011, where the Find/Replace window remains open if one does not specifically close it and is just another window which can be used without resorting to the Ribbon or the Menu bar.

1 Reply

@pokeyarw On a Mac myself though running Big Sur. Mojave is quite old and perhaps an upgrade helps. Can't replicate the erratic  behaviour you described and have never experienced it myself. I don't believe you are accidentally switching off and on the automatic calculation mode either. Or do you?

on the Formulas ribbon.on the Formulas ribbon.

 

With regards to the Find & Replace window, press ctrl-H to open it. If that doesn't work for you something is wrong indeed.