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 2. Total comments: 188

  1. ='Opportunity Tracking'!$BN2*VLOOKUP('Opportunity Tracking'!$BV2&"|"&'Opportunity Tracking'!$BL2,CHOOSE({1,2},'FX rates'!$I$3:$I$38&"|"&'FX rates'!$J$3:$J$38,'FX rates'!$K$3:$K$38),2,FALSE)
    Doesn't work says N/A

    1. BN is a number column, BV is a date column, BL is a text, I3:I38 is the date range and J3:J8 is the match text column like BL, K3:K38 is a number column)

  2. Trying to create a sheet that when you select from a drop down of months - you auto populate rows/columns for day and day of week
    I have this working - However I had to go in and manually update every formula in every column to get this,
    I have a data column that is a date
    C1=DATE(YEAR(TODAY()),MATCH(A1,"January","February","March","April","May","June","July","August","September","October","November","December"},0),1)
    This pulls from A1 that is a drop down of Month -
    C33 is the serial number for given date. C33 = DATEVALUE(TEXT(C1,"M/D/YYYY"))
    A1/B1 is the drop down of months
    I would like A2 and B2 to look like [dd][ddd] from C33 So - A2 formula is =TEXT(C33,"dd") and B2 is =TEXT(C33,"ddd")
    simple enough - now to get A3 and B3 to increment one would be =TEXT(C33 + 1,"dd") this does work -
    However if I try to do the corner drag to populate all the rows for said month - it increments C33 to C34 C38 .... not the 1
    What am I missing here? I am sure it is obvious to the rest of you, but I can't seem to figure this out and have been mucking with this for a few hours!
    Thank in advance
    Bill

  3. Hi,

    When completing B the static date and time will populate in F. I use this formula: =IF(B1"",IF(F1"",F1,NOW()),""). The cell format in F is Custom -> dd/mm/yyyy hh:mm.

    It worked from last year until two weeks ago. Since two weeks ago, F is blank when I select anything in B.

    I managed to find this formula for a different sheet: =IF(AND(SIC!J2>=0,"",SIC!J20),NOW(),""). But it is not static and I need it to be like with the first formula when it worked.

    How can I fix it?

  4. I have an issue with my formula not updating the refence cells when dragged down the column.
    The formula works fine with all calculations, however, when I drag it down it does not update the reference cells so I get the same result for the entire column.
    In order to get the correct result I have to manually update the formula in each cell.

    =IF([Book1.xlsx]Sheet1!$J$3=100%,"Ready",IF([Book1.xlsx]Sheet1!$J$3=0%,"Not Started","In Progress"))

    I tried using this same formula in a brand new workbook to discard formatting issues and I got the same result.

    Any advice?

      1. I have the same issue but my cells do not have absolute values. When I drag whe formula down it does not update to the next line. This is the formula im using because i want to count differente cells that are not in a sequence and meet a criteria:

        =SUM(COUNTIF(INDIRECT({"C22","H22","M22","R22","W22","AB22","AG22","AL22"}),"x"))

        I have 146 lines, but when dragging down I need it to update to the following lines 23, 24 and so on.

  5. I'm trying to get ANY formula to work currently.
    In cell E4 I enter a simple test: =CONCAT(CQ4;CQ5)
    In cells CQ4 and 5, I have "abc" and "xyz", of type General.
    On clicking enter for this formula, it just displays the formula.
    Ensuring Automatic calculation on cell E4 does not work.
    Forcing the calculation does not work.
    Manual Calculation does not work.

    Formulas do however work for cells in other columns.

    There is no Protection or locking on the E column.

    Other simple formulas like =sum(1;2), also do not work in the E column.

    Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20446) 64-bit

    1. Nevermind... I just needed to format the E column to General or similar to make the formulas work.

  6. Didn't work Automatic calculations in my worksheet.i just tried to all the methods of above mentioned but it not corrected.

    1. Same situation with me here, but only 1 cell has a problem. Anybody to answer, please?

  7. I have formulas that work on one tab (year 2019) , and on the next years tab (2020) the formula works on 2 lines but the rest of the cells are coming back zero. All references seem to be correct since 2019 worked perfectly. They are all formatted as a number, and I can see the backup that the formula should be picking up. Any ideas on how to fix this? The formula is a SMIFS.

    1. That kind of error can happen when you copy from one sheet to another. To correct it, ensure you paste it in similar row and column as the sheet you copied from

  8. You are my savior!!!! Thanks a lot for the information! I have been suffering so many years because it was not calculating simple data, i had to click thousands of times the F2 button to put the numbers in the right format!

    1. Sir, what did you do ?

  9. I have a list of employee data and I have cells that are dependent on others others, I used formulas to calculate values like date of retirement, years spent in service and so on. My problem is I want to use conditional formatting on rows that empkjee is either 59 years or above or has spent 34 or more years in service. When I used =$H2>=59 I got the desired result,but $O2>=34,the whole sheet gets formatted. I tried combining the 2 conditions using =OR( $O2>=34,=$H2>=59) it still didn't work

  10. A list of numbers all derived from formulae (e.g. IMPRODUCT(B3,C3) will not sum. They are not text. The only numbers included in the calculation are those not derived from a formula. What is going on?

  11. Hi,
    I have a excel sheet which has a formula in one column say 'G' column, the formula(F2-E2) in the 'G' column is to substract values from two other columns. But the formula is not getting automatically applied on opening the excel, we need to click inside the 'G" column after that the formula gets applied.
    I tried changing format of the column 'G' to 'Number' and also tried Calculation option to 'Automatic', still its not working.
    Please suggest any other solution which can work

  12. A clarification on my previous comment. I create the formulas through a macro, using the RefersToR1C1:= option.

    I build the formula by putting code like this in a loop.
    YTDPointsVar = YTDPointsVar & "RC" & Range("Points" & TheWeek).Column & ","

    Then I build the formula with this code:
    ActiveSheet.Cells(TeamRow(TheTeam), Range("YTD_Points").Column).Formula = "=sum(" & YTDPointsVar & ")"

    I don't have the R1C1 reference style selected in File, Options, Formulas. In the past the above code would automatically convert to this style;
    =SUM($I2,$Q2,$Y2,$AG2,$AO2,$AW2,$BE2,$BM2,$BU2,$CC2,$CK2,$CS2,$DA2,$DI2,$DQ2,$DY2,$EG2,$EO2,$EW2,$FE2,$FM2,$FU2,$GC2,$GK2,$GS2,$HA2,$HI2,$HQ2,) and it would calculate properly. Now, using the same code, not in compatibility mode, it displays in the RC format and no longer calculates the formula.

  13. I have used formulas of the following style in a workbook since 1994: =max(rc13,rc21,rc29)

    These have always worked. I have been saving in compatibility mode until a week ago. I saved the workbook as a .xlsm file instead of a .xls file and no longer use compatibility mode. Now the formulas don't calculate. I have automatic calculate on and have selected F9, etc. When I select the "R1C1 reference style" option under "File, Options, Formulas" the formulas automatically change to something like this: R[-112]C[463],R[-104]C[463],R[-96]C463]).

    In my previous versions of the workbook I did not have "R1C1 reference style" selected and the formulas still updated properly. I'm running Excel 2013 on Windows 10.

    Thanks in advance for your help.

  14. My IF formula has worked no problem for 38 rows of my spreadsheet (adding a sequential number) but now no longer works and I believe I have tried every troubleshoot option above. When I compare the formulae the only difference is the row number which has been the same for the formula in every other row. Please can you help?

    =IF(C38="","",LEFT(C38,1)&"-"&RIGHT(YEAR(D38),2)&"-"&MONTH(D38)&"-"&WEEKNUM(D38)&"-"&RIGHT(A38,4)) - This formula works for row 38 & all previous rows and returns C-22-8-35-1036

    =IF(C39="","",LEFT(C39,1)&"-"&RIGHT(YEAR(D39),2)&"-"&MONTH(D39)&"-"&WEEKNUM(D39)&"-"&RIGHT(A39,4)) - This formula works for row 39 & returns
    C-22-4-17-1037

    =IF(C40="","",LEFT(C40,1)&"-"&RIGHT(YEAR(D40),2)&"-"&MONTH(D40)&"-"&WEEKNUM(D40)&"-"&RIGHT(A40,4)) - This formula does not work for row 40 & returns C-22-4-17- where it should return C-22-4-17-1038.

    Unique identifier
    The first letter is the source from column C.
    2nd two digits are the year (column D)
    Next two are the month (column D)
    Next two are the week (column D)
    Last is (should be) the next sequential number

    Thanks

    1. Hi!
      I can't check your formula because I don't know what is written in cell A40. The RIGHT function extracts the last 4 characters from this cell. It's your sequential number.

  15. Hi!

    I have a lot of data in my excel , around 160 000 rows and I have a few formulas in separate column that are supposed to read from the data. I am manually calculating it ,and for some reason it only calculates part of it and then I have to refresh and calculate again ,it happens on smaller samples as well and I never really know how many times should I recalculate to make sure it's fully calculated. Could you please let me know if you have any idea what can be causing it?

    Thanks in advance!

    Best Regards,
    Nino

    1. Hello!
      If you use the F9 key, then the formulas changed since the last calculation and the formulas that depend on them are recalculated.
      Try CTRL+SHIFT+ALT+F9 key. All formulas in all open workbooks will be recalculated, regardless of whether they have been modified since the last recalculation.
      You can read more details here: Excel calculations: automatic, manual, iterative.
      I hope my advice will help you solve your task.

  16. Dear Sirs:

    I have this formula
    =INDICE(Chiesa[Switch]|COINCIDIR(Instalaciones!$I10|Chiesa[IP]|0))

    if I edit it, for example, adding an extra space at the end, and then removing it, it will never be calculated again. although, formating to text, general, recalculate, etc...

    there is no choice for me to edit it and include an if.na , or other function.

    Please, do you have any ideas?

    Thank you

    Alfredo

    1. Hello!
      Please write the formula for the English version of Excel and explain the problem in more detail.

  17. I have at least two cells the formulae are not working in a .xlsm file. The formulae are in the cells but the displays are blank, and are on different worksheets. In both cases the adjacent formulae, the only difference being the column/row numbers, are working as intended.

    The simplest of the two formulae is =IF(MAX(Y14:Y17)>NOW()-$B46,MAX(Y14:Y17),0)

    Automatic calculation is set, format for the formula below is set to date (and number on the one below).

    I had been trying to find why the following formula wasn’t working for a couple of days, when today I realised that the date formula above also wasn’t working now.

    =INDEX(Nation2!G$4:G$9999,MATCH(INDIRECT("A"&ROW()),Nation2!B$4:B$9999,0))

    I knew that the first formula was working yesterday. For the last three months I have been saving this file as a back-up yet, when I opened two of the back-ups, the corresponding formulae in those two cells were no longer working correctly.

    On the second formula, the ‘A’ cell value date is ‘31/12/22’ [UK date], and there is one ‘31/12/22’ date in the ‘B’ range, and the corresponding ‘G’ value was not being displayed. However, if I changed the ‘A’ and ‘B’ dates to virtually any other date, the correct ‘G’ value would be displayed. This is true if the dates were any month end bar Dec 22 (including Dec 21 and Dec 23), and having seen it work with ‘15/12/22’ I changed just the ‘15’ element but to no avail.

    1. Hi!
      Unfortunately, you didn't explain and I can't guess what doesn't work in your formula.
      If in column B the desired date is in position 4, then the fourth value from column G is returned.

  18. I have a workbook with many different sheets. On one sheet (Master Project List), there are 17 columns and 365 rows of information. I have multiple other sheets that go through this list of projects and separate them into the correct ones (i.e., work plans, under budget projects, over-budget projects, etc.).

    The problem I am having is that if I delete a row from the Master Project List sheet, the data on those other sheets disappears (because it can't reference the cells in the rows that I deleted), and I have to manually copy and paste them back in for it to show data again. (refreshing the calculations doesn't work).

    FORMULAS USED ON WORK PLANS SHEET (for example):
    Column A: =IF('Master Project List'!C2="Work Plan",TRUE,FALSE)
    Column B: =IFERROR(INDEX('Master Project List'!A$2:A$674,SMALL(IF($A$2:$A$1160,ROW($A$2:$A$1160)-ROW($A$2)+1),ROWS($1:1))),"")

    Is there any way to fix it so that when I delete a row on the Master Project List, the cells with TRUE/FALSE can change their reference cell to the row below it (or just delete themselves?

    1. I've been using this workaround:
      To "delete" Row 25 from 'Master Project List'
      Highlight Row 26 through the last row that you're using, PLUS the next row or two. This will ensure that the last (previously populated) row doesn't become a duplicate of the 'new' last row.
      (If the last row you're using is 325, you'll highlight rows 26 thru 327)
      COPY
      Select cell A25
      PASTE
      That will keep all dependent formulas intact.

  19. Date Day Time In Time Out Time In Time Out Total Hours
    5/24/2022 Tuesday 08:05AM 12:00PM 07:00PM 12:01 AM #VALUE!

    I used this =(E9-D9)+(H9-G9) Formula. But my computer didn't work. I Go to Region-Additional Date & Time- number-list operator. But didn't work formula.

  20. I have a budget spreadsheet with a separate sheet for each region and a totals sheet.
    I want to calculate a simple percentage of the total budget used year to date - on the sheet itself it should simply be F2/G2 = xx% - but it always shows 0% as the answer.

    F2 is a sum of B2+C2+D2+E2 (and each of those values pulls from another sheet per row)
    G2 is simply pulling from another cell reference

    so effectively it is a formula calculating cells that are also results of formulas. (of course if I copy the data into a new excel file and the values become absolute, it is a very basic formula that works.

    can this work?

  21. I have Office365 Excel and my formulas don't work at all. Is there a security issue I need to fix? Getting a string value from Column A in Column Z, I used =A365 shows as = [@[COL_A]] not the value from Column A. As well, using

    =XLOOKUP($C:$C,'ExcelSheet2.xlsm]Exceptions'!$I:$I,'[ExcelSheet2.xlsm]Exceptions'!$N:$N,"No Match",0,1)

    does not convert to the expected display value but simply shows the formula itself.

    1. Hello!
      The @ symbol means an absolute structured reference to the current row.
      I can't check the formula that contains unique references to your workbook worksheets.
      However, it is written not quite correctly. Try like this -

      =XLOOKUP($C:$C,[ExcelSheet2.xlsm]Exceptions!$I:$I,[ExcelSheet2.xlsm]Exceptions!$N:$N,"No Match",0,1)

  22. I have a cell with =ROUND((2.83 * 1.02264 * 39.2/3.6)*A8,2)&"kwh"

    If I reference this cell to perform a calculation, I get #VALUE

    But if I use =ROUND((2.83 * 1.02264 * 39.2/3.6)*A8,2)&"123"

    It works fine, I thought that what is between the “” was just a sort of tag, and shouldn’t affect calculations

    If it can’t be done then that’s not a serious problem, just would like to understand why it’s happening.

  23. I have a active-X control button which runs a routine to change values of cells in my spreadsheet. I had a problem with this not working correctly (my programming error) and my code broke in the middle. I had set auto-calculate off and back on again using the statements below at varying points in my code to make it run faster. Now the auto-calculate does not work. I tried turning it back on with the Toolbar at the top and also using the Formula tab in File-Options. Neither one worked. There are big holes in my spreadsheet which do not update now and I can't seem to fix it. Even forcing calculations with the Toolbar option or by just using F9 or even Shift-F9 will not work. I saved my file and re-opened it but nothing changed. Is this a known issue with Excel?

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True

  24. I have been using the =getformula for a while all out of the blue it stop working i get the message #Name?

    Before I used to just type =get it would pop up.

    Regards
    Chris

    1. Hello!
      I'm assuming =getformula is a custom function. The workbook or add-in in which it is written must be open. Only then will it work.

  25. I came here looking for an answer to the issue of the fact that I just noticed that my Excel spreadsheet is not automatically updating my totals and is maintaining old data in cells after I have changed the data. I have noticed that if I go to the print screen and then back my total is now correct. My formula is really simple =Sum(J18:J19)

    The fix recommendation of making sure my Formulas, Options, setting is set for "Calculate Automatically" has been tried...no different result. Our IT department has even went the extra step and uninstalled my Excel and reinstalled but still the same issue.

    Does anyone have any other ideas on what is wrong and a fix?

    1. I had this issue as well. I'm not sure why this worked, but I protected the sheet, then unprotected it again. Now all of the formulas are automatically calculating again.

  26. i want to calculate birthday year and formula it s doesen't working

    =datedif(B2,Today(),"Y")

  27. Hi,

    I have 12 sheet in a workbook, I want to 10 sheets to calculate one time (manual) per day and other 2 sheets to calculate automatic.

    if all 12 sheet calculation automatic, it will be taking time

    1. Hi,
      The calculation order is set in Excel options and applies to all workbooks and worksheets without exception.

  28. Hi, I'm trying to collate attendance to meetings that are being run each day. My colleagues are generating a 'Register' spreadsheet from a meeting attendance download in TEAMS. It just gives three columns of Name, Action, Date/time stamp. This is changed from a CSV to Excel Workbook.
    My 'Attendance' Excel work book has the following formulae:
    =IF(IFERROR((COUNTIFS('filestructure\[2021-01-11 P2 Po Hs.xlsx]meetingAttendanceList (2)'!$A:$A,A105,'filestructure\[2021-01-11 P2 Po Hs.xlsx]meetingAttendanceList (2)'!$B:$B,"Joined")),0)>=1,1,0)
    If I open the 'Register' my 'Attendance' calculations work but, if the 'Regsiter' is closed I just get 0 for attendance when it should be 1
    I have to link into a lot of different 'Registers' for each day and am currently opening all the registers in order to get some figures into my 'Attendance' spreadsheet. What have I done wrong???
    I've got the 'Attendance' and 'Register' spreadsheets in the same folder on our network. It's a lengthy location so I've reduced it to "filestructure" for your sanity. The fact that it calculates it when I open the register tells me it can find it. Very confused - HELP!!!

    1. Hello!
      If I understand your task correctly, your formula uses data from a separate file. She can get this data only when this file is open in Excel. If the file is not open in Excel, then the data from it cannot be used in formulas.

      1. That's right.

  29. I'm having the same issue as Abby - I can't get a simple AutoSum formula to work - it just shows a zero. I've tried both ways (=B21+B40) and =(SUM(B1, B40). And when I click on prior AutoSum formulas that worked perfectly, they reset to zero.

    1. Having the sane "autosum not adding anything up and only giving zero as result" problem here, Used to work in previous versions. No idea why now not working

  30. Hallo,
    Could you help me with this formula, please?
    =IF(AND(F14=12),"Continue",IF(AND(F147),"Continue",IF(AND(F14>70,F22>=12),"Rethink","Cancel")))
    In place where supposed to appear "Rethink" stubbornly apeears "Continue". I tried many different options but even when I delete everything and left only two parts and "Cancel" as false statement , it showed me "Continue"
    There are 5 columns for this formula. Four are working good. All changes in figures cause changes in results - the text. The last column resists.This part : IF(AND(F14>70,F22>=12),"Rethink" is designed exactly for this column to met all condition. I tried everything I know and it is not working. Could you help me with this - why it is happening? I checked settings in my excel and everything is ok

    1. Hello!
      Your formula is incomplete. Try to insert spaces before and after the > and <. Then it will be recorded in full.

      1. Hallo, Thank you for response, Btw I just noticed I made the mistake when I was copying my formula to show you, here is right one =IF(AND(F1412),"Continue",IF(AND(F147),"Continue",IF(AND(F14>70,F22>12),"Rethink","Cancel"))), I added spaces as you advised and got info that typo was found and excel corrected it back with no space :( still not working

        1. Hello!
          Unfortunately, I cannot figure out your formula. I forgot to say that spaces should not be inserted in Excel, but in the comment itself. If possible, describe in words the conditions in the formula to make it easier for me to understand how it works. Thank you.

  31. Hi,
    I have an own function, eg :

    Function Pipo( nRow as integer, nColumn as Integer, nVal as Integer )
    Cells( nRow, nColumn+1).Value = nVal+1
    Pipo = nVal
    End Function

    -> Update next Cell with value : nVal+1
    -> Returns : nVal

    The cell it is used in in looks like : =Pipo( ROW(), COLUMN(), 9 )

    However the return in "#VALUE" and the next cell is not updated.
    It is the Cells function that is causing this error. Even using Range() instead does not work.

    Any ideas?

    G,
    Marco

    1. Well, found it -> updating a cell only works from within a Subroutine.

      Case closed.

  32. was using excell all was ok now suddenly any new formula eg sum of 2 cells or sum of row, and all gives a $ - result. how can i fix this?

  33. I am trying to add a simple column of numbers and sum it up in a separate cell - like I've done for 20 years. I am typing : =SUM(highlighting the cells).... It simply does not add up the result, just showing 0! Ditto any other function like AVERAGE.I followed your tips to no avail. I am using licensed 365 Excel subs. (Also how may I simply clear all formatting from all cells? My cells are all General.

  34. I am trying to use IF function to determine if numbers in two cells are the same. I think the issue is one of the cells in the comparison is from a calculation. i.e. Cell A ($0.14) is a hard number typed in, Cell D is calculated from numbers in cell B and Cell C (B-$31.21*0.45%).
    The IF function will only work if I switch the "if true" and "if false" - that is completely backwards and doesn't make sense to do. Any guidance is appreciated.

      1. You just saved my sanity with that answer :-)

  35. In my current position, I have to examine data in spreadsheets.
    One of the most current problems I have is WHEN I insert column to grab a DAY (for example), it comes back MM/DD/YYYY instead of DD. I have to format that column to GENERAL to just get DD

    1. Unfortunately, Excel always copies the format to a new column or row from neighboring ones. This can only be changed using the VBA macro.

      Excel includes a feature that allows you to modify how you insert rows, columns, or cells in your worksheet. When you insert any of these, Excel displays a small, floating "button" right near the inserted row, column, or cell. The button, called "Insert Options," has a small paintbrush on it, similar to the Format Painter tool. Click the button, and Excel displays some options about how the inserted row, column, or cell should be formatted.

  36. Hi,
    Can you please help.
    Here's my scenario.
    I have a value in cell C1 let's say 5 , this cell value is an output of this formula =TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:30")),1)+0,"")) because I want to get the numeric value only from the other cell. And now in cell D1 I have a value let's say 3 this is an extracted value from previous result. So now in cell E1, I want to match cell C1 and cell D1 if they match or not, so I used this formula =IF(C1=D1,"Match","Not Match"). My problem was it did not get the correct evaluation, all results are "Not Match" even there are values that are Matched. I tried the remedies mentioned but still didn't worked. Thank you.

    1. Hello!
      In your example, you compare 5 and 3. And they do not match. You did not specify real values. I can assume that you are comparing text and number. Perhaps there are extra spaces, non-printable characters in the text. I recommend checking for extra spaces and non-printing characters, as described in this article.

  37. 1. Go to formulas tab, and uncheck the button "Show Formulas". It will show the result in cell instead of formulas. 2. Change the cell format into General -> double click on that cell and enter again (rewrite the formulas)

    1. OMG! Just this trick would work. Thank you!

  38. When I calculate a formula say subtraction:
    E.g x-y, x has multiple values(x-> 1-n) but y is constant (y-> 1234) and has a blank column below it, after calculating the first one manually and then dragging it so as to get the same calculations done quickly it subtracts the blank boxes and i get the result as x only and not x-y.
    What to do?

  39. So Frustrated!
    Simple SUM formula - have tried to go at this multiple different ways still the same result.
    - SUM a column *click the top and drag to the bottom and click the SUM function - shows a zero balance of $-
    - type =SUM(H3:H203) Same issue it shows a zero balance of $-

    The sum should be close to $130,000.00 so why is this happening?? I have made sure there are no apostrophe or anything when typed. I could really use someones help as I am trying on another column as well and the same issue arises.

    1. Hi Michelle,

      You can use the ISNUMBER function to check if your values are numbers. For example:
      =ISNUMBER(H3)

      If the above formula returns FALSE, that means a number is stored as text and you need to convert text to number.

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

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

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

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

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

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

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

  47. I have an SQL query that I run in SQL Management studio and then paste into Excel. Parts of that query include some excel formulas like VLOOKUP(...) or Hyperlink(...) as well as data from the database.

    When I copy and paste these rows into excel, the formulas evaluate just fine. But now I am trying to build that query into the spreadsheet using data connections and the formulas are not being treated as formulas. The cells are formatted as General. The formulas start with =. If I click on the formula cell, pretend to edit it (making no edits) and then press enter, it calculates. But I don't want to do that twice per row ~ 1000 times.

    1. Found a workaround. Do a find-replace of = with =
      That triggers the calculation. But it still isn't a permanent fix.

  48. Hi!
    Im trying to get a result on these formulas:

    =DATEIF(C2<=D2,"On Time","Late Arrival")
    =IF(C2<=D2,"On Time","Late")

    But I get an error message. What could be wrong? I have dates in date format (2016-12-22) in C2 and D2. My aim is to compare to dates and generate the text On time and Late as a result.

    Thank you a lot!

    1. Hi Jenny,

      In Microsoft Excel, there is no DATEIF function. You probably meant DATEDIF, but it is designed for finding a difference between 2 dates and has a different syntax, please check here.

      Your IF formula is correct and works just fine for me. Exactly what error message does it throw in your sheet?

  49. hi dear,

    i have problem with my formula.
    when i key in =Sum(D20+6%), the answer will not appear the correctly

    for example D20 amount is $30.00

    =sum(D20+6%)
    answer : 3006.00%

    **the answer should be $31.80**

    how can i solve this problem? TQ ^_^

  50. I have the formula in my excel below:
    =IF(ISERROR(AD43/SUM(IF($C$9:$C$36>0,IF($B$9:$B$36=2,1,0),0))),0,AD43/SUM(IF($C$9:$C$36>0,IF($B$9:$B$36=2,1,0),0)))
    It is made to grab the average of certain cells. When I click on insert function the formula result is giving me the value of 17.5 which is correct. However the actual cell on the spreadsheet is showing a 0. I cannot figure out why it is doing that. Any help would be great.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)