Excel limitation or bug or what?
 

Thanks for any help on this "mystery"
I have these 'numbers' in an area $A$1:$A$15 as text:
712032100100005419
712132120100006097
712132120300006097
712132120400006097
712132122100006097
712132122200006097
712132122300006097
712132122400006097
712132122500006097
712132122600006097
712132122700006097
712132122800006097
712132127700006097
712132128800006097
712132129900006097

Result of this function is 1:
=COUNTIF($A$1:$A$15;"712132120300006417")

Does anybody know why? The number is not there at all!!!

Excel limitation or bug or what?
 

Excel is only capable of accuracy up to 15 (or is it 16?) digits.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Roman" wrote in message
ups.com...
Thanks for any help on this "mystery"
I have these 'numbers' in an area $A$1:$A$15 as text:
712032100100005419
712132120100006097
712132120300006097
712132120400006097
712132122100006097
712132122200006097
712132122300006097
712132122400006097
712132122500006097
712132122600006097
712132122700006097
712132122800006097
712132127700006097
712132128800006097
712132129900006097

Result of this function is 1:
=COUNTIF($A$1:$A$15;"712132120300006417")

Does anybody know why? The number is not there at all!!!

Excel limitation or bug or what?
 

Well, but this is text. Everything.

Excel limitation or bug or what?
 

It works fine for me if your 'numbers' in A1:A15 are entered as Text
(either preformat the cells as Text, or enter the values with a leading
apostrophe: ')

Per XL specifications (see Help: Specifications), XL only retains a
precision of 15 decimal digits. So if your numbers are entered as
numbers, XL won't retain the last few digits:

712032100100005419 == 712032100100005000

712132120100006097 == 712132120100006000





In article . com,
"Roman" wrote:

Does anybody know why? The number is not there at all!!!

Excel limitation or bug or what?
 

It works when I test it with your data and your formula.

Do you have any leading or trailing space characters?

In article .com,
"Roman" wrote:

Well, but this is text. Everything.

Excel limitation or bug or what?
 

What are you expecting. They are all different values

--
HTH

Bob Phillips

"Roman" wrote in message
oups.com...
Well, but this is text. Everything.

Excel limitation or bug or what?
 

Thank you for cooperation,

My problem is that I´ve got a horde of these numbers (they are
accounting codes) and I´m trying do define sum database functions. Now
I see it is guite problematic with this long textnumbers.
These are imported from a text file as a TEXT (they are always aligned
left, are not calculated by statusbar SUM etc.) But in this Dfunctions
they are sometimes behaving like numbers. I can´t rely on this
calculations cause they are wrong sometimes.
I´m perplexed but thank you anyway.

Excel limitation or bug or what?
 

I certainly expect 0.

Excel limitation or bug or what?
 

I don't know if it is a bug or a limitation or a feature but COUNTIF
sure does strange things. As long as the token can be interpreted as a
number it apparently is. And, once it becomes a number it is subject
to XL's 15 digits of precision.

Two ways around the problem. One is to use the array formula
=SUM(N(A1:A15="712132120300006417"))

The other is to create a criterion that cannot possibly be
misinterpreted by COUNTIF as numeric. In column F, add the formula
=A1&"A" and then use =COUNTIF(F1:F15,"712132120300006417A")

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . com,
says...
Thanks for any help on this "mystery"
I have these 'numbers' in an area $A$1:$A$15 as text:
712032100100005419
712132120100006097
712132120300006097
712132120400006097
712132122100006097
712132122200006097
712132122300006097
712132122400006097
712132122500006097
712132122600006097
712132122700006097
712132122800006097
712132127700006097
712132128800006097
712132129900006097

Result of this function is 1:
=COUNTIF($A$1:$A$15;"712132120300006417")

Does anybody know why? The number is not there at all!!!

Excel limitation or bug or what?
 

You get a result of Zero? (Because none of the numbers stored as Text
match)

It didn't work for me XL2003.

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
It works when I test it with your data and your formula.

Do you have any leading or trailing space characters?

In article .com,
"Roman" wrote:

Well, but this is text. Everything.

Excel limitation or bug or what?
 

I´d like get zero but I got 1.

I've placed a worksheet with complete example if you or someone else is
willing to check it he
http://pechane.aktualne.cz/files/prdsum.zip

Excel limitation or bug or what?
 

I looked at it and I think you will need to use the procedure you have on
the right (similar to what was suggested by Tushar).

--
Regards,
Tom Ogilvy


"Roman" wrote in message
oups.com...
I´d like get zero but I got 1.

I've placed a worksheet with complete example if you or someone else is
willing to check it he
http://pechane.aktualne.cz/files/prdsum.zip

Excel limitation or bug or what?
 

I was afraid of that.
Thank you for consultation.

Excel limitation or bug or what?
 

Sorry to be so late to this party, but I just stumbled upon it.
=COUNTIF($A$1:$A$15;"712132120300006417")
would also be the command to look for numeric values that are equal to
712132120300006417. In the absence of an unambiguous syntax to distinguish
whether the intended comparison value is numeric or text, Excel will consider
it to be numeric if it can (i.e. there is no way with just digits to specify
that you want a comparison to text digits in COUNTIF.

Since Excel understands your comparison value to be numeric, it also coerces
all values in A1:A15 that it can before the comparison. When converting from
text to numeric, Excel first truncates (a very unfortunate choice) to 15
digits before conversion to binary. Your 3rd entry and your comparison value
agree to 15 digits, hence the return value of 1.

To get the behavior that you intended, you either need at least one alpha
character in these accounting codes, or you need to use something other than
COUNTIF.

=SUMPRODUCT(--($A$1:$A$15="712132120300006417"))
would do what you intended.

Jerry

"Roman" wrote:

Thanks for any help on this "mystery"
I have these 'numbers' in an area $A$1:$A$15 as text:
712032100100005419
712132120100006097
712132120300006097
712132120400006097
712132122100006097
712132122200006097
712132122300006097
712132122400006097
712132122500006097
712132122600006097
712132122700006097
712132122800006097
712132127700006097
712132128800006097
712132129900006097

Result of this function is 1:
=COUNTIF($A$1:$A$15;"712132120300006417")

Does anybody know why? The number is not there at all!!!