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

  1. Nope. All three of the above have been checked. Still doesn't calc. I have a formula in every cell in a column. I'ts a lookup. The key (cell keying the lookup) is formatted as number in both the source and target. No space exists before the formula. The formula is NUMBER. I updated the lookup array, and changed the lookup range for the top row - and the formula fails to execute FOR THAT ROW. If I copy the cell below to the cell that won't execute, it executes. If I then change the range of the lookup, it stops executing. If I change it back... it won't execute. I have copy/pasted values, re-formatted, entered a number in another sheet and copied formatting, etc. Same results: Formula won't calculate. And, if I copy paste from the cell immediately below at any time -it calculates. Change the range ($a$2:$a$6226 to $a$2:$a:6554) it doesn't calculate. If I manually change it BACK to 6226.... it STILL doesn't calculate.

    1. I am having the same problem....

  2. My excel spreadsheet won't accept a formula with the "&" in it. For example =A1+A2&" OK" and if A1=1 and A2=2 then the result should be 3 OK.
    This same formula works in another spreadsheet but in the spreadsheet I am work on it gives the result VALUE.
    Why does it work in one spreadsheet but not another?

  3. When I enter the formula"IF(C7=0,(B7-C7)/1),((B7-C7)/C7)) . The field is formatted for %. I get the wrong result for certain situations - for instance, if the number in C7=0 and the number in B7 is 2, the result s/b -200%, but comes back as 100%. What am I missing?

  4. Thanks!!!! Its very helpful.

  5. Hi all, i'm using excel for mac. currently when i am trying to build a formula, pressing "=" then selecting a cell, the cell becomes highlighted in the formula bar, so when I enter "+", the cell is overwritten by the "+" sign.

  6. I seem to be doing everything right, but Excel has suddenly stopped correct logical answers. It was working OK before, but changed about a day ago. If E4 = 21 Jun 19 and T2 -30 Jun 19, the formula =IF($E4>T2,"1","0") should return a value of 0. However, it is returning a value of 1. The cell formatting is 'General'. If I copy each cell without a global reference (ie use =IF(E4>T2,"1","0"), then it returns the correct value. However, I cannot do this; first off because it would defeat the purpose of using a tool like Excel and secondly, I would have to do it for thousands (yes thousands) of cells.

  7. I am looking for some help. I am trying to create a formula that give me a total of a cell that has a subtotal calculated times an amount minus 2 other cells minus that amount. I keep getting an error. This is what I have currently: =(G45*0.065)-[(H32,H33)(*065)]. Any ideas?

  8. Here's a helpful hint for Not Calculating item #3: Check to see if there is an invisible character before the equal sign by using the delete and backspace keys a few times.

  9. i have a problem in the below formula. its working fine for 4th month, and for 5th month its not working at all even though there is a data for it.

    if('CFD Ads'!$C:$C=month(today()),countifs('CFD Ads'!$D:$D,A38,'CFD Ads'!$I:$I,"99 Acres"),"")

    The same formula was working fine till yesterday(i mean for april), and from today (may) its not working

  10. Thanku formula it's working

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

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

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

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

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

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

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

  18. nycccc please send formule sum and vluck

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

  20. Thanks. It helps a lot.

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

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

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

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

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

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

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

  28. Thanks, problem solved!

  29. Thanks very helpful.

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

  31. Thank you so much!!!

  32. This is so helpful. Thanks a lot.

  33. Thanks! Very helpful tips.

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

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

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

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

  38. thanks....problem solved.

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

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

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

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

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

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

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

  46. Very helpful!

  47. Thank very much

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

  49. Thanks very helpful and it worked!

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

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