Excel Formulas Not Calculating

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. 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.

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.

n 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.

_______________

68 thoughts on “Excel Formulas Not Calculating”

    1. I’ve got a weird Mac Excel 2011 simple formula in a check register that quits calculating correctly after about 1500 row. For example =g1600 +sum (e1601:f1601). This takes the outstanding above and adds the negative amount for check, plus deposit. Amount shown after about 1500 rows calculates to wrong answer. Have tried =g1600+e1601+f1601. That get # value error.

  1. 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.

  2. 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.

  3. 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.

  4. 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

  5. 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

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

  7. 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

  8. 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

  9. 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.

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

  11. 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

    1. =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.

      1. 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")))

  12. I thought i had the same problem – tried lots of solutions…just realised I had ‘Show Formulas’ activated. Ooops.

  13. 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.

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

  15. 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

  16. 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.

    1. I realise this is a long time after you asked the question – but I recently came across the same problem dealing with excel files exported from another program. It turned out the cells were formatted as “Text”. Changing that to “General” got them to calculate again.

  17. 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.

  18. 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

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

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

  21. 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)))
      1. The Manual Pend sheet is a pivot table and only picking up work items that had been pended. I am trying to determine to total number of days it took to complete a task whether it was pended or not.

  22. 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

  23. 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

  24. 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.

  25. I noticed that a previously saved file with more than one tab selected, also known as group mode, when opened again will default to manual calculation. If this happens you will need to set calculations back to automatic by any method mentioned above.

  26. Pingback: Functions give error only in macro-called dialog | Solutions for enthusiast and professional programmers
  27. Hi,
    I have some values in b5:b14 currency format, the same way C5:c14 date format. I need recently increased amount, which amount is increased for the which date.
    amount date
    100 01/01/16
    50 02/01/16
    100 03/01/16
    100 05/01/16
    here recently increased 100 for the date of 03/01/16. I need the value amount in cell D3, date is D4.
    so please suggest which formula I can use for the scenarios.

  28. Hi,
    I have created vba to creatable from the consolidated data in a sheet1. After a table structure is created, the macro will populate the values based on the formula.
    in my macro there are 3 tables. the 1st table is getting created with appropriate values based on the formula. during the process od 2nd and 3rd table, formula which is applied for grandtotal sums the above table. This is the area where the error is starting and proceeds the same for the remaining tables.
    I don’t have any issues when this macro is run in my system. when this gets run on the other system, this error occurs. Any fix for this. EXCEL 2013

  29. I’m dealing with the same basic situation in Excel 2003 except that only a few cells not only won’t calculate but displayed the formula. I deleted the formulas (a SUMIF) and used the formula bar to construct the formula, but to no avail. The formula is very simple, nothing fancy. And to make it more frustrating all my other formulas are working fine. I’ve exited Excel, rebooting the computer, etc. Finally, I remembered I had first written the formula using range names instead of references the very first time I entered the formulas in those cells. So I wrote the formulas in different locations a row or two away and a couple columns over. Presto! Go figure.

  30. Hi
    I am having problems with the Substitute formula.
    I am trying to substitute a name (in column K) for a specified number (in a new column). K2 has lots of random names, including the six names in my formula. This is what I currently have:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2, “Justitia”, “886.57”), “Euryalus”, “946.17”), “Warrior”, “1642”), “Leviathan”, “1707.95”), “Fortitude”, “1718.17”), ”York”, “1743”)

    What I get is: #NAME? and the green triangle in the corner of the cell.

    Thanks

Leave a Reply to Megan Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.