Cell values not recognized

[Emece]

I have a column with many values, and at last I have the
addition of all the values. The cells all are formated as
numbers.

When I delete some of the values, this is not reflected in
the addition. Strangely if I copy this column to another
one in the same worksheet it works perfect.

Any idea? Thank you.

 

[Ken Wright]

In that case you are either deleting two values that offset each other exactly, eg 2 and -2 if
both deleted together would not affect your data, but more likely, is that some of your data is
text and not numeric, which means some of your 'numbers' are not actually being pulled into the
totals to start with. Just formatting them all as numbers will not do it.

Assume you have 100 numbers in cells A1:A100, and some of them were really text. You can copy an
empty cell, select A1:A100 and do Edit / Paste Special / Add, and this will coerce all the data
back to numeric. To verify that you are picking up all the data you can try the following
formulas:-

=COUNT(A1:A100) should return 100 if all 100 cells have numbers, and you can then simply use:-

=SUM(A1:A100) to add them all up.

 

[Emece]

Ken. Thanks for your answer.
I've checked, and all the values are being pulled into the
totals. Also I am not deleting two values that offset each
other.

I've tried the function count, it returns exactly the
total of values I have.

It's a mistery for me...
Thanks again
Emece.

-----Original Message-----
In that case you are either deleting two values that

offset each other exactly, eg 2 and -2 if

both deleted together would not affect your data, but

more likely, is that some of your data is

text and not numeric, which means some of your 'numbers'

are not actually being pulled into the

totals to start with. Just formatting them all as numbers will not do it.

Assume you have 100 numbers in cells A1:A100, and some of

them were really text. You can copy an

empty cell, select A1:A100 and do Edit / Paste Special /

Add, and this will coerce all the data

back to numeric. To verify that you are picking up all

the data you can try the following

formulas:-

=COUNT(A1:A100) should return 100 if all 100 cells have

numbers, and you can then simply use:-

 

[Ken Wright]

Okay, now you have me curious - If you are able, then by all means send me the file and highlight
which values make no difference when you delete them. I'll happily take a look and let you know
what's wrong. You would need to take the NOSPAM bit out of my email address.

 

[Ken Wright]

For anyone that was curious, the other values in the column contained formulas that referred to
the cells in question, and as soon as the data in those cells was deleted, the other values
adjusted accordingly by an equal amount. This meant that the bottom line always stayed the same,
although various cells within the column did change.