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

  1. m using any type of formula in excel(like Sum Add concatenate and more, I didn't get answer.
    excel sheet shows formula not excel

    exp: =F3&E3

    1. Hi pankaj,

      It seems you have the "Show Formulas" option enabled. Please go to the Formulas ribbon tab and check if the "Show formulas" option unpressed in the "Formula Auditing" group.

  2. how to write a formula for this

    if A and D are both less than 75: 0
    if A is greater than or equal to 75 and D is less than 75: Calculate (A — 75) = value.
    if D is greater than or equal to 75 and A is less than 75: Calculate (D — 75) = value.
    if A and D are both greater than or equal to 75: Calculate [(A — 75) + (D — 75)] = value.

    all conditions in single formula please help

    thank you

    1. Hi arun,

      You should use the following formula:
      =IF(AND(A1<75, D1<75), 0, IF(AND(A1>75, D1<75), A1-75, IF(AND(A1<75, D1>75), D1-75, A1-75 + D1 - 75)))

  3. I am using a UDF to sum a range based on their cell colour below:

    Function SumByColor(CellColor As Range, SumRange As Range)
    Application.Volatile
    Dim ICol As Integer
    Dim TCell As Range
    ICol = CellColor.Interior.ColorIndex
    For Each TCell In SumRange
    If ICol = TCell.Interior.ColorIndex Then
    SumByColor = SumByColor + TCell.Value
    End If
    Next TCell
    End Function

    This works fine, however the range I am using has conditional formatting set to change the colour. For some reason this script only recognises the cell colour if I manually change it.

    Am I missing something?

    Thank you for any help you can provide

  4. hi nigel,
    my formula =sum(e3:e30) is showing error#####
    what could be the problem

    1. Hi Ann,

      Excel displays hash marks if a cell is too narrow to display the value. If it's the case, simply make the cell wider.

      1. Hi
        Thanks very helpful

  5. I have a cell (F17) which calculates how many years between dates. The result of this formula needs to be looked at by an If function to return a value: =IF(F17=2,1,IF(F17=3,2,IF(F17=4,3,IF(F17=5,4,IF(F17>5,5)))))

    i.e if cell F17 is 3 years, return 2 etc

    It does not recognise the formula result in the cell calculating the years.

    Any solutions?

    1. Hi Nigel,

      Your IF formula is correct. And what formula do you use to calculate years (F17)?

      1. Hi,

        Just realised I should have used the DatedIf function. Just changed it and it now works. Thanks for getting back to me. I knew it had to be simple error on my part.

        Cheers

  6. Hi. Why do i get zero value when i find the sum of a bunch of number or sometime the display is"-"

    1. Hi!

      The most common reason why the SUM formula returns 0 for a bunch of numbers are numbers formatted as text. To check this, use the ISNUMBER() function. If it returns FALSE, it means your numbers are text strings, and you need to convert them to numbers as explained in "6. Make sure numbers are not formatted as text values".

      Zeros may appear as dashes because of the following reasons:

      - The Accounting format is applied, which shows 0 as "-".
      - The cells have a custom format that shows dashes for zero values.

      To check this, try setting the format of all cells to General.

  7. Very useful article but I still can't seem to fix my problem. I am trying to add 40% to a column of numbers using this formula : =D5*(1+40%)

    Once I hit enter, the result is 0.00%

    What does that mean!?

    It's not a huge excel sheet but there is a lot of other formulas in a couple of columns, can that be the problem?

    1. One thing... Ensure the cell is not formatted for percentage... Otherwise, you would essentially be telling it to show a percentage of your calculation.

    2. Hi George,

      It's difficult to detect the source of the problem without seeing the workbook. The formula is correct, and you can make sure of this by testing it on a new sheet. So, most likely the problem is with cell formatting. Make sure the format of D5 and formula cell is set to General. Also, verify that the value in D5 is a number (sometimes numbers can be formatting as text and Excel perceives them as text strings).

  8. Svetlana Cheusheva
    I have the following data sheet where i have used the function =IF(D11:J11>4.5,"ALERT",IF(D11:J11>7.1,"ALARM","NORMAL"))
    but i am getting a error in this formula.
    Can you help me out.

    D E F G H I J
    11 #VALUE! 7.10 3.00 4.50 6.10 2.14 2.00 4.60

    1. D11:J11 is the error,

      try using Min(D11:J11) or Max(D11:J11)

      1. Also, if cell E11 has an error in it ( the #value! you wrote) then that error in the inputs will follow into your formula output.

  9. Why is it that after I put in a formula there is a dash in the center of the cell instead of the answer to the formula?

    The formula I am using: =D12*ABS(H5)
    contains an absolute value function.
    Please advise.

    -Beth

    1. Hi Beth,

      It's very difficult to determine the source of the problem without seeing your source data. The first thought that comes to mind is that the result of the formula is 0 and that cell has a custom format to show "-" for zero values. To check this, try setting the format of the formula cell to General.

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