Thank you Ken.
Unfortunately the count and counta return the same result. They both counted
the non numeric values. So this solution did not work
But I think that all the persons responded to my message ignored the fact
that these values were numbers and non numbers at the same time: They were
valid for multiplication and at the same time invalid for the Sum function.
This inconsistency is the problem!!!
Reuven
"Ken Wright" wrote:
Maybe not, but if you are taking data from an import then it goes without
saying that you should do some data cleansing first. There are numerous
ways of checking whether or not a set of data is numeric or not, one of
which is a simple
=COUNTA(Rng)-COUNT(Rng)
If all your data is supposed to be numeric then this should be 0.
There are easy ways of flagging/fixing data that is supposed to be numeric
but is not, but the onus is on you to do some of the error checking up
front. Excel is generally as reliable as any other tool in as much as
garbage in means garbage out.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------
"?????" wrote in message
...
thank you Debra
I applied your good advice
it is eficient for small spreadsheets but could hardly help when my
spreadsheet contains thousands of lines.
thank you very much
Reuven
"Debra Dalgleish" wrote:
In Excel 2002, and later versions, you can turn on the error checking
feature, and set it to mark cells with numbers stored as text:
Choose ToolsOptions
On the Error tab, add a check mark to
'Enable background error checking'
Add a check mark to 'Numbers stored as text'
Click OK
????? wrote:
Thank you
1. After analysis I found that the first two values were not numbers
(applying the ISNUMBER function resulted as False).
2. I got the file from a colleague who was not aware to the problem.
3. But my problem is that I could multiply "non numbers" and get a
correct
result (and this supposed to be a tool to verify if these values were
numbers), and at the same time the "SUM" function ignored them.
Who knows how many times we had summarized "Non Numeric" numbers
without
being aware that we had a problem.
I know how to fix it this time, but I'm afraid that every time I will
use
excel, it will be required to verify that no "Non Numbers" infected my
spreadsheets.
thank you
"Doug Kanter" wrote:
I've never seen Excel format a cell as text without human assistance.
Could
you spreadsheet have been a victim of slippery fingers, perhaps before
enough cups of coffee in the morning?
"?????" wrote in message
...
the values we 200 135 360 80 350 350
and the summay was: 1140
the first two values were not included in the Sum.
reuven
"Gary's Student" wrote:
What are the specific values that are being miss-calculated?
--
Gary's Student
"?????" wrote:
I'm using excel for years but today I was shocked. almost died.
While summarizing a column of numbers, the summary was incorrect.
I suspected that few cells contained text rather than numbers so I
multiplied every cell in the column and got the correct number.
So I assume that sometimes excel ignores cells and sometimes
considers
them.
What if the numbers are significant amounts of money? How can I rely
on
excel?
I will be happy to send this little sheet to you.
do i have to crosscheck every spreadsheet i use?
reuven
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html