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 5. Total comments: 463

  1. Thanku formula it's working

  2. I have created a formula that works well. However, I get my data from a downloaded excel file. When I paste it into my spreadsheet and run my formulas they give an incorrect result. But, when I double click into the cell and then click out of it, it updates correctly. When I look at the number formats for the cell before clicking into it, it shows the same value for every selection. How do I fix without having to click each cell independently?

  3. The formula for the sports spreadsheet listed above is {=sum(($A$5:$A$27=Al18)*(ISNUMBER($B$5:$B$27)),($C$5:$C$27=Al18)*(ISNUMBER($D$5:$D$27)))}

  4. Hi Guys
    I am using a formula based spreadsheet to help with team sport fixtures and have just increased my fixture listing which needs an amendment to the games played. I am trying to modify the cells its looking for to increase from A27 to A32 BUT any attempt at changing the formula translates to losing the end parenthesis {} from the formula and it then does not recognise it at all. I have tried dragging rather than correcting in the formula, what I am missing please its excel 2013
    Thanks

  5. Hi there....i've just upgraded to 365 and tried to do something in my file that i did with the 2010 version but having no success.....
    We use excel for our check register.... we enter the amount in one cell., and the category number in one.....then i (used to) check enable macros
    and Control C to spread the amounts to each category that are listed horizontally(columns)
    i have no way of doing that now...there's probably an easier way to spread out the figures so they match the totals????
    HELP!

  6. This was so helpful. My formulas calculation were changed to manual and I don't remember how. This was lifesaving. Thanks

  7. Hello, I am trying to calculate the total price of an item but the formula produces a slightly inaccurate result. The formula shows:
    QTY ITEM COST TAX SUBTOTAL TOTAL
    26 shirts $28.95 $2.90 $31.85 $827.97
    The correct TOTAL should show $828.10. Can anyone help please?

  8. I have entered the following formula:
    =IF(AND(F3>TIME(6,0,0),F3<TIME(22,0,0)),"TRUE","FALSE")
    F3 = [=Now()] to return the time (Cell is formatted to show time (00:00:00)
    at 10:29:00 why does my formula return FALSE? when it should be TRUE...

    What I require:
    I want the cell with the formula in to return a figure when the time of day is between 6am and 10pm and another if it is not (Ie between 10pm and 6am)..

    Anyone.....please help!!

  9. nycccc please send formule sum and vluck

  10. To extract due date between two given dates, I have used the following array formula
    {=SMALL(IF(($G$5:$G$7=$P$1), IF(($J$5:$J$7=$P$1), ($G$5:$G$7+$J$5:$J$7), "A")), ROWS($A$5:A5))}
    The due date is extracted from two distinct date column i.e. G and J
    The result evaluated #NUM!
    Please help to rectify the problem.
    Thanks

  11. Thanks. It helps a lot.

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

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

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

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

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

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

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

  19. Thanks, problem solved!

  20. Thanks very helpful.

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

  22. Thank you so much!!!

  23. This is so helpful. Thanks a lot.

  24. Thanks! Very helpful tips.

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

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

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

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

  29. thanks....problem solved.

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

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

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

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

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

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

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

  37. Very helpful!

  38. Thank very much

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

  40. Thanks very helpful and it worked!

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

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

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

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

  45. Thank you!

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

  47. every time i change the number the font corrupted

    N.B the font is Arabic

  48. very useful
    thanks a million

  49. very helpful! & useful

  50. thanks that is very usful

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