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. So, what was the problem?
Hint: It wasn't one of the obvious solutions.
Check for Automatic Calculation
When someone tells you that formulas aren't calculating, it's probably 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. In 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.
Or watch on YouTube: Excel Calculation Automatic or Manual
_______________
Debra,
Check my comment on your recent SUMIFs post - there is a bug in Excel 2010 with these functions when the arrays are not located on the same sheet as the formula:
http://blog.contextures.com/archives/2012/02/02/excel-sumifs-sum-with-multiple-criteria/#comment-23047
See also:
http://social.technet.microsoft.com/Forums/en/excel/thread/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7
Thanks Stuart! I've updated the article to include your link.
I read your previous comment, but too quickly -- it obviously didn't register in my brain.
Debra - did you save the file as a xl2010 file first?
I noticed calculation problems when using xl2003 files in compatibility mode with xl2010. Saving as .xlsm, closing, and reopening seems to have solved the problem.
Thanks Alex, I did save the file in xlsm format, and still had the problems. Looks like the SUMIF problem is fixed for 32-bit but not 64-bit
Can the averageif function use in pivot table ?
None of these options worked for me.
What did work, however, was ending the excel.exe task in the task manager.
This solution was very helpful. All of a sudden this morning, when copying and pasting between workbooks, or when copying and pasting within a worksheet, or when using auto-fill to copy my Excel formulas within a worksheet, my formulas stopped calculating. It appeared as only the values were being pasted, not the formulas. I searched and searched and all I came up with was solutions having to do with my clipboard being hijacked by malware, which could not have been the problem as I was not online at the time this problem began to occur. Thank you very much for helping me.
Thanks for this post, appreciate additional solutions other than just turning the calculation on, as is suggested in most other forums. Replace "=" was all I needed to fix dead formulas.
Thank you! This was my exact problem and the solution you have worked splendidly. Saved me hours!
Thanks. it solved my problem.Very useful post.
Thanks alot
You solved my problem righ away. Thanks
Not sure how to post a new thread, but:
Why do these two formulas return the same result:
in col L =IF(E3:E133="*VISAMM*",G20,F20)
where col E has the text string, col M should be false or blank
in col M =IF(E3:E133="*VISATB*",G20,F20)
where col E has the text string, col L should be false or blank
If I reverse the Value if TRUE or FALSE cells I get 0.00
I get a return of col F for both IF Statements
I want to return col F as true when the text string is satisfied in the range.
Thanks,Mark
I have had this issue for a while, the file is saved as a 2003 version (due to issue when we first changed over) and sits on a server. The formula is basic =SUMIF('Full List'!D:D,B19,'Full List'!J:J) so it should work ..and it does sometimes. It is not the autocalc setting and does not work even when force calculated, double clicking in the cell gets that cell to work, but not the others. I have a manual fix, I have a macro to save the file to C:\temp (the formula works straight away) and then saves back to the server. From then on it works fine !!! Until the next day when we do this all again. My fear is how many other files are not calculating correctly !
Is there a fix for this now ?
Thanks ...Bill
Hey, thanks a lot, that ws quick and easy fix, on which I had been stressing and scratching my head over. Thanks again.
Hello,
So I am having an issue with one of my formula's I am using the basic "=Sum" calculation. It work for most cells but for a few it is a number off. So it will give me a result for five for something the should be 6. I have two cells each has 3 in it and the result shows 5. I have checked and I have the automatic calculation on. Anyone who could shed some light on this? I would greatly appreciate it.
Thanks...Kristen
Thank you - it's great when I can find a solution without spending hours searching for the solution
Hi There Guys
I recently bought a notebook with windows 8 on. I purchased Office 2010
What I find and cannot solve is that if I open an old document and change values, excel calculates incorrectly. Even if I re-type all the values in the cell and do the summation over it still calculates incorrectly. If I do aa new calc in the same area(just below) it also calculates incorrectly. If I do another calc in the same page but in a different area the calc is fine. New documents is no problem.
Please help... as it were I am also technologically challenged
produk bottel trigger label box labour Total cost
100ml 10.3 .95 .97 1,00 .1 2,00 3,00
100ml 10.30 0,95 0,97 2,00 0,10 2,00 6,02
Thank you Debra!!!
Saved hours of work for me too.
Great job!!!
My formulas were giving me issues only with subtraction. To fix it, I entered the formula with "spaces". For example:
Instead of =B10-D10 I wrote: = B10 - D10
This helped me after all else failed.
Just Thanks easy when you know how!
The cell values will also fail to update properly if your formula refers to a defined function in another workbook.
Thank you!
i have excel sheet which contain data of license and contracs. i want a cell to notify me the expire status in 3 conditions.. 1) 30 and more days= active 2) below 30 = expire soon 3) 0 and below 0 days = expired. can u provide a formula to solve this pls thanks in advance
I thought i had the same problem - tried lots of solutions...just realised I had 'Show Formulas' activated. Ooops.