Is Excel reliable

[ר×ובן]

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

 

[Gary's Student]

What are the specific values that are being miss-calculated?

 

[ר×ובן]

the values were: 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]

Sadly, I cannot duplicate your erroneous result. The version of EXCEL on my
computer, unfortunately, returns 1475. Please check that your SUM function
covers all the desired inputs.

 

[Doug Kanter]

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?

 

[ר×ובן]

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

 

[Debra Dalgleish]

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 Tools>Options
On the Error tab, add a check mark to
'Enable background error checking'
Add a check mark to 'Numbers stored as text'
Click OK

 

[ר×ובן]

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

 

[Doug Kanter]

I'd bet you could sort based on the checkmark...maybe. I don't use Excel
2002, so I don't know if the checkmark is applied at the beginning or end of
the number. If at the beginning, sorting should work. Correct those cells
when they're all in a group, and then re-sort when done.

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ragdyer]

<<<"I've never seen Excel format a cell as text without human assistance">>>

Depends on what you consider "human assistance".

New sheet
Format A1 to Text.
In A6, enter:
=SUM(A1:A5)

Notice ... the zero is left justified.
Format of A6 is NOW Text !

Enter numbers in A1:A5, and return total of A2:A5.
A6 is STILL Text!

But, since the formula *IS* working (not displayed in the cell as a text
string), and the contents of A6 equate to True to Isnumber(), then A6 *HAD*
to be General at the time of formula entry, BUT changed after referencing
(duplicating the format of the *first* cell) the range.
Try it with $'s or %'s.

Now, I would consider that "without human assistance".<bg>

 

[Doug Kanter]

I stand corrected! This is one I hadn't seen.

<<<"I've never seen Excel format a cell as text without human
assistance">>>

Depends on what you consider "human assistance".

New sheet
Format A1 to Text.
In A6, enter:
=SUM(A1:A5)

Notice ... the zero is left justified.
Format of A6 is NOW Text !

Enter numbers in A1:A5, and return total of A2:A5.
A6 is STILL Text!

But, since the formula *IS* working (not displayed in the cell as a text
string), and the contents of A6 equate to True to Isnumber(), then A6
*HAD*
to be General at the time of formula entry, BUT changed after referencing
(duplicating the format of the *first* cell) the range.
Try it with $'s or %'s.

Now, I would consider that "without human assistance".<bg>

 

[Doug Kanter]

This raises a question (for me, at least). I import lots of data from
customers, and some of it looks like it was tossed into a lunch bag and
shaken. I clean it up using scripts in Paradox, the database, because within
the Paradox language, there's a function called SCAN, which tells a script
to look at each record and perform whatever procedure you've programmed. Is
there something similar with VBA, to tell Excel to go down a list of records
and do whatever?

 

[Amedee Van Gasse]

In <[email protected]>, Doug Kanter told us an
interesting story. My reply to this story is at the bottom of this
message.

This raises a question (for me, at least). I import lots of data from
customers, and some of it looks like it was tossed into a lunch bag
and shaken. I clean it up using scripts in Paradox, the database,
because within the Paradox language, there's a function called SCAN,
which tells a script to look at each record and perform whatever
procedure you've programmed. Is there something similar with VBA, to
tell Excel to go down a list of records and do whatever?

If you write it yourself, yes.

 

[Doug Kanter]

In <[email protected]>, Doug Kanter told us an
interesting story. My reply to this story is at the bottom of this
message.


If you write it yourself, yes.

That was the natural assumption here. Can you be any more specific?

 

[ר×ובן]

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

 

[Dave Peterson]

Try that =count() vs =counta() once more.

If they turn out to be equal, then either there's nothing in those cells or the
"text" numbers have already been converted to "number" numbers.

And for the inconsistency part--I think it depends on what your definition of
inconsistency is.

Excel is consistent in the way it treats "text" numbers--it always does the same
thing when you do the same thing.

Excel does try to help you by coercing "text" numbers to "number" numbers in
some cases and ignores it in others. But that behavior is consistent within
excel's rules.

(It may not be what you expect, but it is consistent.)

=======

I think that the onus lies with the user/developer to make sure the data is
correct--just like any other computer program--heck, just like everything in
life. I can't put dirty dishes in the clothes washer and expect that to be
consistent with the dish washer.

 

[ר×ובן]

Thank you Dave
1. I'm sorry. count and counta gave the same result (I can send the sample
to you if you wish).
2. The inconsistency is that excel does not treat some value in the same way:
If you multiply "abc" by 2 you get #Value. In my case Sum (200, 100) was 100
(ISNUMBER returned false on the 200) but 200*2 was 400
So 200 was numeric and non numeric at the same time. How would you call it?
Reuven

 

[Jerry W. Lewis]

1. I'm sorry. count and counta gave the same result (I can send the sample
to you if you wish).

In all versions of Excel, if A1:A2 appears to contain 200 and 100 but
SUM(A1:A2) returns 100, then COUNT(A1:A2) will return 1 but COUNTA(A1:A2)
will return 2. What you did is to get the results you claim is a mystery.
If you post your e-mail address, I will e-mail you privately.

2. The inconsistency is that excel does not treat some value in the same way:
If you multiply "abc" by 2 you get #Value. In my case Sum (200, 100) was 100
(ISNUMBER returned false on the 200) but 200*2 was 400
So 200 was numeric and non numeric at the same time. How would you call it?

For bivariate math operations, Excel consistently tries to coerce values
into numbers. Thus ="abc"*2 will return #VALUE and ="200"*2 will return 400
because "200" can be coerced into a number and "abc" cannot. For range
functions, such as SUM, excel coerces nothing; if you have a text string of
digits in a cell, they will be ignored.

A simple way to use this to prep your data is to place a zero in a blank
cell, copy that cell then select the imported data range and Edit|Paste
Special|Add. All text "numbers" will be coerced to numeric values; all
non-coerceable text will become error values. After taking note of the error
locations, you can undo and then repeat the process excluding the
non-coerceable cells if you want to leave them as imported.

Jerry