Closed Thread
Results 1 to 14 of 14

formula does not calculate

  1. #1
    TaiwanSwede
    Guest

    formula does not calculate

    Help!

    Something is weird with my Excel sheet. When I enter a formula, for example
    =AB2+AB3
    Excel does not calculate the result. Instead the formula just stays as it
    is. I am sure there is some strange option that has been chosen by accident,
    but I can't come up with where.

    Any help out there!




  2. #2
    Stefi
    Guest

    RE: formula does not calculate

    Check cell format! It is probably set to Text. Set it to General!
    Regards,
    Stefi


    „TaiwanSwede” ezt *rta:

    > Help!
    >
    > Something is weird with my Excel sheet. When I enter a formula, for example
    > =AB2+AB3
    > Excel does not calculate the result. Instead the formula just stays as it
    > is. I am sure there is some strange option that has been chosen by accident,
    > but I can't come up with where.
    >
    > Any help out there!
    >
    >
    >


  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Also check your options. Auto Calc maybe switch off

    Tools > Options > Calcualtion Tab then click Auto Calc

    VBA Noob

  4. #4
    Max
    Guest

    Re: formula does not calculate

    > Check cell format! It is probably set to Text. Set it to General!

    Just a little add-on to Stefi's note above (and as hinted in Steel Monkey's
    response) ... The formula needs to be re-confirmed* before it'll work after
    re-formatting to General (or Number). *Eg: click inside the formula bar,
    press ENTER. The re-formatting alone will not fire the formula.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  5. #5
    Muhammed Rafeek M
    Guest

    RE: formula does not calculate

    Select entire column and go Data-->Text to Column, then just click finish.
    your problem will solve.

    "TaiwanSwede" wrote:

    > Help!
    >
    > Something is weird with my Excel sheet. When I enter a formula, for example
    > =AB2+AB3
    > Excel does not calculate the result. Instead the formula just stays as it
    > is. I am sure there is some strange option that has been chosen by accident,
    > but I can't come up with where.
    >
    > Any help out there!
    >
    >
    >


  6. #6
    Registered User
    Join Date
    08-10-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: formula does not calculate - SOLVED!!

    My kingdom for a CLEAR response...

    The issue is this: When you use a formula to reference a cell value (=b3), Excel 2007 also references the format of the cell and copies that as well. Most of the time this is what you want. The vexing problem happens when you don't realize the STARTING format of the cells you're working with...pretty common in existing worksheets. So...here are all the bits and pieces you need to know:

    1. A formula entered in a cell formatted as TEXT will not calculate. (the crux of your problem)
    2. Changing the cell to General seems like it SHOULD work, but an EXISTING formula still doesn't calculate.
    3. Reentering or editing the formula will make it work...BUT (and this is the detail that I chased around for a while) watch out if the referenced cell is formatted as TEXT too. When Excel copies the cell along with the formatting, it (you guessed it) reverts the formula cell back to TEXT again. Not a problem unless and until you need to edit the formula. (more on this in a second)
    4. With the Home ribbon bar selected, the Number panel displays the current cell format. Try this: set both A1 and B1 to TEXT and type Hello in B1. In A1 enter =B1...it simply displays the entered text. Now change A1 to General format...nothing changes. Now reenter the formula in a1 (=B1) and now A1 contains Hello...BUT...it is also now formatted as TEXT again. If you edit the formula, guess what...it no longer calculates!!!!
    5. From what I can determine, this issue only happens with a direct cell reference such as =b1. It any other calculation or manipulation occurs, the formatting is not copied so the issue is less recurring. In the above example, after changing the format of A1 to General, edit the formula, but instead of simply =b1, make it =left(b1,4). Now A1 will contain "Hell" (couldn't resist), but the format remains General.

    The way I got trapped was that I wanted to extract a product item number out of a UPC code. When the formula didn't work initially, I simplified the entry to just =c1 and stumbled into the TEXT format issue. Changing it to General and reentering the formula put the whole code in into C1 so I thought I had fixed it. Of course, then I edited the formula to the necessary =mid(f1,7,5) and BAM...it stayed that way (due to the format copy described above.) The behavior appeared erratic, but it's actually VERY consistent and the logic is: If at the time a formula is entered in a cell, if that cell is formatted as TEXT, don't calculate it.

    I hope this saves you more time than it took you to read this...but now yous should have enough details to get back to work!! ;-)

    MoreDrums


    Quote Originally Posted by Muhammed Rafeek M View Post
    Select entire column and go Data-->Text to Column, then just click finish.
    your problem will solve.

    "TaiwanSwede" wrote:

    > Help!
    >
    > Something is weird with my Excel sheet. When I enter a formula, for example
    > =AB2+AB3
    > Excel does not calculate the result. Instead the formula just stays as it
    > is. I am sure there is some strange option that has been chosen by accident,
    > but I can't come up with where.
    >
    > Any help out there!
    >
    >
    >

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    18,807

    Re: formula does not calculate

    MoreDrums the date of that post is 07-19-2006, 12:25 PM perhaps

    TaiwanSwede is still looking for a answer but i doubt it!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  8. #8
    Registered User
    Join Date
    08-10-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: formula does not calculate

    HaHa...yeah, I noticed after I posted. Still, I just struggled with this, so perhaps someone else will benefit.

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    32,058

    Re: formula does not calculate

    If you format the cell with the formula as anything other than General beforehand (e.g., Number), that behavior will not occur.
    Microsoft MVP - Excel
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Registered User
    Join Date
    09-08-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: formula does not calculate

    MoreDrums - that helped me a lot - kept running into that same problem. It's always something simple to fix, isn't it?

  11. #11
    Registered User
    Join Date
    10-21-2010
    Location
    San jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: formula does not calculate

    I've recently run into a real conundrum. I have large spreadsheets that have always worked. I am well versed in the cell formatting and auto calc options. However, recently I did something which has caused all of my spreadsheets to require manual calculation. The F9 does not work to recalculate, calculation is set to auto, cells are all 'number' formatted. I have to calculate every field by editing with F2 and then clicking enter. I can also copy a row and then paste the same. But I am wondering what the problem might be? What could I have possibly done on an application level for this to happen? I have hundreds of spreadsheets and they are all doing the same thing. Any ideas would be appreciated.
    Thanks

  12. #12
    Registered User
    Join Date
    09-21-2009
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: formula does not calculate

    I wanted to chime in. I have seen this behavior ocurr on workbooks that previously operated without issue. I am wondering if it is a result of a recent MS Office update.

  13. #13
    Registered User
    Join Date
    06-16-2011
    Location
    Quebec City, Canada
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: formula does not calculate

    Quote Originally Posted by nicksto View Post
    I've recently run into a real conundrum. I have large spreadsheets that have always worked. I am well versed in the cell formatting and auto calc options. However, recently I did something which has caused all of my spreadsheets to require manual calculation. The F9 does not work to recalculate, calculation is set to auto, cells are all 'number' formatted. I have to calculate every field by editing with F2 and then clicking enter. I can also copy a row and then paste the same. But I am wondering what the problem might be? What could I have possibly done on an application level for this to happen? I have hundreds of spreadsheets and they are all doing the same thing. Any ideas would be appreciated.
    Thanks
    I got exactly the same problem. Working with F2 + Enter works, but if you have to recalculate a whole column, it can take quite some time. F9 doesn't work and auto calc is on. What should I do? Does anyone have any clue of the problem?

    BTW, I'm on Excel 2003 SP3 (11.8335.8333) running on Windows XP.

    Thanks!

  14. #14
    Registered User
    Join Date
    06-20-2011
    Location
    London
    MS-Off Ver
    Excel 2000
    Posts
    1

    Re: formula does not calculate

    I have a similar problem, also under XP. I have a spreadsheet which has worked for years, containing several udfs. I recently bought a laptop, running XP Pro SP 3 as does my desktop. I installed on the laptop the same copy of Office 2000 as is installed on my desktop. My spreadsheet on my desktop machine calculates correctly. The very same spreadsheet on my laptop does not: the functions that calculate correctly on my desktop machine give #VALUE errors on the laptop. It is the same spreadsheet with the same formatting of cells. The error crops up when any cell in the data section is changed, and changing the cell back to its original value does not clear the error.

    Reinstalling MS Office on the laptop does not cure the problem.

    I am baffled.

    Thanks.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts