I am summing a column of numbers, but data from several cells are not adding
to the sum. This is a basic function and a potentially serious bug, but I do
not know how to report it.

I found this problem when I was checking for errors after modifying a data
list. After deleting extraneous data, I wanted to check a column of numbers
to be sure it matched the unmodified column. I totalled both columns by
using the "=Sum(XX:YY)" function; the totals were off by 4.

I had not sorted the list, so I copied the modified column beside the
original and subtracted one row from the other in each column: equal numbers
result in zero, showing the discrepancies. These formualas showed zero for
every row. Yet, summing the columns resulted in different totals!

I identified several cells which were not being summed in the original
column of numbers. The autosum button usually sums an uninterrupted list of
numbers in the column above it. But, using autosum at the bottom of the
problem column put in a sum function which stopped at the problem cell,
apparently reading that cell as a blank although it contained a number. If I
click on the cell, delete its number, re-type and re-enter it, the cell is
then added in by the sum function at the bottom of the column.

I would like to send a sample worksheet to Microsoft so they could possibly
tell me what may be happening. Is there a way to report this?

Run this sub on the selected range of cells.

Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each C In Selection 'Range("a1:q" & lr)
If Trim(Len(C)) 0 And C.HasFormula = False Then
C.NumberFormat = "General"
C.Value = CDbl(C)
End If
Next

Application.ScreenUpdating = True
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric, JDT Inc" <Eric, JDT wrote in message
...
I am summing a column of numbers, but data from several cells are not
adding
to the sum. This is a basic function and a potentially serious bug, but I
do
not know how to report it.

I found this problem when I was checking for errors after modifying a data
list. After deleting extraneous data, I wanted to check a column of
numbers
to be sure it matched the unmodified column. I totalled both columns by
using the "=Sum(XX:YY)" function; the totals were off by 4.

I had not sorted the list, so I copied the modified column beside the
original and subtracted one row from the other in each column: equal
numbers
result in zero, showing the discrepancies. These formualas showed zero
for
every row. Yet, summing the columns resulted in different totals!

I identified several cells which were not being summed in the original
column of numbers. The autosum button usually sums an uninterrupted list
of
numbers in the column above it. But, using autosum at the bottom of the
problem column put in a sum function which stopped at the problem cell,
apparently reading that cell as a blank although it contained a number.
If I
click on the cell, delete its number, re-type and re-enter it, the cell is
then added in by the sum function at the bottom of the column.

I would like to send a sample worksheet to Microsoft so they could
possibly
tell me what may be happening. Is there a way to report this?

hi
wild guess but i think that some of your numbers were formated as text.
numbers formated as text will behave like you describe.

regards
FSt1

"Eric, JDT Inc" wrote:

I am summing a column of numbers, but data from several cells are not adding
to the sum. This is a basic function and a potentially serious bug, but I do
not know how to report it.

I found this problem when I was checking for errors after modifying a data
list. After deleting extraneous data, I wanted to check a column of numbers
to be sure it matched the unmodified column. I totalled both columns by
using the "=Sum(XX:YY)" function; the totals were off by 4.

I had not sorted the list, so I copied the modified column beside the
original and subtracted one row from the other in each column: equal numbers
result in zero, showing the discrepancies. These formualas showed zero for
every row. Yet, summing the columns resulted in different totals!

I identified several cells which were not being summed in the original
column of numbers. The autosum button usually sums an uninterrupted list of
numbers in the column above it. But, using autosum at the bottom of the
problem column put in a sum function which stopped at the problem cell,
apparently reading that cell as a blank although it contained a number. If I
click on the cell, delete its number, re-type and re-enter it, the cell is
then added in by the sum function at the bottom of the column.

I would like to send a sample worksheet to Microsoft so they could possibly
tell me what may be happening. Is there a way to report this?

My guess is that you imported the data and consequently
some of the numbers are being read as text.
All imported data should be cleaned before use in Excel.

True bugs in Excel are rare and when found usually make
the national news reports.
In addition, some "bugs" are dismissed by MS as "by design".
--
Jim Cone
Portland, Oregon USA



"Eric, JDT Inc" <Eric, JDT
wrote in message
I am summing a column of numbers, but data from several cells are not adding
to the sum. This is a basic function and a potentially serious bug, but I do
not know how to report it.

I found this problem when I was checking for errors after modifying a data
list. After deleting extraneous data, I wanted to check a column of numbers
to be sure it matched the unmodified column. I totalled both columns by
using the "=Sum(XX:YY)" function; the totals were off by 4.

I had not sorted the list, so I copied the modified column beside the
original and subtracted one row from the other in each column: equal numbers
result in zero, showing the discrepancies. These formualas showed zero for
every row. Yet, summing the columns resulted in different totals!

I identified several cells which were not being summed in the original
column of numbers. The autosum button usually sums an uninterrupted list of
numbers in the column above it. But, using autosum at the bottom of the
problem column put in a sum function which stopped at the problem cell,
apparently reading that cell as a blank although it contained a number. If I
click on the cell, delete its number, re-type and re-enter it, the cell is
then added in by the sum function at the bottom of the column.

I would like to send a sample worksheet to Microsoft so they could possibly
tell me what may be happening. Is there a way to report this?