Cell values not recognized

  • Thread starter Thread starter Emece
  • Start date Start date
E

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.
 
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.
 
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
Click to expand...
offset each other exactly, eg 2 and -2 if
both deleted together would not affect your data, but
Click to expand...
more likely, is that some of your data is
text and not numeric, which means some of your 'numbers'
Click to expand...
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
Click to expand...
them were really text. You can copy an
empty cell, select A1:A100 and do Edit / Paste Special /
Click to expand...
Add, and this will coerce all the data
back to numeric. To verify that you are picking up all
Click to expand...
the data you can try the following
formulas:-

=COUNT(A1:A100) should return 100 if all 100 cells have
Click to expand...
numbers, and you can then simply use:-
 
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.
 
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.
 
Back
Top