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

  1. Please fix my formula
    =COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Trade SS"})

  2. Thank you for your cooperation. This was very helpful for me.
    Have a nice day!

  3. I used this formula =HYPERLINK(INDIRECT(C3)) in cell d5 while c3 contain Sheet8! A4, but while i click on d5 it show error "cannot open the specified file" but while i click fx in formula bar it take to desired link place.

    Please help

  4. Query..
    I copied data from Power point
    Data pasted in excel sheet
    But..
    The data pasted in excel sheet in each cell after the value the one extra space is been showing,
    Because of this extra space the sum value cannot be calculated.

    For example.
    "30.5 "
    It should be only "30.5" no space required after the 5..

    How to solve this problem.????
    General done, number format done, trim formula used, find replace doesn't work..

  5. If your date is not possible, formulas won't work. For example, 11/31/2020 can be entered into a cell but it will result in a calculation error until updated to 11/30/2020. Only 30 days in November. Spent about a 20 minutes on that particular typo.

  6. Thank you!

  7. Thanks so much, very helpful!

  8. I have followed the instructions for a complex number format, e.g. COMPLEX(real part, imaginary part), in my Mac version 16.20, but Excel does not recognize that format. Any suggestions?

  9. Thanks a lot, quite useful! Specifically the point about formula "entered as text"

  10. Column A Column B
    Inv No. PC How Can I get the answer on column B by using
    Excel Formula
    18JH42A330EN00710076 A330
    18JH42A330EN00720077 A330
    18JH44A330RT00720086 A330
    18CH2S011S0047 S011
    18JK2T007G0111 T007
    18TN2O004G0280 O004
    18PM080154WB0648 PM08
    18P055N0365WB117 P055
    18A377C0531KA025 A377

  11. Thanks a lot

  12. Thank you for this! Very comprehensive list of things to check. My issue was that the formula was entered as text!

  13. I saved an excel file in dropbox so I cam access it at my home computer and as well as in the office. When I open the file in my home computer the formulas are all working perfectly but when I open it in the office and I change the value included in the data range, the formula then returns O. What should I do?

  14. I get this formula on the end all on "0".
    Being using it for months and then I saw it does not work.
    I also check all possibilities provided and tis buffels event expert in the field.
    What must I try next. I am stuck.

    mathem2 10 1.7 0-20% 0
    mhlongt6 11 1.8 20-40% 0
    rikhotn1 11 1.8 40-60% 0
    khumalz5 2 0.3 60-80% 0
    dladlabs 2 0.3 80-100% 0
    mtengws 8 1.3 >100% 0
    =COUNTIFS(CX1:CX116,">=0,01",CX1:CX116,"<=0,2")

  15. regret to inform you that i could not upload any workbook for reference. Pls help with the excel formula

    Salary Range
    Age 15-25 26-35 36-45
    18-25 1 2 4
    26-30 3 5 5
    31-35 4 81 1
    36-40 6 8 2

    Require excel formula when the age and salary is written the corresponding value with the range data of the table gets displayed in "Output"

    Example

    Input
    Age 29
    Sal 29

    Output 5

  16. Thanks helped

  17. when I do my spreadsheet, 25+22 = 47 but it shows ######

  18. That was very helpful. Thank you

  19. I use the SUP function in excel 2013 it was working well but at the end of the table is no longer give me the results, the cell still empty.

  20. Dear Sir, for 2 months i had small problem in Automatic sum Calculation, Today i rectified by your guiding,Thankyou

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

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

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

  24. Thanks!!!! Its very helpful.

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

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

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

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

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

  30. Thanku formula it's working

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

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

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

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

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

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

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

  38. nycccc please send formule sum and vluck

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

  40. Thanks. It helps a lot.

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

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

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

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

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

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

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

  48. Thanks, problem solved!

  49. Thanks very helpful.

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

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