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

  1. can anyone help me to resolve my query. I want to sum some numeric values by using excel formulae having some special charecters. for example 20+14@+36+17*+16+23? where @ means condone marks and * means fail in that particular subject

  2. I use the =AVERAGE(IF(....)) formula.
    When I click the fx button, the result is correct. Unfortunately the output into the cell is incorrect!!!!!!

    1. Thank you for your comment, Maria,

      Could you please specify the entire formula you're using in your cell?

  3. I have a formula. It works. I copy down in the sheet. It works...up to a point. From that point on it returns the same value regardless of the inputs. The formula doesn't change:
    This works:
    =IF('Formal Labs'!L46="","",VLOOKUP(VLOOKUP(A46,'Formal Labs'!$A$4:$L$48,12),Parameters!$L$2:$M$15,2))

    This doesn't (and any others after L47)
    =IF('Formal Labs'!L47="","",VLOOKUP(VLOOKUP(A47,'Formal Labs'!$A$4:$L$48,12),Parameters!$L$2:$M$15,2))

    Its on automatic calculation, I've recalculated. I've formatted all referenced cells to the same categories.

  4. Hello -

    I'm running Excel 16.20 for Mac (Office 365). My cells are all text. I have this function:

    =IF(A3=A2,C2&", "&B3,B3)

    The result when true is only the contents of C2. I don't get the , B3.

    Any help is appreciated, thanks!

    1. I wrote that my cells are all text, actually just the contents are text. The format of the cells is General. Thanks.

  5. =IF(B5>'New Input'!$C$36, (((B5-'New Input'!$C$36)*'New Input'!$F$36)+(('New Input'!$C$36-'New Input'!$C$35)*'New Input'!$F$35)+(('New Input'!$C$35-'New Input'!$C$34)*'New Input'!$F$34)+(('New Input'!$C$34-'New Input'!$C$33)*'New Input'!$F$33)+(('New Input'!$C$33-'New Input'!$C$32)*'New Input'!$F$32)), IF(B5>'New Input'!$C$35, (((B5-'New Input'!$C$35)*'New Input'!$F$35)+(('New Input'!$C$35-'New Input'!$C$34)*'New Input'!$F$34)+(('New Input'!$C$34-'New Input'!$C$33)*'New Input'!$F$33)+(('New Input'!$C$33-'New Input'!$C$32)*'New Input'!$F$32)), IF(B5>'New Input'!$C$34, (((B5-'New Input'!$C$34)*'New Input'!$F$34)+(('New Input'!$C$34-'New Input'!$C$33)*'New Input'!$F$33)+(('New Input'!$C$33-'New Input'!$C$32)*'New Input'!$F$32)), IF(B5>'New Input'!$C$33, (((B5-'New Input'!$C$33)*'New Input'!$F$33)+(('New Input'!$C$33-'New Input'!$C$32)*'New Input'!$F$32)), (B5-'New Input'!$C$32)*'New Input'!$F$32))))

    not working as i expect, Excel doesnt compare the values and take the decison, it keeps skipping to the FALSE value and calculates, (B5-'New Input'!$C$32)*'New Input'!$F$32) this part!

    i dont even know what to do!!

    please let me know if there's a problem with my formula!!

  6. My problem here is my sum formula does not give correct answers to the subsequent row but it gives the same result while being scrolled. what is the problem?

    like
    2 4 5 6 17
    1 3 6 2 17

    what is the problem here?

  7. You solved my "Excel formulas not updating" problem.
    Thank you very much xx :)

  8. Thanks, problem solved!

  9. Thanks very helpful.

  10. i love you! it helped me with my problem. :D

  11. Thank you so much!!!

  12. This is so helpful. Thanks a lot.

  13. Thanks! Very helpful tips.

  14. Activity Code Pipe Size Thickness Qty in CUM Qty in SQM.
    1 0.5 25 0.0182 1.1200
    1 0.5 40 0.0054 0.2228
    1 0.5 50 0.0056 0.1905
    1 0.5 65 0.0264 0.7130
    1 0.75 25 0.0061 0.3614
    1 0.75 40 0.0091 0.3620
    1 0.75 50 0.0000 0.0000
    1 0.75 65 0.0936 2.4614
    1 0.75 75 0.0168 0.3886
    1 1 25 0.0229 1.3100
    1 1 40 0.0065 0.2494
    1 1 50 0.0066 0.2095
    1 1 65 0.0301 0.7700
    1 1 100 0.0293 0.5133
    1 1 125 0.0311 0.4452
    1 1 150 0.1296 1.5711
    1 2 50 0.5300 1.0000
    1 2 65 0.4000 1.2000
    1 2 75 0.0223 0.4625
    1 2 100 0.0574 0.9322
    1 2 125 0.2000 0.5000
    1 10 150 0.3000 0.6000
    1 10 300 0.4000 0.7000
    1 12 50 0.5000 0.8000
    1 12 100 0.2000 0.9000
    1 12 200 0.3000 0.6000
    1 20 75 0.4000 0.7000
    1 20 150 0.5000 0.8000
    1 20 300 0.3000 0.9000

    and criteria are as below:
    Activity Code Pipe Size Thickness
    1 0.5 25
    3 0.75 40
    7 1 50
    4 1.5 65
    5 2 75
    3 80
    4 90
    5 100
    6 125
    8 150
    10 175
    12 200
    14 225
    16 250
    18 300
    20
    up to 96

    How to sum of cum or sqm of specify activity, pipe size and thickness.

  15. Hi, I have a formula that looks at cell B1 to see if it's blank, if it is, it takes the value from cell A1, but if it isn't it takes the value from cell B1....that's not the problem.

    My cell B1 is blank, so should take cell A1, but the formula does not bring back the value (formula cell stays blank), unless I go to cell B1, hit F2 and enter. My calculation option is set to Automatic...

    Why is this happening and what can I do to fix it?

    Thanks

  16. Thank you! I thought I was going crazy!

  17. Hi. really hope you can help me. i want to copy a formula into a column that will multiply cell a3's formula down the column with result being sum of each row. i'm getting a3's result down the column not calculating each row appropriately.

  18. thanks....problem solved.

  19. Common things listed on other sites. I have functions that will not update unless you click in the formula line and hit enter (super simple COUNTIFS function based on some table data). There are no errors, they will just not calculate, period. This seems more like a problem with Excel.

  20. What a relief! Calculation was changed to manual instead of automatic... I was ready to cry! Thank you so much!!

  21. Hi, wondered if you can help, i have a spreadsheet. Edited the formula and its now displaying as zero even though the returned value should not be zero. Then i went and changed a figure of the precedents to get this to work and it fixed the orginial formula but now this one does not. tried updating sheets and cell formatting and no luck....any ideas? thanks

  22. If function does not update or calculate easiest trick is to create function to new blank excel and copy it from there to existing sheet. This method copies all formatting as needed.

    1. Thank you this helped me solve my problem. The new spreadsheet displayed the same issue, so I changed the formula.

  23. thanks kindly, correcting to Auto Calculation, which i had mistakenly turned off, repaired my problem.

  24. Thank you so much! I have been having this problem on and off for years. So helpful.

  25. I have same issues as above but, my calculation options are set to automatic and my formatting is not on text - it is set to percentage. This formula works on all other years data except for 2018 data; it will not automatically populate. HELP!

  26. Very helpful!

  27. Thank very much

  28. Wow, thank you - Calculation setting has been changed to Manual instead of Automatic ! Was about to scream before I read this ! 2 minutes later all sorted ! Thank you !

  29. Thanks very helpful and it worked!

  30. Solved after much trauma. I thought I was going mad.
    I also have been running Wordperfect Quattro pro and having the same problem there and am about to investigate that.

  31. Hooray, thank you search engines and thank you for your article in solving my problem.

  32. When I updated the cell with new values the cell continued to show the old value. After reading this tutorial, I fixed it in 30 seconds by restoring Calculation Options back to automatic. Thank you!

  33. None of this applies. Excel will evaluate the formula once and then stop.

  34. Thank you!

  35. none of the 3 above advises are working I still have the same problem that the formula is not changing to text nor numbers... can someone help please?

    1. Great Help!

  36. every time i change the number the font corrupted

    N.B the font is Arabic

  37. very useful
    thanks a million

  38. very helpful! & useful

  39. thanks that is very usful

  40. In my worksheet I continuously add a range of cells (not the entire column) using the sum formula, however if I insert a new row below my cell range but above the sum total cell, it does not recalculate the sum total. How can I update the sum total without having to adjust the cell range each and every time I insert a new row below the cell range/above the sum total cell. Thank you for your time. Victoria

    1. Hi Victoria, try formatting the entire range as a table, that way, Excel should automatically add the new row to the table, and then update the sum. The formula for the sum would look something like this =sum(Table1[Column1])

      1. I was having trouble with the upper formula not showing results of the formula. I updated the worksheet to a table as mentioned above and it worked!

  41. Wow - very helpful! With 3 clicks my problem of cells not automatically updating was fixed. Thank you!!!

  42. Hi,
    I've created this formula, trying to pull out a data from a table to another table
    there is no indication that there is an error in the formula but I get the wrong results, while testing the formula with F9 gives me correct results, any idea how to fix it?
    =IF(ROWS(F$17:F17)=$G$12,INDEX(A$2:A$62,SMALL(IF($G$11=A$2:A$62,ROW($A$2:$A$62)-ROW($A$2)+1),ROWS(F$17:F17)),ROWS(F$17:F17)),"")

    the result I get is the value in A17 while the correct answer should be A6

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  43. I have a excel sheet with formulas which was delivering correct results before upgrading my windows.

    After upgrading latest updates the sheet is not giving correct answers.

    I have checked the formulas they are same.

    Same sheet delivers proper answers on other systems.

  44. Hi!
    When I'm using Excel and want to enter a function, I typ for example "=S" and then Excel lists all the funktion options for me. The problem is that when I select the wanted function and press enter it leaves the cell with only "=S" and moves to the cell below. How do i fix this? I want to press enter so that the funktion is selected and i can start plugging in values faster.

    1. Hit tab instead of enter to selection the function.

  45. Thank you for sharing this information. this was really helpful.

  46. Hi,

    Why any functions are not working in my Excel sheet.This data copied another sheet.

  47. Good Morning
    I have a problem with formatting. I have and appointment start date and time in Cell H5, which by using the formula =TEXT(H5,"hh:mm")in cell K5, I have the time only, it is the same for the appointment end time. I now need to see if the appointments start and end within a particular frame. By using =IF(AND(K5>=O3,AND(M5<=O4)).
    The formula works on a practice sheet where I have typed the appointment times in, but not with the data where the time formula is. Can you help please?

  48. Thank you! Yet another save on your part.

  49. Hi Everyone, I'm trying to work out why one cell is not giving the right answer when every other cell is...
    I've triple checked the formulas to see if they are consistent & they seem to be.. any help would be greatly appreciated.

    This is the formula:
    =IF($D7='Vehicle List'!$B$4,LOOKUP(F7,'Delivery Rates 010717'!$A$3:$A$529,'Delivery Rates 010717'!$E$3:$E$529),LOOKUP(F7,'Delivery Rates 010717'!$A$3:$A$529,'Delivery Rates 010717'!$F$3:$F$529))

    Breakdown:
    D7= Vehicle registration number
    'Vehicle List' B4:B13= List of vehicle registration numbers
    'Vehicle List' C4:c13= Lists whether the rego in column B is a SEMI or TRAY truck
    F7= Suburb
    'Delivery Rates 010717'A3:A529= List of suburbs
    'Delivery Rates 010717'E3:E529= List of prices if D7 is a TRAY truck
    'Delivery Rates 010717'F3:F529= List of prices if D7 is a SEMI truck

    The formula needs to take the Tray or Semi price from another spreadsheet(Delivery Rates 010717 ranging from E3:E529 or F3:F529)but the same workbook, depending on what value is entered into F7.
    F7 will match a cell in Delivery Rates 010717 A3:A529 then should correspond with the relevant value in either column E or F.

    But for some reason it is returning a value from a completely different row in Delivery Rates 010717 rather than the row that matches.

    Hard to explain, so I hope this makes sense!!

  50. Hi to everyone. I have a question about the use of function in Excel. I am trying to use the vlookup function, and I am sure it is set up correctly, but the result for no reason is the same for all the rows, which is strange... Unless I hit for each Enter individually, then it calculates correct. Can you help please? Thanks to everyone in advance.

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