Hi Steve,
Might be a good idea to make a copy of the sheet or a backup of file first,
to protect your data, and so that you can reexamine the original data.

I would suggest running the TRIMALL macro to see if that fixes
fixes things up.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Will fix up a date that has a leading space, because the space
will be trimmed, similar fixup for other numbers that were hidden
because of a leading space.

Will convert CHAR(160) or non-breaking space to a CHAR(32) space
which if leading or trailing would also get trimmed off.

Macro turns calculation on when finished, have just added a MsgBox
indication if calculation was found to be off when macro was invoked.

There are quite a few things that can cause problems that are
noted with the description and in the section(s) above that
reference.

You can run the following tests on your original data to see what
the problem actually was:

The use of SUM (in your formula) for instance will only add up valid data, it will
ignore text entries. One of the tests is an ISTEXT
test. =ISTEXT(A1) which helps to identify problems, you
could use Conditional Formatting as an initial vehicle to identify text
and then to check each character of a cell to find out what the
problem actually is. =CODE(MID(A1,1,1) =CODE(MID(A1,2,1)
etc., if you want to know the reasons for problems before fixed up
with TRIMALL.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"KiwiSteve" wrote in message ...
Thanks to both Dave and David for the two replies. [...]
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 won't calculate mathmatical results correctly
until we do the 'no change' edit.