0

What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?

I've Googled this problem and ruled out all the explanations I can find:

  • Calculation settings are set to automatic.
  • Formulas/Calculation Options for the tab is set to automatic.
  • Manually recalculating doesn't work.
  • Calculate in VBA doesn't do it.
  • It's not a circular reference or numbers stored as text issue.

Normally calculating manually with F9 or switching calculation to automatic works as expected, but sometimes I have to use Ctrl + Shift + Alt + F9 to recalculate certain cells. That works as a short term workaround, but I need to fix the problem going forward to calculate normally. I've only ever seen the issue happen in specific cells in spreadsheets sent to me by one person. I've been unable to recreate the issue or figure out what is causing it.

I hope this is ok to ask here, I tried on StackOverflow and was told it was a question for SuperUser.

  • Any similarities in the cells it happens to? – Raystafarian Apr 8 '16 at 21:58
  • I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally. – ELW Apr 8 '16 at 22:01
  • What if you copy that sheet to another workbook? Does it still happen? – Raystafarian Apr 8 '16 at 22:02
  • Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected. – ELW Apr 8 '16 at 22:05
  • 1
    I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module. – Raystafarian Apr 8 '16 at 22:05
0

A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.

B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.

-1

Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.

Cell format value in Ribbon

Then edit the cell and exit the cell. The formula should now resolve.

I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.

  • A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula. – Aganju May 27 '16 at 1:57
  • @Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General. – MJA May 28 '16 at 1:39
  • no, if it is set to 'Text', everything would look exactly as he types (so 4 would look 4 and not 4.000000), and the numbers could not be used for formulas. – Aganju May 28 '16 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.