Excel formulae start doing strange things

[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 ???

[via135]

hi!

be specific abt ur formuale & other things!

-via135

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

[Gord Dibben]

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
<WarrenC.29k0mn_1150560601.7588@excelforum-nospam.com> 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 ???

[WarrenC]

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.

[Gord Dibben]

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
<WarrenC.29k4bz_1150565402.1184@excelforum-nospam.com> 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.

[bobocat]

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

Bobocat

"WarrenC" <WarrenC.29k4bz_1150565402.1184@excelforum-nospam.com> ¼¶¼g©ó¶l¥ó·s»D:WarrenC.29k4bz_1150565402.1184@excelforum-nospam.com...
>
> 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
>

[WarrenC]

thanks

Warren