"KiwiSteve" wrote in message
oups.com
Thanks to both Dave and David for the two replies. Calculation is
set to automatic and I tried F9 a few times just to be safe. You can
never be sure when helping someone else after the fact, but the user
says she created two new adjacent columns using the insert command
on the column header and then dragged the existing formula and cell
values from the adjacent left column.
At that point the new column had
value errors. After tracing the origin of the value error to one
particular cell in each column , I tried Format Cell Number format
on all feeder cells with no change. I looked at all of the
Tools-Options tabs and their settings and saw nothing that I
recognised as affecting the results.
At the suggestion of a colleague I even copied the working
and non working cell formula to the clipboard and pasted them into
Notepad. Apart from expected column reference differences there were
no differences to the formula. I then eventually found that doing
any of the following to the non working cell fixed the problem. 1)
Editing the cell in the edit bar (with no change) 2) Using David's
replace equals with equals edit 3) Dragging the left hand working
cell over the non working cell (which is what the user says she did
in the first place to fill the cell. Using the format painter did
not fix the problem. It really acts as if Excel has internally lost
track of what these non working cells are all about and then resets
itself once the cell is edited. Now that we know Davids work around,
its no big deal, but I would have liked to know the conditions that
trigger this in an attempt to avoid it in the future.

As requested the formula is =(CEILING(SUM(H35:H43),1)) where the
range has cells that have calculated results that are all working
ok.

As an aside I have seen many times a similiar problem with data we
import into XL from external sources. Even though a cell contains a
numeric value and is formatted as numeric, XL will complain that the
cell has an error and wont calculate mathmatical results correctly
until we do the 'no change' edit. The only menu command that allows
us to fix the problem in mass is the Data-Text to columns command.
The other clumsy solution I have is a macro that loops through every
populated cell and does a non destructive edit on the value. Again
XL appears to be acting as if it has lost track of what is going on
until after the edit. The text to columns solution is a pain as I
only know how to apply it one column at a time and my macro is real
slow (but faster than doing it by hand). Once again, if I understood
the failure mode, maybe I could avoid it.


Hi,

Out of interest, does it recalculate correctly if you do a full recalc
(Ctrl-Alt-F9) as opposed to a quick recalc (F9)?

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address