+ Reply to Thread
Results 1 to 7 of 7

Excel formulae start doing strange things

  1. #1
    Registered User
    Join Date
    06-17-2006
    Posts
    28

    Excel formulae start doing strange things

    Has anyone had the problem where the result of a formula is incorrect. I've had on occasion entered a simple formula, such as adding a group of numbers within an "IF" statement and the result is wrong. If I start a new workbook, and enter the exact same thing, it works out correctly. Yesterday one of my co-workers had a problem with a spreadsheed she's been using for years. All of a sudden, she started getting "VALUE" errors in the lower half of the spreadsheet. All formulae were the same in each row. I ended up fixing the problem by deleting 5 rows, then entering data in one column for about 10 cells. All the "VALUE" fields disappeared and I then deleting the values in those same 10 cells. All seemed to work okay after that.

    Strange ???

  2. #2
    Forum Contributor
    Join Date
    08-28-2005
    Posts
    298

    excel formulae start doing strange things!

    hi!

    be specific abt ur formuale & other things!

    -via135


    Quote Originally Posted by WarrenC
    Has anyone had the problem where the result of a formula is incorrect. I've had on occasion entered a simple formula, such as adding a group of numbers within an "IF" statement and the result is wrong. If I start a new workbook, and enter the exact same thing, it works out correctly. Yesterday one of my co-workers had a problem with a spreadsheed she's been using for years. All of a sudden, she started getting "VALUE" errors in the lower half of the spreadsheet. All formulae were the same in each row. I ended up fixing the problem by deleting 5 rows, then entering data in one column for about 10 cells. All the "VALUE" fields disappeared and I then deleting the values in those same 10 cells. All seemed to work okay after that.

    Strange ???

  3. #3
    Gord Dibben
    Guest

    Re: Excel formulae start doing strange things


    Warren

    The #VALUE! could come from Excel and the formula not recogninzing the data as
    valid for that formula.

    i.e. numbers that have been formatted as text will not add up. Perhaps those
    lower down cells had been pre-formatted as text.

    By deleting and re-entering you have changed the format and now they are
    correct.


    Gord Dibben MS Excel MVP

    On Sat, 17 Jun 2006 11:09:19 -0500, WarrenC
    <[email protected]> wrote:

    >
    >Has anyone had the problem where the result of a formula is incorrect.
    >I've had on occasion entered a simple formula, such as adding a group
    >of numbers within an "IF" statement and the result is wrong. If I start
    >a new workbook, and enter the exact same thing, it works out correctly.
    >Yesterday one of my co-workers had a problem with a spreadsheed she's
    >been using for years. All of a sudden, she started getting "VALUE"
    >errors in the lower half of the spreadsheet. All formulae were the same
    >in each row. I ended up fixing the problem by deleting 5 rows, then
    >entering data in one column for about 10 cells. All the "VALUE" fields
    >disappeared and I then deleting the values in those same 10 cells. All
    >seemed to work okay after that.
    >
    >Strange ???



  4. #4
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    The co-workers spreadsheet were simple addition/subtraction formula. The ones I've had problems were in this format:

    =IF(a1-b1-c1-d1<>0,"oops",0). Even though the correct answer is zero, I would get "oops" as the result. If I opened a new spreadsheet and and entered the same thing, it would work. Also have had the problem were the formula would work, but as soon as I copy it, it wouldn't. However, if I deleted this copied version and then typed it in, it would work. I only come across this type of issue occasionally, but it gets to be a bother.

  5. #5
    Gord Dibben
    Guest

    Re: Excel formulae start doing strange things

    Warren

    I would say that some of the numerics are seen as text.

    That's why re-entering makes it work.

    Make sure you are copying to cells formatted as General.


    Gord

    On Sat, 17 Jun 2006 12:29:47 -0500, WarrenC
    <[email protected]> wrote:

    >
    >The co-workers spreadsheet were simple addition/subtraction formula. The
    >ones I've had problems were in this format:
    >
    >=IF(a1-b1-c1-d1<>0,"oops",0). Even though the correct answer is zero, I
    >would get "oops" as the result. If I opened a new spreadsheet and and
    >entered the same thing, it would work. Also have had the problem were
    >the formula would work, but as soon as I copy it, it wouldn't. However,
    >if I deleted this copied version and then typed it in, it would work. I
    >only come across this type of issue occasionally, but it gets to be a
    >bother.



  6. #6
    bobocat
    Guest

    Re: Excel formulae start doing strange things

    have you try to use the formula evaluation tools to trace the problem. It is
    a very useful tools.

    Bobocat

    "WarrenC" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
    >
    > The co-workers spreadsheet were simple addition/subtraction formula. The
    > ones I've had problems were in this format:
    >
    > =IF(a1-b1-c1-d1<>0,"oops",0). Even though the correct answer is zero, I
    > would get "oops" as the result. If I opened a new spreadsheet and and
    > entered the same thing, it would work. Also have had the problem were
    > the formula would work, but as soon as I copy it, it wouldn't. However,
    > if I deleted this copied version and then typed it in, it would work. I
    > only come across this type of issue occasionally, but it gets to be a
    > bother.
    >
    >
    > --
    > WarrenC
    > ------------------------------------------------------------------------
    > WarrenC's Profile:
    > http://www.excelforum.com/member.php...o&userid=35524
    > View this thread: http://www.excelforum.com/showthread...hreadid=552933
    >




  7. #7
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    thanks

    Warren

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1