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 14. Total comments: 459

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

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

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

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

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

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

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

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

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

  10. thank you sir

  11. thank you your data is usefull for me

  12. Super it's working

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

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

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

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

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

  18. pls. help formula in one line

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

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

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

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

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

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

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

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

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

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

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

  30. Dear ,

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

    Thanks

    Arif

  31. Very Helpful..!!
    thank u sooo much

  32. The following formula is not working on a mac excel worksheet.
    =OFFSET($G2,0,MATCH($B$1,$G$1:$L$1,0)-1)
    although it seems to work just fine on PCs. Any ideas?

  33. Thanks very helpful

  34. I have copied and pasted (value) a new column of formulas in my spreadsheet and updated the date for the new period of data. However, the new column of data just won't update. I have tried deleting and re pasting the column and have checked the formula is consistent. Rechecked the date field is formatted as a date. Running out of ideas as to what else I could do.

  35. excel formula for the following please: Joe appears in B2 and D6; I want that when this happens, the answer should be CIS in another cell. Can some provide me with the formula? Rgds

  36. i am having 23+56+89 in cell a2. i want to calculate value of cell a2 i.e, result should be 168. data was uploaded by third party and 8000 such cell needs to be calculated. is there in function to calculate the text from formulatext function.

  37. Thanks!!

  38. very helpful tq much appreciated

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

  40. Thank you!

  41. I tried all of the fixes and they did not help, most of which were already OK, such as formatting everything as General. Only one formula is not updating. It is of this form: ="Manager Name Is "&R32, where R32 is just a cell where you input a manager name. Everything is formatted as General. If I hit F9, it does not help. However, if I simply move to another worksheet, then move back, it updates. Actually, fortunately, if I do file print, it updates. I cleared the row, deleted the row, and typed formula back in, with no luck. Tried doing it on a fresh empty file, and same problem there too. Given the problem exists even on a new file, does this mean my installation of EXCEL is corrupted? Thanks.

  42. Formula fill is not working. The result for the following rows is the same result as the first row....if you click on cell for the second result, the formula bar correctly shows it should be 6*66 but results 32. if you click in the formula bar then enter, the result will be correct. Any ideas?

    Qty price total
    1 32 32
    6 66 32
    7 79 32
    1 18 32

  43. Thanks. It is helpful

  44. thanks for the help

  45. The below suggestion really works for me.
    Thanks ...

    "On the Excel ribbon, go to the Formulas tab > Calculation group, open the Calculate Options drop down, and click Automatic:"

  46. Hi. Why do i get zero value when i find the sum of a bunch of number or sometime the display is"-"

    1. Hi!

      The most common reason why the SUM formula returns 0 for a bunch of numbers are numbers formatted as text. To check this, use the ISNUMBER() function. If it returns FALSE, it means your numbers are text strings, and you need to convert them to numbers as explained in "6. Make sure numbers are not formatted as text values".

      Zeros may appear as dashes because of the following reasons:

      - The Accounting format is applied, which shows 0 as "-".
      - The cells have a custom format that shows dashes for zero values.

      To check this, try setting the format of all cells to General.

  47. A workbook on which I'm collaborating downloads its data, which in turn are routed to a number of formulas. The data are correctly cell-referenced until row 1008, after which the formulas suddenly refer to a row 244 rows away, e.g, the formulas in row 1008 reference row 1252. Would you have any theories on this? I should point out that the A column has hard-coded row numbers down its rows, and the last entry is 1250 in row 1252.

    Thanks,
    Abbott Katz

  48. If i enter the number like 8.25 than get it as 83661 or 8.35.01
    Formula example.....
    A - B
    A = 74
    B = 61.750
    than Formula is 12.250
    But in wrong formula get it as 83361

  49. The =A2*1 formula just fixed my issue with formulas not working which had me stumped all morning - many thanks.

    Why on earth the data changes from one week to another is beyond me.

  50. I have a cell formula =M351+M364+M289+M144, but when I press F2, the result coming as "-". How to fix this?

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 :)