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

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

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

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

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

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

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

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

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

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

  10. Dear ,

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

    Thanks

    Arif

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

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

  13. Thanks very helpful

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

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

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

  17. Thanks!!

  18. very helpful tq much appreciated

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

  20. Thank you!

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

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

  23. Thanks. It is helpful

  24. thanks for the help

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

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

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

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

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

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

  31. Very helpful, thanks

  32. Thank you, could not figure out why all of a sudden formulas would not update. Very helpful!

  33. very helpful. Thanks.

  34. When I type =sumif( and then click on the first cell of a column and then hit Ctrl+Shift+down arrow, I get the error message "You've entered too few arguments for this function." When I hit Ctrl+Shift the computer makes a chime noise. Any ideas?

  35. I had cells formulated to time stamp when I entered information in the adjacent column. One day, when I opened up Excel, the time stamp no longer worked in any of the sheets I'd created; now it says "circular error." Why doesn't the formula work anymore on any of the files?

  36. Hi Svetlana Cheusheva
    I have the following data sheet where i have used the function =IF(D11:J11>4.5,"ALERT",IF(D11:J11>7.1,"ALARM","NORMAL"))
    but i am getting a error in this formula.
    Can you help me out.

    D E F G H I J
    11 #VALUE! 7.10 3.00 4.50 6.10 2.14 2.00 4.60

  37. Simple solution:

    1. On your problem sheet, high light and copy the entire area of your data.

    2. Open a brand new excel file.

    3. Paste value only to the newly opened Excel file.

    4. Test the sheet. I should work ok now.

    This was how I resolved the said problem.

    Thank you

    Rosalind

  38. Very useful article but I still can't seem to fix my problem. I am trying to add 40% to a column of numbers using this formula : =D5*(1+40%)

    Once I hit enter, the result is 0.00%

    What does that mean!?

    It's not a huge excel sheet but there is a lot of other formulas in a couple of columns, can that be the problem?

    1. One thing... Ensure the cell is not formatted for percentage... Otherwise, you would essentially be telling it to show a percentage of your calculation.

    2. Hi George,

      It's difficult to detect the source of the problem without seeing the workbook. The formula is correct, and you can make sure of this by testing it on a new sheet. So, most likely the problem is with cell formatting. Make sure the format of D5 and formula cell is set to General. Also, verify that the value in D5 is a number (sometimes numbers can be formatting as text and Excel perceives them as text strings).

  39. Svetlana Cheusheva
    I have the following data sheet where i have used the function =IF(D11:J11>4.5,"ALERT",IF(D11:J11>7.1,"ALARM","NORMAL"))
    but i am getting a error in this formula.
    Can you help me out.

    D E F G H I J
    11 #VALUE! 7.10 3.00 4.50 6.10 2.14 2.00 4.60

    1. D11:J11 is the error,

      try using Min(D11:J11) or Max(D11:J11)

      1. Also, if cell E11 has an error in it ( the #value! you wrote) then that error in the inputs will follow into your formula output.

  40. Really helpful. Thanks.

  41. Why is it that after I put in a formula there is a dash in the center of the cell instead of the answer to the formula?

    The formula I am using: =D12*ABS(H5)
    contains an absolute value function.
    Please advise.

    -Beth

    1. Hi Beth,

      It's very difficult to determine the source of the problem without seeing your source data. The first thought that comes to mind is that the result of the formula is 0 and that cell has a custom format to show "-" for zero values. To check this, try setting the format of the formula cell to General.

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