I'm baffled!

https://dl.dropboxusercontent.com/u/...m-Puzzle-1.jpg

Regardless of what format I use, that SUM gives the strange result
shown.

I then re-typed all those numbers into another column, P, and now the
SUM gives a different but still wrong total equal to =SUM(O8:O16)
instead of =SUM(O3:O16).

Then I added a fresh worksheet and typed in a few arbitrary numbers
into a column and SUM worked correctly.

But when I copy/pasted the original 14 cells D3:D16 into the new
worksheet and summed them, once again I got 38!

It may be relevant that when I choose the SUM tool, Excel 2000 first
sometimes selects a horizontal default range. When I somehow get it to
select a vertical range, it chooses a default of only the last 9 cells
(which don't give a total of 38 anyway). I then extend it upwards to
the full 14 cells, but still get a total of 38.

What can possibly have caused this weird behaviour please?

If it's relevant, the original data entry was probably formatted as
either General or Text. I've changed it many times since, but it makes
no difference.


--
Terry, East Grinstead, UK

Terry Pinnell wrote:

I'm baffled!

https://dl.dropboxusercontent.com/u/...m-Puzzle-1.jpg

Regardless of what format I use, that SUM gives the strange result
shown.

I then re-typed all those numbers into another column, P, and now the
SUM gives a different but still wrong total equal to =SUM(O8:O16)
instead of =SUM(O3:O16).

Then I added a fresh worksheet and typed in a few arbitrary numbers
into a column and SUM worked correctly.

But when I copy/pasted the original 14 cells D3:D16 into the new
worksheet and summed them, once again I got 38!

It may be relevant that when I choose the SUM tool, Excel 2000 first
sometimes selects a horizontal default range. When I somehow get it to
select a vertical range, it chooses a default of only the last 9 cells
(which don't give a total of 38 anyway). I then extend it upwards to
the full 14 cells, but still get a total of 38.

What can possibly have caused this weird behaviour please?

If it's relevant, the original data entry was probably formatted as
either General or Text. I've changed it many times since, but it makes
no difference.

I remain baffled, but meanwhile to make progress I re-entered the
numbers over the top of the existing ones. As soon as I'd entered the
5th, the SUM showed the correct result of 336.

Presumably the first five entries must have become corrupted in some
obscure way?

If anyone shares my curiosity, here's a simplified one-column
worksheet demonstrating the issue:

https://dl.dropboxusercontent.com/u/...SUM-Puzzle.xls

--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 28 Sep 2015 09:50:43 +0100 schrieb Terry Pinnell:

Presumably the first five entries must have become corrupted in some
obscure way?

the first five values were text formatted. Formatting them as number did
not solve the problem. In this case:
Select the numbers = Format them as numbers and then with remaining
selection goto Data = Tetx to columns = Fixed Width = Finish


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch wrote:

Hi Terry,

Am Mon, 28 Sep 2015 09:50:43 +0100 schrieb Terry Pinnell:

Presumably the first five entries must have become corrupted in some
obscure way?

the first five values were text formatted. Formatting them as number did
not solve the problem. In this case:
Select the numbers = Format them as numbers and then with remaining
selection goto Data = Tetx to columns = Fixed Width = Finish


Regards
Claus B.


Hi Claus,

I was hoping you'd be around!

That works fine, thank you. I've made a note for future reference.

I don't understand why it wouldn't work after formatting as Numbers?
Perhaps it's an Excel 2000 bug, or is it also true in more recent
versions?

--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 28 Sep 2015 13:17:24 +0100 schrieb Terry Pinnell:

I don't understand why it wouldn't work after formatting as Numbers?
Perhaps it's an Excel 2000 bug, or is it also true in more recent
versions?

sometimes Excel (in all versions) refuses to format text values to
numbers. Then you can force it with Text to columns


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional