Comments on: Excel formulas not working, not updating, not calculating: fixes & solutions

This tutorial explains the most common mistakes when making formulas in Excel, and how to fix a formula that is not calculating or not updating automatically. Continue reading →

Comments page 13. Total comments: 459

  1. nup.. doesn't work still not calculate the sum of a range of cells NB one Column only.. a rainfall record.. not what I would call complex Chhers Roger

  2. I have an SQL query that I run in SQL Management studio and then paste into Excel. Parts of that query include some excel formulas like VLOOKUP(...) or Hyperlink(...) as well as data from the database.

    When I copy and paste these rows into excel, the formulas evaluate just fine. But now I am trying to build that query into the spreadsheet using data connections and the formulas are not being treated as formulas. The cells are formatted as General. The formulas start with =. If I click on the formula cell, pretend to edit it (making no edits) and then press enter, it calculates. But I don't want to do that twice per row ~ 1000 times.

    1. Found a workaround. Do a find-replace of = with =
      That triggers the calculation. But it still isn't a permanent fix.

  3. DITTO to #77 ! Frustration OVER !

  4. Hello anyone, I am trying to work out my mpg by using the formula =sum(E18/b18) and I get value instead of an answer. The whole line is 24.28Litres costing 117.7 totalling 28.58 with 213.2 miles. Why do I get value rather than a result? I was using Libra which did the calculation and switched to MS 365 which doesn't.

  5. last 20years am using excel, i just confused suddenly my formulas not working, after i searched google.... and i found this page, its great to have such solution (switching calculation back from manual to automatic),thank you dear

  6. Thanks for this, my excel formulae were not working this morning and it was indeed a terrifying experience, great to have such a simple solution (switching calculation back from manual to automatic)

  7. Hi my excel sheet contains addition formula for a particular date and that date is linked with other formulas.
    but even single is not working out what might be the probable cause ?

  8. Your article assumes a "mistake" was made in entering formulas.. I have used Excel for years and now it doesn't work... no mistake made. Call it what it is a huge Microsoft BUG! It will no longer copy formulas down a column using relative and absolute values either!

  9. I want to thank you very much. I was sick to my stomach when I came in today and tried to use my spreadsheet that I developed for use every day only to find out that some rather complex formulas had stopped working. I had a glitch in my computer yesterday causing one of my displays to rotate 90 degrees CCW. Apparently it also caused some changes in my Excel settings. After reading your information I was able to quickly determine that it had switched from automatic calculation to manual. Easy fix. Thanks again.

  10. Thanks, it was in 'Text' format.

  11. thank you for wonderful information

  12. When I am typing 1 in excel sheet. It is turning 0.01 automatically. Why is it so? Can anybody help me? And when typing 100 then ist 1.

  13. thanks..:)

  14. That was really helpful.. thanks

  15. when i am using multiplication in excel 2016 - 2.5*5.4 that showing error only showing decimal number

  16. Wow, that was a hassle. I had a column of mixed text and numbers, but the answer for COUNT() was always one less than it should have been. I tried the other solutions, finally used the trick of copying the column to a notepad file and pasting it back to a new column, and now I get the right count. (I used Paste Special just to be sure, although I'm not sure it was necessary.)

    One note: I didn't see a Paste Special > Values option on Excel 2010. Paste Special > had the options "Unicode Text" and "Text". I used Text because that was the unformatted option.

  17. I have been using time formulas to track specific activities in my job. I have a column for date, beginning time, ending time, net time by subtracting end from beginning and then at the end of week add up activities total time for the week.

    For the first 3 weeks it worked perfectly but at the 4th week the summ for the week quite working. I copied the formula from above, updated the cells to sum for that week and now week 4 comes up with 0 hours thought the hours each day are 5:30, 5, 6:30, 3:30 and 3:30 and the fifth week sums 3:30 though the sells being summed are 8,8,9,2:30. Those are all formatted as hours and minutes so show hh:mm though I only gave absolute values. What could have gone wrong?

  18. Thank you.
    Calculation option was changed!!!!
    I updated to automatic.....
    curse the shared file!!! :)

  19. great!
    Thanks

  20. Good morning - I am using Power Query linked to Sales Force reports and the data is shown in a table. I added 3 of my own formulas in the table to the right of the last column. When I open the file and refresh the data new rows of data are added daily but the formulas I created do not copy down in the table. Is this a setting I need to change or is there a way to correct?

    Thank you

  21. Hi!
    Im trying to get a result on these formulas:

    =DATEIF(C2<=D2,"On Time","Late Arrival")
    =IF(C2<=D2,"On Time","Late")

    But I get an error message. What could be wrong? I have dates in date format (2016-12-22) in C2 and D2. My aim is to compare to dates and generate the text On time and Late as a result.

    Thank you a lot!

    1. Hi Jenny,

      In Microsoft Excel, there is no DATEIF function. You probably meant DATEDIF, but it is designed for finding a difference between 2 dates and has a different syntax, please check here.

      Your IF formula is correct and works just fine for me. Exactly what error message does it throw in your sheet?

  22. am kindly asking for help . am working on results of students and each student has an individual workbook . how can I put position (rank ) in each students workbook basing on the totals of all the students ' workbooks?

  23. FINALLY - I wish you had a button that says "was this page helpful" because for the first time EVER - yes, I've finally found a page that was helpful. Thank you - the automatic calculation button had gone to manual. Now everything works again. Great joy !!!!

  24. Hello,

    I'm making an overtime spreadsheet to track the overtime pay of the employees. I have different rates which needs to be satisfied by different conditions. One of the rates would be the x1.0 the other would be x1.5.
    As for the x1.0 I am able to calculate the amount using...
    =IF(AND(D12="Public Holiday",OR(E12="Day",E12="Night")),L12*$N$11,"")
    However, when using a similar version for the x1.5, the amount isn't calculated.

    Am I making some mistake somewhere?

  25. Thank you! Thank you! My problem was inadvertently clicking the Show Formulas. Easy fix thanks to you! :)

  26. Very helpful... Thank you so much!

  27. Thank you so much for helping me solve my problem of my cells not computing. I realized after following your instructions, that somehow my formula converted itself to manual, not automatic. As you can imagine, it was driving me crazy. I would not have figured this out if it wasn't for this awesome article. Thank you!

  28. After countless hours using excel I stumble with this problem, don´t know the cause but closing and reopening the spreadsheet just worked for me.

    Sometimes just keep it simple.

  29. You saved my life! Thanks! i recently purchased a new dell laptop and got office 2016 installed onto this. Wasn't able to use my vlookup function across several rows and thought this could be an excel 2016 issue, but was lucky to come across your post and got it fixed! Thanks again.

  30. thank you sir

  31. thank you your data is usefull for me

  32. Super it's working

  33. hi dear,

    i have problem with my formula.
    when i key in =Sum(D20+6%), the answer will not appear the correctly

    for example D20 amount is $30.00

    =sum(D20+6%)
    answer : 3006.00%

    **the answer should be $31.80**

    how can i solve this problem? TQ ^_^

  34. I have the formula in my excel below:
    =IF(ISERROR(AD43/SUM(IF($C$9:$C$36>0,IF($B$9:$B$36=2,1,0),0))),0,AD43/SUM(IF($C$9:$C$36>0,IF($B$9:$B$36=2,1,0),0)))
    It is made to grab the average of certain cells. When I click on insert function the formula result is giving me the value of 17.5 which is correct. However the actual cell on the spreadsheet is showing a 0. I cannot figure out why it is doing that. Any help would be great.

  35. m using any type of formula in excel(like Sum Add concatenate and more, I didn't get answer.
    excel sheet shows formula not excel

    exp: =F3&E3

    1. Hi pankaj,

      It seems you have the "Show Formulas" option enabled. Please go to the Formulas ribbon tab and check if the "Show formulas" option unpressed in the "Formula Auditing" group.

  36. Dear sir,

    My problem is with links to sheets, I enter the formula =+'Items C'!C125, in a cell and get no results, however few lignes bellow I enter the same formula again but the text is shown.

    Now if I create a new row and enter =+'Items C'!C125, it works. However my goal is to do the formula =+'Items C'!D125, but once I enter it by changing the previous one It doesn't work anymore. Therefore, when I crtl+z, the previous formula doesn't work anymore.

    Next, If I create a new row and enter the formula =+'Items C'!D125 It works. But If I try to transpose it to =+'Items C'!D122, it doesn't work anymore and the problem occurs again when I try to go back to =+'Items C'!D125.

    Now If I enter the formula right at the first time and expend it it works for the next values, but when I try to change number/letter inside the formula, it stop working.

    I don't understand why formula doesn't work when I do these transformation...

    Some help would be nice.

    Thank you

    1. Hi salade,

      To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  37. how to write a formula for this

    if A and D are both less than 75: 0
    if A is greater than or equal to 75 and D is less than 75: Calculate (A — 75) = value.
    if D is greater than or equal to 75 and A is less than 75: Calculate (D — 75) = value.
    if A and D are both greater than or equal to 75: Calculate [(A — 75) + (D — 75)] = value.

    all conditions in single formula please help

    thank you

    1. Hi arun,

      You should use the following formula:
      =IF(AND(A1<75, D1<75), 0, IF(AND(A1>75, D1<75), A1-75, IF(AND(A1<75, D1>75), D1-75, A1-75 + D1 - 75)))

  38. pls. help formula in one line

  39. Fabulous, formula calculation to automatic, works for me,. i was facing this issue from so long. Thank you so much Team- amit

  40. I am using a UDF to sum a range based on their cell colour below:

    Function SumByColor(CellColor As Range, SumRange As Range)
    Application.Volatile
    Dim ICol As Integer
    Dim TCell As Range
    ICol = CellColor.Interior.ColorIndex
    For Each TCell In SumRange
    If ICol = TCell.Interior.ColorIndex Then
    SumByColor = SumByColor + TCell.Value
    End If
    Next TCell
    End Function

    This works fine, however the range I am using has conditional formatting set to change the colour. For some reason this script only recognises the cell colour if I manually change it.

    Am I missing something?

    Thank you for any help you can provide

  41. hi nigel,
    my formula =sum(e3:e30) is showing error#####
    what could be the problem

    1. Hi Ann,

      Excel displays hash marks if a cell is too narrow to display the value. If it's the case, simply make the cell wider.

      1. Hi
        Thanks very helpful

  42. This week I noticed that my formulas in my time tracking for work just stopped calculating correctly. All of a sudden this one document became locked and when I unlocked it nothing seems to work right. Two of my coworkers experienced this as well with completely random worksheet they are working on.

    The only solution that seemed to have worked to fix the problem is to repaste it into a brand new excel doc. I regenerated the formulas for one area and when I just tried to paste the Formulas with special paste feature it still is pasting the values as swell. To me it seems like a glitch has occurred with Microsoft itself. Please help

  43. Nice Suggestion it worked, Mine was the Formula option accidentally chosen as Manual not i changed to Automatic, It is working now

  44. I have a cell (F17) which calculates how many years between dates. The result of this formula needs to be looked at by an If function to return a value: =IF(F17=2,1,IF(F17=3,2,IF(F17=4,3,IF(F17=5,4,IF(F17>5,5)))))

    i.e if cell F17 is 3 years, return 2 etc

    It does not recognise the formula result in the cell calculating the years.

    Any solutions?

    1. Hi Nigel,

      Your IF formula is correct. And what formula do you use to calculate years (F17)?

      1. Hi,

        Just realised I should have used the DatedIf function. Just changed it and it now works. Thanks for getting back to me. I knew it had to be simple error on my part.

        Cheers

  45. My formulas are not calculating correctly. The sum is incorrectly calculated as "0". I have followed various recommendations including checking that formulas are set to calculate automatically. I have converted any cells set as text to numbers etc. Please help. Thanks.

    1. Hello Karina,

      Please check is your SUM formula does not make a circular reference. For example, if you are totaling a column using a formula like SUM(A:A) and input that formula in any cell of column A, the formula will return 0. If it's not the case, you can send us your sample worksheet (support@ablebits.com) and we will try to help.

  46. Why is that the help function (fx) shows the correct answer but the cell is returning the wrong value.

    1. Hi Mary,

      Sorry, it's difficult to determine the source of the problem without seeing your formula and data. If you can send us your sample worksheet at support@ablebits.com, we will try to help.

  47. thank you, a simple explanation on automatic calculation saves my day

  48. Hi,

    My Vllokup formula was working till Friday now it isnt taking the table array data from a different file (source file), what should i do to make it work. I tried copying the entire data and opening in new workbook too it isnt working either.

  49. I have Excel 2010 and I am having trouble getting correct calculations in simple formulas: =A10 + B10
    In cases where the cell values are currency with $ the answer comes up wrong by pennies.
    Why so?

  50. Dear ,

    How to get due date email from my excel sheet 2010 automatically,

    Thanks

    Arif

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)