What happens when good Excel formulas go bad? A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. Even more mysterious, the calculations worked fine on some machines, but not others. Why are Excel formulas not calculating on some computers?
Hint: It wasn’t one of the obvious solutions.
Check for Automatic Calculation
When someone complains about Excel formulas not calculating , it’s usually because the Calculation setting has been changed to Manual, instead of Automatic.
That can happen if the first workbook that you open in an Excel session was saved as Manual calculation. That setting affects all the other workbooks the you open during that session.
In this case, the workbook calculation was set to Automatic on all the machines – that’s the first thing we checked.
To check the setting, click the Formulas tab on the Excel Ribbon, and click the Calculate Options drop down.
Or, go to the Excel Options window, and click Formulas.
Force the Calculation
Even if the Calculation option is set for Manual, you can use a Ribbon command or keyboard shortcut to force a calculation.
Click the Formulas tab on the Excel Ribbon, and click Calculate Now or Calculate Sheet.
n the tooltip that is shown in the screen shot below, you can see that the shortcut for Calculate Sheet is Shift + F9.
We tried using the Calculation shortcuts, including Ctrl + Alt + Shift + F9 (that one recalculates everything, and starts your car).
However, none of the shortcuts had any effect on the non-calculating cells in this workbook. I also tried a macro that did a full calculation, and that didn’t work either. Sigh.
Kick Start the Formulas with VBA
Next I wrote a macro that replaced all the “=” signs at the start of all formulas in the workbook. This has a similar effect to manually selecting a cell, clicking in formula bar and pressing the Enter key, to recalculate the cell.
Sometimes that brings a tired formula back to life, but not in this case. These formulas weren’t sleeping, they were deceased!
Change the Named Range in the Formula
I spent a considerable amount of time in Google, looking for other suggestions, but didn’t find anything new. So, I kept tweaking and testing, going through the file one worksheet at a time.
Finally, I found the formulas that seemed to be causing the problem. They were SUMIF formulas that referred to a named range on another worksheet.
=IF(B7=””,0,SUMIF(DataEntryStep2a,E7,$D$7:$D$22))
There was no obvious reason why that wouldn’t work in Excel 2010, but I was getting desperate. So, I changed the named range to a worksheet reference, using cells on the same worksheet.
=IF(B7=””,0,SUMIF($E$7:$E$22,E7,$D$7:$D$22))
Miraculously, that solved the problem! I held my breath, while we tested on a few more machines, and everything calculated as it was supposed to.
Why that worked, I have no idea, but if you run into a similar problem, maybe it will help you too. Of course, this solution could stop working again, at the next lunar eclipse!
[Update] There is problem with SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS, in Excel 2010, when the references are on a different sheet. Thanks to Stuart Valentine, who posted a link to a discussion of this SUMIFS problem. My named range was on a different sheet, so the location was the problem.
Watch the Video
In most cases though, the problem is the Automatic Calculation setting.
To see the effects of opening Excel workbooks with different calculation option settings, watch this short Excel tutorial video.
_______________
Superb man
Excel sheet back to behaving as it should. Thanks so much!
@Brandon, Great! Glad the tip helped you
Hi,
I have problem with excel formula,
when I make I G10 Multiply with I10 then they show rust wrong the G10 cell have also divid formula, if I remove the divide formula from G10 and add value then they make result right, you can see screenshot in this link, https://i.imgur.com/Q0j8fTx.png
pleae fixed this problem,
Sajida
Thanks a ton!
I have a problem with formula in excel if I write for example =0.5*2 then ENTER “Error msg displays that my formula is incorrect; but if I change to write =0,5*2 then ENTER “Answer is 1” as I expect: I can see that my computer takes (,) comma instead of (.)! What can I do to reset it to normal
Was racking my brain on a similar issue. I wrote my own functions in VBA that read data across other named ranges on the same work sheet, and changes to their value would not update cells with that function. My problem was the named references were used in the VBA, not as parameters to the function. If you recode your functions even to require a parameter you won’t use, but some cell that you know will update when you enter changes, then Excel will refresh the cell calling your function. Excel seems to look in the function property of each cell for references, and if you use the dependency tool, you can see how it inter-relates your cell that isn’t updating. You have to select the cell you want to update manually to use the tool (Formula / Formula Auditing, Trace Dependents). Even if you don’t have a single cell that updates every time you make changes to data, you can create a hidden row at the bottom and use a countA function to count the number of cells that have data in them. This won’t result in any overflow errors because you can’t have enough cells to cause an overflow. For example, “=COUNTA(1:60)” can be placed in cell A61. Make one of your parameters to your built in function called “RefreshWhen as variant” and just don’t use it anywhere in the function. When you type your formula, include all the necessary parameters your function needs, and the additional reference to cell A61. Now any time data is changed in the cells of rows 1 though 60, A1 recalculates, thus all uses of your function referencing it will recalculate.