Excel Formulas Not Calculating

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.

formulacalculate00

Or, go to the Excel Options window, and click Formulas.

formulacalculate01

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.

formulacalculate03

We tried using the Calculation shortcuts, including Ctrl + Alt + Shift + F9 (that one recalculates everything, and starts your car).

formulacalculate04

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.

formulacalculate05

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

_______________

52 comments to Excel Formulas Not Calculating

  • Stuart Valentine

    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

  • AlexJ

    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.

  • andi

    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.

  • Jason

    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.

  • Caroline

    Thank you! This was my exact problem and the solution you have worked splendidly. Saved me hours!

  • avinash

    Thanks. it solved my problem.Very useful post.

  • Heather

    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

  • Bill Robinson

    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

  • Dimbs

    Hey, thanks a lot, that ws quick and easy fix, on which I had been stressing and scratching my head over. Thanks again.

  • Kristen

    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

  • Lori

    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

  • Alberto Frocht

    Thank you Debra!!!
    Saved hours of work for me too.
    Great job!!!

  • Cheryl

    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.

  • Darren

    Just Thanks easy when you know how!

  • Jason_WI

    The cell values will also fail to update properly if your formula refers to a defined function in another workbook.

  • basheer

    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

    • Ram Paudel

      =IF(DATEDIF(A4,TODAY(),"D")>=30,"Active",IF(DATEDIF(A4,TODAY(),"D")<31,"Expire soon","Expired"))

      I have created a formula for you. Put the date in A contract date in column, I am sure this formula will work.

      • Ram Paudel

        You can use the following formula if you want to avoid formula errors due to date formats:
        =IF(A11>TODAY(),"Expired",IF(DATEDIF(A11,TODAY(),"D")>=30,"Active",IF(DATEDIF(A11,TODAY(),"D")<31,"Expire soon","Check Dates")))

  • Rob Bane

    I thought i had the same problem - tried lots of solutions...just realised I had 'Show Formulas' activated. Ooops.

  • Jessica

    Thank you! perfect instructions. Fixed the problem :)

  • Eric

    Thank you for helping me solve my calculation issues. I don't know how it got changed from automatic to manual and would never have thought that there would be a setting to make the formulas only change by saving.

  • Scott

    Thank you..just like Eric I inadvertently changed my calculation settings to manual. Learn something new every day...

  • SHIVA NAIK

    Hello everybody,

    I have a problem with external link when used with index function otherwise rest of external link are working perfectly.
    I have created a external link after doing all the appropriate setup for automatic update of values the destination file formula updated correctly. even if not opened the source file. This is working perfectly as expected. but when i used the same thing with index function it updated ONLY when source workbook was opened otherwise it don't updated. please please help on this i am stuck with this and not able to continue further development.

    BELOW ARE THE TWO FORMULAS. #1 IS UPDATING AUTOMATICALLY. BUT #2 NOT UNLESS SOURCE FILE OPENED.

    1> ='D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!Q4

    2> =INDEX('D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!$A$10:'D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!$Q$100001,MATCH(A11,'D:\SHIVA\CABLE_2013.xlsx]Customer_collection_2013'!$A$10:'D:\SHIVA\CABLE_2013.xlsx]Customer_collection_2013'!$A$100001,0),17)

    Thanks,
    Shiva Naik

  • Meg

    THANK YOU! This had been making me CRAZY. Easy fix!

  • Muswell

    It seems there are many reasons why this is happening, but none of the above have fixed my issue which is in Excel 2013.

    I should say I have auto calculate on, and on my own excel files ( ie ones created on MY pc - no problems)

    However If I get an excel file from a client and try to add the simplest of formulas ( eg If I write ="fred" in cell A1) it should say fred - it just sits there saying ="fred"

    This is fairly basic microsoft - have you really not tested this ???

    The only solution I have found is to save the incoming file as a csv format and then reload it - bingo - all automatic calculations work perfectly.

  • Dwaraki Rao

    Thnx a lot... i salute you.. was simply unable to findout why "only my excel sheet cannot calculate"..

  • Diane Turley

    Thanks!!! This was really helpful, and saved me a possibly painful call to the Help Desk.

  • Old Paul

    I faced a similar issue - formulas that referred to other formulas were not getting updated despite the 'automatic workbook calculation' being active. This is Excel 2010 running on Windows 8. Replacing the equal sign fixed it.
    I am still facing another issue - short cuts such as 'alt-e-t' to cut a row don't work. If I make another workbook active and then go back to the original workbook, the short cuts work again.

  • Steve

    None of your solutions have been a perfect one for me, but reading this article helped me focusing my research. I found a page on MSDN that gave me the perfect solution. I force the problematic cells to be dirty by typing the following line in the "change" procedure of the worksheet object code.

    Application.Range(myRange).Dirty

    It simply tells excel that the cells within the range require a recalculation whenever any change occur in the sheet.

    The link to the MSDN reference:
    http://msdn.microsoft.com/en-us/library/office/bb687891.aspx

  • Osama wais

    Thanks your information is helpful

  • sudheer kumar

    I would like to get excel tutorials will u please send them to my mail....thank you...

  • louis

    This is so helpful. Thanks a lot. I met the very same problem, formulas not updating due to setying to manual

  • brad

    Hey, Thanks a lot. You saved me hours... This was my issue, too.

  • JULIE

    Thanks! Solved my problem quickly with no frustration

  • Amit

    Many Thanks! Could not think the solution could be too easy. Saved a lot of time and efforts of mine.

  • Jay

    Wow, thank you so much!

  • Mike

    I have a formula returning N/A and I know it has to do with not dealing with a specific cell returning the result of "= Completed" but I am racking my brain on how to get it in the formula.

    =IF(D3="Completed",0,IF(VLOOKUP(A3,'Pended Info'!M:Q,5,FALSE)<0,
    IFERROR(VLOOKUP(A3,'Manual Pend'!M:Q,5,FALSE),0),
    IFERROR(VLOOKUP(A3,'Pended Info'!M:Q,5,FALSE),0)
    +IFERROR(VLOOKUP(A3,'Manual Pend'!M:Q,5,FALSE),0)))
  • leo

    Thanks a lot guys...

  • Brandon

    Excel sheet back to behaving as it should. Thanks so much!

  • 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, http://i.imgur.com/Q0j8fTx.png

    pleae fixed this problem,

    Sajida

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>