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

 

[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

 

[via135]

hi!

be specific abt ur formuale & other things!

-via135

 

[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

 

[bobocat]

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

Bobocat

 

[WarrenC]

thanks

Warre